Select query from another mdb

[teel73]

are you able to specify the location of another database that you want to select data from?

For example: I want to do something like this:

SELECT [field1], [field2] from C:\DesktopFolder\MyOtherDatabase.mdb\TableName

Is this possible? Can someone assist me with that structure or syntax.

 

[plog]

You would create a linked table and then reference it that way.

External Data->Access->

Browse to your database, check the 'Link to the data...' and click Ok. A box will appear and allow you to select the table(s) you want to link. It will appear in the Tables section with a name. Use that name to reference it in your query.

 

[teel73]

Ah yes.. that's the physical way. I know how to do that. But what I need and want to do is something like this:

SELECT

.[field1],

.[field2] from C:\Desktop\SomeFolder\OtherDb.mdb

I want to select from other tables without physically linking the tables because I have 45 tables from about 10 different databases that I need to get data from.

 

[plog]

I don't think its possible in a query. It might be possible hacking together something using VBA. You could even create one linked table, then update where it links to based on VBA. That would allow you to have a table of file locations, then dynamically change that one linked table to whichever source you want to work with.

 

[PeterF]

It is possible and your sample is almost right, from the Acces help:

SELECT* FROM tabelexpressie IN externaldatabase