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!

Answer 1

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 & "';"

Answer 2

Try the CopyObject method:

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