Solved

ADOX - Copy table to another Access db

Posted on 2001-08-06
8
581 Views
Last Modified: 2013-11-27
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
Comment
Question by:RichW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
8 Comments
 
LVL 3

Accepted Solution

by:
casassus earned 300 total points
ID: 6356323
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
 
LVL 4

Author Comment

by:RichW
ID: 6356405
I tried this, but the catalog object doesn't have an Execute method.

0
 
LVL 4

Author Comment

by:RichW
ID: 6356419
I tried this, but the catalog object doesn't have an Execute method.

0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 4

Author Comment

by:RichW
ID: 6356426
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6356434
<The tables do not yet exist in the destination db.>

instead of using INSERT INTO, use SELECT INTO (which will create the table).
0
 
LVL 4

Author Comment

by:RichW
ID: 6356497
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
 
LVL 4

Author Comment

by:RichW
ID: 6356524
Sorry.

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

0
 
LVL 4

Author Comment

by:RichW
ID: 6356932
Thanks casassus.  I used the execute on the connection object and it worked.

emoreau thanks too.  

0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question