Runtime Error executing Make Table Query

[Guest]

I am using the following code to execute a Make Table Query to add table to
the database currently open:

Set db = OpenDatabase("ExternalDatabase.mdb")
Set rec = db.CreateQueryDef("", "SQL with IN Clause pointing to current db")
rec.Execute dbFailOnError
rec.close

It works fine when I first open the database, but when I try to run the code
again with another set of external records I get the following error 3734:
The database has been placed in a state by user 'Admin' on machine <machine>
that prevents it from being opened or locked.

Has anyone else experienced this?

Thanks.

 

[Allen Browne]

Instead of OpenDatabase, use the one that is already open, i.e.:
Set db = dbEngine(0)(0)

For more info on the DAO object model, see:
DAO: Data Access Objects
at:
http://allenbrowne.com/ser-04.html

 

[Guest]

Maybe I did not explain well enough. The OpenDatabase method opens an
external database. It then creates a QueryDef in the external database,
momentarily, and then executes, making a table in the currentdb via the IN
clause of the SQL string.

If I did what you are suggesting, the SQL string would not find the tables;
they are not in the database that is already open.

Thanks for your reply.

DEI

 

[Allen Browne]

From the current database, you can execute this string to read the table
from the other database, and create a copy in the current database:
SELECT Table1.* INTO MyTargetTable
FROM Table1 IN 'C:\MyOtherDatabase.mdb';

The IN clause finds Table1 in the other database, even if it is not linked.

Does that achieve the result you are aiming for?