How Do I create a Macro that Creates Linked External tables?

[Savin]

I have an MS ACCESS database that is nothing more than a large number of
linked tables. The list of tables needs to be constantly added to and
removed and there is actually a pattern of sorts to the process. Rather than
add links to about 80 databases each month I would like to write a VBA to do
these links (or macro if that can be done).

In simple terms for each link to another database (lets pretend the other
database is also in MS ACCESS for this example)

1) I use the External Data tab
2) I select Access as my Import type
3) I choose the LINKED TABLE radio buitton
4) I browse to the path and select the database to which I wish to link
5) I click OK
6) I click select two of the tables in the database (they happen to always
have the same name)
7) I click OK
8) I find the new tables in my list and rename them to something else (avoid
confusion and duplicates)
9) I open a Query and modify the SQL to include the new table (a UNION query)

etc etc ...

How the heck to I write a macro to do this?

I don't need any help on the flow of the code .. I've confused as heck as to
where in the object model I need to do this.

I need to create a new table but whose properties include all the link info.
I need to rename the table ( I can probably work this out if I could ever get
past 1 above .. but some help might save me hours of fiddling LOL)
I need to open a Query Object
I need to modify the SQL statement stored within it ...

seems pretty simple if I only understood where the stuff exists in the object
model.

HELP!!!!

 

[Marshall Barton]

I have an MS ACCESS database that is nothing more than a large number of
linked tables. The list of tables needs to be constantly added to and
removed and there is actually a pattern of sorts to the process. Rather than
add links to about 80 databases each month I would like to write a VBA to do
these links (or macro if that can be done).

In simple terms for each link to another database (lets pretend the other
database is also in MS ACCESS for this example)

1) I use the External Data tab
2) I select Access as my Import type
3) I choose the LINKED TABLE radio buitton
4) I browse to the path and select the database to which I wish to link
5) I click OK
6) I click select two of the tables in the database (they happen to always
have the same name)
7) I click OK
8) I find the new tables in my list and rename them to something else (avoid
confusion and duplicates)
9) I open a Query and modify the SQL to include the new table (a UNION query)

etc etc ...

How the heck to I write a macro to do this?

I don't need any help on the flow of the code .. I've confused as heck as to
where in the object model I need to do this.

I need to create a new table but whose properties include all the link info.
I need to rename the table ( I can probably work this out if I could ever get
past 1 above .. but some help might save me hours of fiddling LOL)
I need to open a Query Object
I need to modify the SQL statement stored within it ...

seems pretty simple if I only understood where the stuff exists in the object
model.

Not very aimple. Maybe you can get started by reading Help
on the TransferDatabase method. If you know the file and
table names, it does pretty much what the import/export menu
item does.

As for working within the object model, read up on the
TableDef object and its properties, especially Connect and
SourceTableName