Copy Access Tables Between Database Files Using VBA
Question details
The user needs to programmatically copy or transfer specific tables from one Microsoft Access database file to another.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Automating the transfer, export, or backup of database tables between two separate .accdb or .mdb files using VBA code.
- Observed behavior
- The goal is to securely append or make tables in a destination database, or to open a source database separately and export its tables to a target file.
Ensure that the source and destination Microsoft Access database files are not exclusively locked by other users, and that you have sufficient file permissions to read from the source and write to the destination directory.
Use an SQL Append or Make-Table Query
Execute an SQL statement with the 'IN' clause to push data directly to an external database file. This is the fastest and most reliable method.
By utilizing the SQL IN clause, you can target an external database without needing to instantiate a separate Access Application object. This approach can be used either for an Append Query (INSERT INTO) if the table already exists, or a Make-Table Query (SELECT INTO) if it does not.
Press ALT + F11 in Microsoft Access to open the Visual Basic for Applications (VBA) editor.
Right-click in the Project Explorer, select Insert, and then click Module to create a new code window.
Construct an SQL query using the IN clause. For example, to append data, use: CurrentDb.Execute "INSERT INTO TargetTable IN 'C:\Path\To\Destination.accdb' SELECT * FROM SourceTable;"
Place your cursor inside the written subroutine and press F5 to execute the code and copy the table data.
Use DoCmd.TransferDatabase with an Application Object
Open the source database in a separate Access instance and use the TransferDatabase method to export the table.
Looking for a Lightweight, Everyday Office Suite?
While WPS Office does not feature a relational database tool like MS Access, it is an exceptional, free alternative for your core productivity needs. It handles documents, spreadsheets, and presentations with ease, ensuring high compatibility with Microsoft Word, Excel, and PowerPoint formats without the burden of expensive subscriptions.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Install WPS Office: Run the downloaded installer and follow the on-screen instructions to complete the setup.
- 3. Open and Edit Your Files: Launch WPS Office to instantly open, edit, and save your existing Office documents, spreadsheets, and presentations.
Frequently Asked Questions
Can I copy only the table structure without the data using VBA?
Yes. When using the DoCmd.TransferDatabase method, you can set the StructureOnly parameter to True. This will export the table schema without transferring any of the existing records.
What happens if the destination table already exists when I run the transfer?
If you are using an append query (INSERT INTO), the records will be added to the existing table. If you use DoCmd.TransferDatabase, Microsoft Access will typically append a number to the table name (e.g., TableName1) to avoid overwriting the existing table.
Do I need to hardcode the database file paths in the VBA script?
No. You can use the FileDialog object (specifically msoFileDialogFilePicker) in your VBA code to prompt the user to browse and select the source or destination database files dynamically at runtime.
