Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

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.

Thanks!

0
RichW
Asked:
RichW
  • 6
1 Solution
 
casassusCommented:
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"



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

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

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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.


0
 
É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).
0
 
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.





0
 
RichWAuthor Commented:
Sorry.

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

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

emoreau thanks too.  

0
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now