Create a Macro and Import into Access to RUN

[Gil]

I need your professional help. I got a project that needs to be
completed by tomorrow.

here is what I am trying to do;

I have an excel file that contains all the database file names. There
are approximately over 300 file names that I need to use.

I would like to create a macro that will take those database file names
in the excel file and use the ODBC link to transferfile into MS Access.

I've been trying to do a search on Import Macros for Access..but all I
get was creating macros to import/export.

I have not seen anything on the thread on creating a script or something
that I can import the scripts into Access as a macro to be used.

I would appreciate anyone's help, below is an example:

Excel File:
goodfiles.xls (column A = Database names)
Column A
ABC
ABC1
ABC2
ABC3...

Macros

ODBC;DSN=LIBRARY1;;TABLE=LIBRARY1.ABC
ODBC;DSN=LIBRARY1;;TABLE=LIBRARY1.ABC1
ODBC;DSN=LIBRARY1;;TABLE=LIBRARY1.ABC2
ODBC;DSN=LIBRARY1;;TABLE=LIBRARY1.ABC3
.....

So, I could create 300 lines of ODBC commands to transferdatabase into
Access from Library1 system, but that is not practical.

Could I use this?:
ODBC;DSN=LIBRARY1;;TABLE=LIBRARY1.*

Where * is all the files???

Reason why I can not use the * is that some database files have no
records and when there are no records, the transferdatabase macro bombs out.

So, I created an Excel file of all the database has records in them call
'goodfiles.xls' and would like to use the transferdatabase and use the
column A as an import database file name for transferdatabase into Access.

I hope I make it clear what I wanted to do.

Thanks for all your help...

Gil

 

[Ken Snell \(MVP\)]

Try importing the EXCEL list into a temporary table. Then you can loop
through that table and get each database name, and use TransferDatabase
actions to import the information from those ACCESS databases. You probably
will need to use VBA code to accomplish this, though -- ACCESS macros do not
lend themselves to opening recordsets and looping through them.

 

[jsheehy]

hi this is nick.