How Do I Copy a table from one Access DB to another Access DB

Question

I am trying to automate a process to create a secondary database from a primary. Both DB's (MS Access) contain one table; the table in the secondary DB is a subset of the table in the primary.

Is there a simple way to copy a recordset frone one DB to another? I am using VBScript and ADO.

Thanks!

Is this a one time job? If you could, create a link table from base table into seconday table. The code then will become much simpler. — shahkalpesh
– shahkalpesh, Commented Jul 30, 2009 at 23:02

BIBD · Accepted Answer · 2009-07-31 19:27:04Z

1

Try the CopyObject method:

DoCmd.CopyObject "DestinationDatabaseName", "NewName", acTable, "SourceTable"

answered Jul 31, 2009 at 19:27

BIBD

15.5k26 gold badges89 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

onedaywhen · Accepted Answer · 2009-08-02 06:05:31Z

You can run Insert queries referencing external Access database files files (MDB, ACCDB, etc). For example:

strSQL = "INSERT INTO ServiceRecordInvoices " & _
    "( sriID, sriServiceRecordID, sriInvoiceDate, sriInvoiceNumber, " & _
                                "sriDescription, sriInvoiceAmount ) " & _
    " IN '" & strDatabasePathandNameTo & "' " & _
    "SELECT srpID, srpServiceRecordID, srpInvoiceDate, srpInvoiceNumber, " & _
                                "srpParts, srpPartsAmount " & _
    "FROM ServiceRecordParts IN '" & strDatabasePathandNameFrom & "';"

Note the two string variables strDatabasePathandNameTo and strDatabasePathandNameFrom. The above dynamic SQL code will work fine in either DAO or ADO.

If the two tables are identical then you could use the following (untested):

strSQL = "INSERT INTO ServiceRecordInvoices.* " & _
    " IN '" & strDatabasePathandNameTo & "' " & _
    "SELECT * " & _
    "FROM ServiceRecordParts IN '" & strDatabasePathandNameFrom & "';"

Collectives™ on Stack Overflow

How Do I Copy a table from one Access DB to another Access DB

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related