How to Retrieve Data From Another Access Dataase Without Linking to It?

I would like to be able to pull data from another Access database without
linking to its tables. If I create a database object that references the
other Access database the only way I can seem to get data back is as a
recordset. But I cannot use a recordset as input to another query.

For example, if I want to extract data from a table in the other database
and append it to a table in the current database I would have to walk the
returned record set and copy each row into the target table.

With a true back end server I could use a back end query that returned a
recordset and use that query as input to an append query.

Obviously, that doesn't work with Access as there isn't a server to pass the
back end query to. What if the other database was opened in another
instance of Access -- could a back end query be made to work and if so how?

Stewart Berman wrote:

I would like to be able to pull data from another Access database without
linking to its tables. If I create a database object that references the
other Access database the only way I can seem to get data back is as a
recordset. But I cannot use a recordset as input to another query.

For example, if I want to extract data from a table in the other database
and append it to a table in the current database I would have to walk the
returned record set and copy each row into the target table.

With a true back end server I could use a back end query that returned a
recordset and use that query as input to an append query.

Obviously, that doesn't work with Access as there isn't a server to pass the
back end query to. What if the other database was opened in another
instance of Access -- could a back end query be made to work and if so how?

SQL allows for an IN phrase in the FROM clause. Eg.

SELECT . . .
FROM table IN "path\otherdb.mdb"

The IN phrase can be a full connection string that is the
same as the Connect property of a linked table's TableDef
object. For more information about IN see the SQL Reference
in Help.

--
Marsh
MVP [MS Access]

Marshall Barton wrote:

Stewart Berman wrote:

I would like to be able to pull data from another Access database without
linking to its tables. If I create a database object that references the
other Access database the only way I can seem to get data back is as a
recordset. But I cannot use a recordset as input to another query.

For example, if I want to extract data from a table in the other database
and append it to a table in the current database I would have to walk the
returned record set and copy each row into the target table.

With a true back end server I could use a back end query that returned a
recordset and use that query as input to an append query.

Obviously, that doesn't work with Access as there isn't a server to pass the
back end query to. What if the other database was opened in another
instance of Access -- could a back end query be made to work and if so how?

SQL allows for an IN phrase in the FROM clause. Eg.

SELECT . . .
FROM table IN "path\otherdb.mdb"

The IN phrase can be a full connection string that is the
same as the Connect property of a linked table's TableDef
object. For more information about IN see the SQL Reference
in Help.

The SQL Reference section is not terribly clear (except to say there is a
big performance hit) but it looks like it might work.

It may be adequate for what I am doing since performance is not an issue.

Stewart Berman wrote:

Marshall Barton wrote:

Stewart Berman wrote:

I would like to be able to pull data from another Access database without
linking to its tables. If I create a database object that references the
other Access database the only way I can seem to get data back is as a
recordset. But I cannot use a recordset as input to another query.

For example, if I want to extract data from a table in the other database
and append it to a table in the current database I would have to walk the
returned record set and copy each row into the target table.

With a true back end server I could use a back end query that returned a
recordset and use that query as input to an append query.

Obviously, that doesn't work with Access as there isn't a server to pass the
back end query to. What if the other database was opened in another
instance of Access -- could a back end query be made to work and if so how?

SQL allows for an IN phrase in the FROM clause. Eg.

SELECT . . .
FROM table IN "path\otherdb.mdb"

The IN phrase can be a full connection string that is the
same as the Connect property of a linked table's TableDef
object. For more information about IN see the SQL Reference
in Help.

The SQL Reference section is not terribly clear (except to say there is a
big performance hit) but it looks like it might work.

It may be adequate for what I am doing since performance is not an issue.


I have not had an issue with performance, but I don't
connect to anything other than Access and Excel files. If
you have trouble figuring out what the connect string should
be, manually link to a table, and then use the Immediate
Window to see what Access did:
?CurrentDb.TableDefs!thelinkedtable.Connect

--
Marsh
MVP [MS Access]