VBA & Macro Problems

Use VBA to Insert Related Records into Microsoft Access Tables

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to insert data from an Access form into related Member and Contacts tables using VBA, but is encountering syntax errors or issues with SQL string construction.

Product: Microsoft Access
Device & OS: not provided
Scenario: Submitting an unbound form to insert parent and child records (Members and Contacts) simultaneously into a database.
Observed behavior: Generating SQL strings manually often results in syntax errors due to missing delimiters, unescaped apostrophes, or incorrect field names.

Before you start

Verify the exact spelling of your table and field names in the database schema to ensure they match your VBA code references exactly.

Solution 1Recommended

Format SQL Strings Correctly and Inspect with Debug.Print

Resolve syntax errors by properly quoting string values and verifying the generated SQL output in the Immediate window.

When constructing SQL strings in VBA, text values must be enclosed in single quotes, and dates in hash symbols. Using Debug.Print helps you see the final translated string instead of the VBA variables, making it much easier to spot missing spaces or delimiters.

Construct the INSERT statement

Build your INSERT INTO statement ensuring text fields are wrapped in single quotes and date fields in hash marks.

Add Debug.Print

Add Debug.Print sqlStringName immediately after constructing your SQL variable in the VBA editor.

Inspect in Immediate Window

Run the VBA code, then open the Immediate Window by pressing Ctrl+G to inspect the exact SQL output.

Test the Output

Copy the printed SQL string and paste it into a new Access Query in SQL View to test for specific database engine errors.

Pro Tip: To prevent syntax errors when a user enters an apostrophe (like O'Connor), always use the Replace function to double the apostrophes in your variables before appending them to the SQL string.

Solution 2

Wrap Insert Statements in a Database Transaction

Ensure data integrity by creating both the Member and Contact records together, rolling back the database if either insert fails.

Free Microsoft Office alternative

Try WPS Office for Your Document and Spreadsheet Needs

While Microsoft Access requires specific VBA handling for databases, you can manage your daily document, spreadsheet, and presentation tasks effortlessly with WPS Office. It provides a lightweight, highly compatible alternative to Microsoft Office, ensuring your workflows remain uninterrupted without the heavy subscription costs.

1. Visit the Official Website: Go to the official WPS Office website to access the latest secure download links.
2. Download the Installer: Click the Free Download button corresponding to your operating system (Windows, Mac, or Linux).
3. Install and Launch: Run the downloaded installer and follow the simple on-screen instructions to set up your new office suite.

Free and lightweight Office suiteSeamless compatibility with Microsoft Excel, Word, and PowerPoint formatsBuilt-in PDF editing and conversion toolsFamiliar user interface for easy migration

microsoft office alternative - wps office

Frequently Asked Questions

How do I prevent SQL injection when inserting records via VBA?

The most secure way to prevent SQL injection and quoting issues is to use DAO QueryDefs or ADO Command objects with parameters. By passing your form values as parameters rather than concatenating them into a raw SQL string, the database engine automatically handles escaping and sanitization.

Why do I get a 'Syntax error in INSERT INTO statement' error?

This error typically occurs if a field name in your table is a reserved database word (like 'Date', 'Name', or 'Index'), if you are missing commas between values, or if string/date values lack proper delimiters. Enclose reserved field names in square brackets (e.g., [Date]) to resolve this.

How do I retrieve the ID of the newly inserted parent record in Access VBA?

If you are executing raw SQL, you can immediately query 'SELECT @@IDENTITY' on the same connection to get the last auto-number generated. Alternatively, use a DAO Recordset to perform an .AddNew operation, update the record, and then read the ID directly from the recordset before closing it.

Excel & Data Problems