Getting Table info from another DB

[pafcpilgrim]

Guys i have tried to seach the Forum but can't find a solution to my need.

I would like to get a collection of table names from another DB, what i am trying to do is populate a table with a list of table names that the front end links to and their paths once a user has selected the data file location.

Instead of me having to force a user to place the DB files in a location of my choice when the database first loads it will prompt the user to select the location of the data file then (this is why i need the above) populate a table with a list of table names and their source, then establish a link to them for the front end to work.

Also if i ever send amended DB files or they move the location of the data files they can automatically re-establish the linked tables.

Hope this makes sense.

Cheers guys.

 

[pdx_man]

The system table MSysObjects is the table you need to look at. You can't see them unless you have the System Objects selected under the Tools ... Objects menu command. You don't need to have this selected to run the query, though.

Use the IN keyword to specify a database other than your own.

You can dynamically create a query that looks something like:

SELECT MSysObjects.Name
FROM MSysObjects IN 'C:\YourDB.mdb'
WHERE (((MSysObjects.Type)=1));

For local tables the Type is 1, for linked, it is 4 (I believe)

 

[pafcpilgrim]

Thanks didn't realise you could do that in an SQL Query in all the years i have been using Access!lol I will try it though seems a lot easier that using code to link and get the information.

Thanks for replying

 

[pafcpilgrim]

Sorted now, but sorry i didn't use your method. The problem i still had was getting the file location into the SQL query, so i searched the help file and managed to work out how to connect to another DB get the list of table names and then create new Table Defs to the curr DB to re-establish the links.

Thanks anyway you have giving me another learning curve.