ADOX - Copy table to another Access db

Hello all,

I have two Access 97 databases.  I need to copy certain tables from one to the other.

I'm using ADOX's catalog to find the table names.

My problem is that the source db is opened through a .mdw file with the user id and password, and when I try to do the copy I'm getting an error saying that that the db is opened by another user (ME!).

I tried using the table name from the catalog and doing a DoCmd.CopyObject and the TransferDatabase method, using the Access 8.0 Object Library, but I get the same error.

Does anyone have any ideas how to copy tables from a secured Access 97 db to an unsecure Access 97 db?

I will give 300 points and an A to anyone who can come up with the answer.


Who is Participating?
Try to use a SQL clause like this one

I suppose that your ADO catalog name's is MyBase,your dest database name's is Dest.mdb, your dest table name's MyTableDest, your origine table name's MyTableOri

MyBase.execute "insert into MyTableDest in 'C:\...\Dest.mdb' select * from MyTableOri"

RichWAuthor Commented:
I tried this, but the catalog object doesn't have an Execute method.

RichWAuthor Commented:
I tried this, but the catalog object doesn't have an Execute method.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RichWAuthor Commented:
See the problem is I'm trying to copy the table object with the data.

I've used the code I have on two db's without security, and the tables copy perfectly.

The tables do not yet exist in the destination db.

Éric MoreauSenior .Net ConsultantCommented:
<The tables do not yet exist in the destination db.>

instead of using INSERT INTO, use SELECT INTO (which will create the table).
RichWAuthor Commented:
Here's my code:

cat.ActiveConnection.Execute "Select into """ & strTblName & """  in  """ & strTarget & """"

The select statement comes out to:
Select into "CCPROCash"  in  "C:\Gateway\Gateway.mdb"

I get an error that says that there's a reserverd word or argument name that is misspelled.  This is not the case.  The table name is correct, and so is the path to the db.

RichWAuthor Commented:

Here's the select statement:
Select into CCPROCash  in  "C:\Gateway\Gateway.mdb" select * from CCPROCash

RichWAuthor Commented:
Thanks casassus.  I used the execute on the connection object and it worked.

emoreau thanks too.  

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.