Access Saved Import details

[BeckyC]

Is there a way, maybe using vba code, to pull in the saved imports from a
table creation? I create a table and save the import steps and then need to
reimport this file later in a macro. I need to do this with about 90 tables,
so I want to automate it. Any suggestions on how? We don't run Outlook at
this point, so I can't schedule it to run. I want the macro to kick it off.

 

[Tom Wickerath]

Hi Becky,

What type of file are you importing? The Text Import wizard allows one to
save an Import Specification, which can be used in VBA code. My preference is
to link to external files, and then run an append query to append (add) the
data from the linked file to a table, rather than importing each file as a
separate table. You can include one or more fields in the table that help you
identify the source file for each record.

Here is a sample that I have available, with VBA code that allows one to use
a Common File Dialog to select a text file, which can be either imported or
linked. This might give you a starting point for your task at hand:

http://www.accessmvp.com/TWickerath/downloads/ImportAndLinkTest.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[BeckyC]

Tom,

This is really a BI application.

We are using a mainframe report writer to download dbf files (of critical
business facts from separate lines of business) to a specific temporary LAN
folder. From inside Access, import procedures have been defined/saved. Since
this entire process will be run monthly, we want to automate as much of this
process as possible so it can be handed off to others. As the imports have
been defined and are saved, accessible and will be rerun each month exactly
as is, we want to also automate the import via macro commands. Once the files
are imported, they are renamed (since they import with a "1" suffix as the
file name already exists inside the database), and about 100 queries run
against the data files to ETL them into what our execs want to see.

Becky

 

[Tom Wickerath]

Hi Becky,

Since this entire process will be run monthly, we want to automate as much
of this process as possible so it can be handed off to others.

This should be quite "do-able", however, I'm not sure that it can be done
successfully with macros alone. You will most likely need some custom VBA
(Visual Basic for Applications) code.

Once the files are imported, they are renamed (since they import with a "1"
suffix as the file name already exists inside the database), ....

It sounds to me like this operation could be tedious and time consuming to
rename all imported tables (depending on how many .dbf files you are
importing). Have you considered using VBA code to link to the .dbf files, and
then append the data from each file into a local table within your
application? I have a sample application that does this type of operation. It
currently links text files, but changing to link .dbf files instead would be
trivial. If you are interested, I'd be happy to share a copy of this
application with you, however, I do not currently have it available for
download.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[BeckyC]

Tom,
Yes I'd love to have the vb code for this! That sounds much better than the
current solution.

Becky

 

[Tom Wickerath]

Hi Becky,

Send me a private e-mail message with a valid reply-to address. My e-mail
address is available at the bottom of the contributor's page indicated below.

Please do not post your e-mail address (or mine) to a newsgroup reply. Doing
so will only attract the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________