Remote Queries in Access

MS-Access makes it very easy to link to tables in other Access databases . . .

File ~ Get External Data ~ Link Tables...

just navigate to your mdb file & click Link, select the table(s) and click OK.

But you can also connect & retrieve data from another file, without creating a linked table. This little-known technique has been around AFAIK since the Access 2.0 days. I'm not suggesting that you use this instead of linked tables, but it has certain advantages & flexibility in specific cases.

Begin a new query, don't add any tables, and switch to the SQL view. Type in your SQL statement, which might look like this:

SELECT *
FROM Table1
IN 'C:\MyFile.mdb'

Note the single quotes around the file name. When you run this query, you'll see the data in that external table. If you want to apply some criteria, you could use this:

SELECT *
FROM Table1
IN 'C:\MyFile.mdb'
WHERE MyField = "something"

One advantage of this technique is that it bypasses the show / hide settings for hidden & system objects ( Tools ~ Options ~ View ~ Hidden objects, and ~ System objects ).

Let's say you want to view all the user tables in an external file - you could use this:

SELECT [name]
FROM MSysObjects
IN 'C:\MyFile.mdb'
WHERE [type] = 1
AND LEFT([name],4) <> "MSys"

MSysObjects is a system table that contains all the objects in the Access mdb. The type = 1 denotes local (not linked) native Access tables.

Taking this a step farther (with a little bit of VBA code) you could view a list of the fields in that external table (watch for line breaks) . . .

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim sql As String
'
Set db = CurrentDb
sql = "SELECT * FROM Table1 IN 'C:\MyFile.mdb' "
Set qdf = db.CreateQueryDef("", sql)
For Each fld In qdf.Fields
Debug.Print fld.Name
Next fld