VBA & Macro Problems

How to Build an UPDATE SQL Statement in Microsoft Access VBA

Algirdas Jasaitis Oct 9, 2026 869 views

Question details

The user needs to correctly construct and execute an UPDATE SQL statement in Microsoft Access VBA to save data from an unbound form to a table.

How to Build an UPDATE SQL Statement in Microsoft Access VBA

Product: Microsoft Access
Device & OS: not provided
Scenario: Updating a supplier record in tblSuppliers when a user clicks the Save button on an unbound form.
Observed behavior: Constructing the SQL string manually causes syntax challenges, particularly with escaping text, handling dates, managing apostrophes, and correctly concatenating control names.

Before you start

Determine if your Access form genuinely needs to be unbound, as utilizing a bound form is significantly easier and generally requires zero custom VBA code to save records.

Solution 1Recommended

Bind the Form to the Table (Recommended)

The most efficient and reliable method to save data in Microsoft Access is by binding the form directly to the underlying table, which completely eliminates the need for manual SQL construction.

Microsoft Access is specifically designed to handle data binding natively. When you use a bound form, the application automatically handles all the complexities of formatting text, dates, and apostrophes without risking SQL injection or syntax errors.

Set the form's RecordSource

Open your form in Design View. Open the Property Sheet, navigate to the 'Data' tab, and set the 'RecordSource' property to the table you want to update (e.g., tblSuppliers).

Bind individual controls

For each text box or input control on the form, find the 'Control Source' property in the Property Sheet and select the corresponding field name from the table.

Let Access handle the save

Once bound, Microsoft Access will automatically save the data back to the table when the user navigates to a new record or closes the form. No VBA UPDATE code is required.

Bind the Form to the Table (Recommended)

Solution 2

Construct the UPDATE SQL Statement Safely for Unbound Forms

If your project strictly requires an unbound form, you must use helper functions in VBA to safely escape strings, format dates, and handle embedded apostrophes before running the UPDATE query.

Free Microsoft Office alternative

Looking for a Simpler Way to Manage Tabular Data?

While Microsoft Access requires complex VBA coding and SQL knowledge for simple tasks, managing data lists is often much easier using powerful spreadsheets. WPS Office provides a lightweight, free, and highly compatible alternative for data tracking.

1. Download the WPS Office installer: Visit the official WPS Office website and download the free software for your operating system.
2. Install and launch the suite: Run the setup file and follow the on-screen instructions. Once installed, open the application.
3. Use WPS Spreadsheet for data: Open WPS Spreadsheet to organize, filter, and validate your data easily without the complexities of relational database management.

Free and lightweight alternative to Microsoft OfficeExcellent compatibility with Microsoft Excel formats (.xlsx, .xls) for managing data listsFamiliar user interface requiring no steep learning curveEasily handle data filtering, sorting, and forms without writing a single line of SQL or VBA code

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA UPDATE statement crash when a user types an apostrophe?

In SQL, apostrophes (single quotes) are used to denote the beginning and end of a text string. If a user types an apostrophe in a name (like O'Connor), it prematurely terminates the SQL string, causing a syntax error. You must escape it by replacing the single quote with two single quotes in your VBA code.

What is the difference between a bound and unbound form in Access?

A bound form is linked directly to a table or query via the RecordSource property, meaning Access automatically handles fetching and saving the data. An unbound form has no direct link, meaning you must write manual VBA code and SQL statements to populate the form and save any changes.

How do I correctly insert dates into an Access SQL string using VBA?

When concatenating dates into an Access SQL statement, you must wrap the date values in hash tags (#). Furthermore, the Access database engine expects dates in the US format (mm/dd/yyyy), so it is highly recommended to explicitly format the date variable in VBA before adding it to the SQL string.

Excel & Data Problems