Related Topics

Saved Imports

VBA to Import Excel Worksheet
Have been using the saved imports in access to import excel data into access tables. Like the way it works except for the difficulty when you have to move the program from one directory to another, means have to redo all of the saved imports to reference the new location. Very time consuming.

Is there VBA code that will perform the same action as the access saved imports? That way, just have to change the location in the code instead of redoing the saved import


Saved Imports
I am using Access 2010 and have some saved imports which work well but I need to call them from a command button or in a macro and then proceed to other functions. My problem is that currently I can get to the Save Imports Dialog box but then have to click 'Run' then afterwards close the dialog box before the remainder of the routines run.


RunSavedImportExport
I'm trying to create a macro that imports Excel data into a table so that I can create a switchboard button.

I created my import successfully and saved the steps. It works fine and the import shows in the Saved Imports list (External Data > Saved Imports)

When I create the macro, I am trying to use the RunSavedImportExport action, but the import does not appear in the dropdown list of Import Export Names available to use. In fact, there is nothing in the dropdown list. I've tried closing the database and re-opening. nothing.

If it makes a difference, this is a split database.

Any idea why the saved import wouldn't be in the list


Problem with Access Import Text Wizard; Saved Imports
I am encountering a curious scenario today. I am running the Access Import Text Wizard; I define several DataTypes as Double and several as Text. When I run the Wizard, I save the Import and everything imports just fine.

If I later click External Data > Saved Imports, and then run the Saved Import, I have to wait for a while, just like the initial import (it’s a large file).

I then get a message that says all objects were imported correctly. However, NOTHING is imported. The entire table is completely blank. Has anyone encountered thisbefore?

I am using MS Access 2010.


Access 2010 Saved Imports
Is there a way to use a command button to execute a Saved Import? I have a Saved Import that I execute using Access default options. A command button on the switch board would be nice. Any information or direction would be much apprecated!

On a related note - I have a Table called "2011" that gets updated via the Saved Import process. This process brings over a Table called 20111 which I have to rename 2011, by first deleting the already named 2011. Is there a code that can do this that can be run through a command button?


programmatic file imports and file dialogs
I have a regular process wherein I import two excel files into Access as tables, and run a series of queries on them. I am trying to automate this process as much as possible. The data in the files in different every time, but otherwise the process is the same.

Part I: I want the user to get a file dialog from a form, so that they can search the hard drive and locate the files to import. Then, I imagine, the paths of the two files selected would be stored in text boxes.

Part II: The user would click a 'go' button, and both files would import. The imports happen according to pre-defined parameters, I.e. only some of the fields would be imported (the same ones every time).

Part I: And then the queries run, which I think I can do myself.

Surely this scenario is very common, yet I can't find a clear set of instructions for doing it online. the file dialog in particular seems to be complicated and depend on one's version of Access (mine is Access 2007).

what I want to do is a saved import. which is an existing Access feature. The problem is that with the saved import feature, you must always import the same file, from the same place, every time.


ADP-files - how queries etc. are imported?
I was shown at work an adp-file, which is in 2002-2003 format, I'm using this with Access 2010. The size of the file is about 81 kilobytes, but it has tens of thousands of records inside. It dynamically receives queries from SQL Server (or at least my supervisor said that he never creates new queries into Access, still it gets them as they are in SQL server) and I can't see forexample VBA code inside the adp-file.

I'm supposed to fix this program, but to begin with, I would need to find where exactly the logic is that imports the queries etc. Saved imports has nothing. VBA Project has nothing. There's no macros.

So shortly: Where is the logic in adp-files?


Run saved import spec in VBA
Thought this would be easy, but getting error message

Want to run an import spec that imports and appends data from Excel to my Access table. I ran the import process, saved the import spec, naming it "Import-PGI_Data" then, in my VBA for my button:

DoCmd.RunSavedImportExport "Import-PGI_Data"

I keep getting "argument not optional" error.


Importing CSV file into 2010/creating save import routine
I'm not sure whether this should be 2 separate posts, but since it's a similar problem -
I created a program in 2003. It has a saved import routine that imports a CSV file from C:\program files\exports\data.csv. No problems.

In 2010, that function stopped working as Access said that the import routine 1 didn't exist. I recreated it in 2010.

First of 2 problems -
The import routine doesn't import the data as entered, which makes it very difficult to proof.

Second of 2 problems -
I thought that deleting the saved import specs and re-creating them with a different name might solve the problem. No go. When I go to the import step in the macro and try to select the newly named/saved routine, it doesn't appear in the list. No matter what I've done (even compact/repair), no luck.


Importing CSV file into 2010/creating save import routine
I'm not sure whether this should be 2 separate posts, but since it's a similar problem -
I created a program in 2003. It has a saved import routine that imports a CSV file from C:\program files\exports\data.csv. No problems.

In 2010, that function stopped working as Access said that the import routine 1 didn't exist. I recreated it in 2010.

First of 2 problems -
The import routine doesn't import the data as entered, which makes it very difficult to proof.

Second of 2 problems -
I thought that deleting the saved import specs and re-creating them with a different name might solve the problem. No go. When I go to the import step in the macro and try to select the newly named/saved routine, it doesn't appear in the list. No matter what I've done (even compact/repair), no luck.

I'm stumped at this point and could sure use the


Macro command RunSavedImportExport
So after discovering how to "enable" the often referenced but well-hidden RunSavedImportExport macro command (tell Access to list all commands, not just ones for "untrusted databases"), I am now wailing and gnashing my teeth trying to figure out why I can't enter the name of a saved Import into the argument field.

If I look under the Save Imports option on the External Data tab, I SEE IT RIGHT THERE!

There are no spaces in the name or anything. But when I type it into the argument column or the Saved Import Export Name text box at the bottom, it won't acceptit.

Microsoft's documentation on this is of no use whatsoever (unless it's one of those subjects where you have to already know the answer in order to ask the question.)

It's a macro, for crying out loud! If I wanted this much secrecy,


Saved Import Export Executes but Creates new local table
I am trying to use a saved import routine in an Access 2010 database (in a VBA Sub: DoCmd.RunSavedImportExport "Import-X"); the routine was created by a user who is no longer available.

My research on this subject indicates that one can neither view details nor edit these saved imports - oh, well. I understand that the only path is to recreate,

Here's the issue: when designing the steps, I specify a table where to import the data - the table specified is a linked table to a backend Access database. I'm trying to do this in thefront-end database - the one with the links to the back end tables.

The import executes fine - the only problem is that once complete, it has created a new local table of the same name in the front end (and obviously overwrites the reference to the linked back-end table).

this behavior is unacceptable - not to mention frustrating.

Am I missing something? Or is this a "feature" of Access 2010.


Pass Value from Report To Query
I have a Saved query that is pulling data from tables and other Saved Queries. I have a SQL statement built in VBA which pulls from that Main Saved Query and then I set as a reports record source. In VBA I specify criteria one being That a parameter (which is a date), from a form, is between 2 Dates.

Problem I am having is that one of the Saved queries that is in the Main Saved Query also needs to use that (Date) as criteria also. But I don't know how to get this date to the saved query that is in the Main Query


Import from mult sheets in Excel to mult tables in Access
I have an Excel file with 8 different sheets. Each sheet is related to a specific table in the database. I need to bring the data in via VBA. I made saved imports for each sheet thinking I would be able to use them (like you can with the TransferText) but I do not see a way to call the import name in the TransferSpreadsheet method.

The code needs to be set up so the user can select the Excel file then the 8 imports will run. The amount of data on each sheet will vary every time so I don't think named ranges would be a practical way of handling it.

Just for a bit more background, this process sets up a new backend database, then the spreadsheet import will bring in user developedstartup data (ie user table, labor codes table,.)

Am I just missing something in the TransferSpreadsheet method?

In Summary: I need to provide the Excel file (path and name) then run an import for each of the 8 sheets into 8 different tables.


Import data from excel but keeping old information
I import data from and excel sheet into the database using the saved imports button on the external tab, what I would like to know is there a way to keep the old data as well as when I run the import it over writes the table,

The reason I ask is the excel document is a crystal report put into excel and uploaded but I only get the last months data so could I change it to add the new to the bottom of the table


transaction based macros?
I have a macro plus some VBA code, which imports moves data around a database, imports a spreadsheet, then performs a few more related data tidy ups within the Access database.

Is there a way to run a transaction over all of this so that if something goes wrong, everything wll be rolled back? For example, if the import of the spreadsheet is rejected due to a bad field, can I roll back the first data changes


Modifying an import specification
I am using Access 2007 to import a number of different text files with the import wizard.

As the files are complex, it might take a few attempts to get the specification correct.

if I attempt the import and there is an error, I do get the option to save the import specification.

From "Saved Imports" I can later rerun an import using an existing specification, but I cant see how to edit an existing specification.


Modifying an import specification
I am using Access 2007 to import a number of different text files with the import wizard.

As the files are complex, it might take a few attempts to get the specification correct.

if I attempt the import and there is an error, I do get the option to save the import specification.

From "Saved Imports" I can later rerun an import using an existing specification, but I can't see how to edit an existing specification.


Linking Access Query of Query to Excel - Blank Result
In access I have a query (made from 2 queries) then compared against another double query.

When I open excel and try to import it (so they sync/refresh data). It just imports nothing but the headers, no data. Just 2 blank rows.

Anyone know why? My other imports to excel work just fine


How to import data into a split database
I've been working on a database for a few months now. The user clicks a command button and can select a text file to import, All the imports go into the same table called 'Joint Data'.

I also have a copy of this table as I have to perform some background checks on the imported data separately. At the moment the database is just a prototype and so Ithought that splitting the database is the best way for various users to import their data into the front-end DB, which is then stored in the back-end DB which is kept in a secure location.

The problem I am having after splitting my DB is that when I import a text file (which I have tested before splitting), an error message appears basically saying this saved import does not exist.

So on trying to (re) make the saved import, I then got another error saying: "You cannot record your changes because a value you entered violates the settings defined for this table or list.
. Correct the error and try again"
I think this means that my table set-up does not have the same fields as the text file or something but having checked through, this is not the case.

Once again I'll remind you that this is the first time I've tried splitting any kind of database or even used a linked table.