How to Copy Access Parent and Child Records with VBA
Question details
The user needs to copy hierarchical data, including parent, child, and grandchild records, using VBA while ensuring all foreign-key relationships remain intact.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Duplicating complex database records where related tables rely on auto-generated primary keys to maintain referential integrity.
- Observed behavior
- The goal is to programmatically duplicate the parent record, capture its new identity key, and apply that key to the copied child and grandchild records using DAO recordsets.
Ensure you have a complete backup of your Microsoft Access database backend before executing VBA scripts that perform bulk insertion operations.
Use DAO Recordsets to Copy Relational Records
Utilizing DAO (Data Access Objects) recordsets allows you to capture the newly generated AutoNumber primary key of a copied parent record and apply it immediately to related child records.
DAO recordsets are highly recommended over repeatedly running standard append queries when copying relational data. They allow you to accurately retrieve the new primary key of a parent record as soon as it is inserted, preventing data orphans.
When generating your SQL statements in VBA, be careful to exclude AutoNumber fields and calculated fields from your INSERT operations, as the database engine handles these automatically.
Open a DAO recordset for the parent table. Add a new record (`.AddNew`), populate the fields by referencing `.Fields("FieldName")` from the original record, and then `.Update` the recordset.
Immediately after updating the parent record, use `.LastModified` on the recordset to navigate to the new record and store its AutoNumber primary key into a VBA Long Integer variable.
Open a recordset containing the related child records. Loop through them using `Do While Not .EOF`. For each iteration, generate an INSERT statement that uses the captured parent key as the new foreign key.
If grandchild records exist, retrieve them directly from their underlying table or query. Capture the newly generated key of each child record inside your loop, and use it as the foreign key when inserting the grandchildren.
If you are using SQL strings to insert records, include `Debug.Print strSQL` in your code. Open the Immediate Window (Ctrl+G) to inspect the completed SQL statement for missing commas, incorrect quotes, or apostrophes.
Looking for a Lightweight, Free Microsoft Office Alternative?
While complex relational databases require Microsoft Access, you can handle standard data management, reporting, and daily office tasks efficiently with WPS Office. It provides a robust, highly compatible alternative to Microsoft Word, Excel, and PowerPoint without the expensive subscription fees.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Install the Software: Run the installer and follow the on-screen instructions to complete the setup in minutes.
- 3. Open Your Data Files: Launch WPS Spreadsheet to import your exported Access data, CSV files, or Excel workbooks seamlessly.
Frequently Asked Questions
Why should I use DAO recordsets instead of standard DoCmd.RunSQL append queries?
DAO recordsets allow you to stay on the newly created parent record to capture its generated AutoNumber primary key. Standard append queries execute blindly, making it difficult to accurately retrieve the newly assigned ID needed for the child records.
How do I troubleshoot syntax errors in my VBA SQL strings?
Insert `Debug.Print strSQL` right before the execution line in your VBA code. This prints the fully concatenated SQL statement into the Immediate Window (Ctrl+G), allowing you to visually check for missing spaces, commas, or mismatched quotes.
Can I copy nested grandchild records using this same method?
Yes. Within your loop for the child records, you must capture the new identity key of the inserted child record, then open a nested loop to duplicate the related grandchild records, applying the new child key as their foreign key.
Why do I get errors when referencing fields in my DAO recordset loop?
Ensure you are explicitly referencing the field using the syntax `.Fields("YourFieldName")`. If the field is an AutoNumber or a calculated field, do not include it in the destination fields of your INSERT statement.
