Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 609
  • 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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