VBA & Macro Problems

How to Use Dynamic Fields and Updateable Recordsets in Access VBA

Emma Brown Oct 10, 2026 869 views

Question details

The user needs to construct dynamic field names in Access VBA and resolve read-only errors to successfully update records across joined Batch and AnswerKeys tables.

How to Fix Access VBA Dynamic Fields and Updateable Recordsets Errors

Product: Microsoft Access
Device & OS: not provided
Scenario: Grading user responses by comparing data across joined tables (Batch and AnswerKeys) using a loop in a VBA script.
Observed behavior: The VBA script throws read-only errors when attempting to update records, preventing the dynamic field values from saving.

Before you start

Before running update loops on your database, create a backup of your Access file and verify that the Microsoft DAO Object Library reference is enabled in your VBA editor.

Solution 1Recommended

Use dbOpenDynaset and String Variables for Dynamic Fields

By explicitly opening the recordset as an updateable Dynaset and correctly formatting field strings, you can dynamically read and write values in a loop without triggering read-only errors.

A common cause of read-only errors in Access VBA is opening a joined recordset without specifying the correct lock type, or attempting to update a query that lacks underlying primary keys.

You must explicitly declare dbOpenDynaset for the target table and ensure you use the .Edit and .Update methods when altering records programmatically.

Define dynamic field variables

Inside your VBA loop, build the field name strings using variables, such as 'questionField = "Q" & i' and 'answerField = CStr(i)'.

Open the recordset as a Dynaset

Modify your OpenRecordset statement to include the dbOpenDynaset argument to allow edits: 'Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)'.

Enter edit mode

Call the 'rs.Edit' method before assigning new values to your fields.

Update the fields dynamically

Reference the fields using your string variables, such as 'rs.Fields(questionField).Value = X', to assign the graded results.

Save changes

Call 'rs.Update' immediately after altering the fields to commit the changes to the database before moving to the next record.

Use dbOpenDynaset and String Variables for Dynamic Fields

Verify Primary Keys: If the recordset remains read-only even with dbOpenDynaset, check your underlying tables. Joined tables must have properly defined primary keys for a query to be updateable.

Free Microsoft Office alternative

Manage and Grade Data Easily with WPS Office

While Microsoft Access requires complex VBA and precise database architectures for tasks like record grading, you can often handle batch data processing effortlessly using robust spreadsheet formulas in WPS Office. WPS Office is a highly compatible, free Office alternative that simplifies data management.

1. Download WPS Office: Visit the official WPS Office website to download the free, lightweight installer.
2. Install the Suite: Run the setup file and follow the on-screen prompts to quickly install the software.
3. Open WPS Spreadsheet: Launch WPS Spreadsheet to import your data and start grading effortlessly using built-in formulas.

Highly compatible with Microsoft Excel (.xlsx, .xls) and macro-enabled workbooks (.xlsm).Free, lightweight, and uses fewer system resources than heavy database engines.Built-in advanced formulas (like IF, VLOOKUP, and XLOOKUP) that can replace complex VBA grading loops.Familiar spreadsheet interface ensuring a seamless migration from Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a read-only error when updating my Access recordset?

Recordsets will open in read-only mode if the underlying query lacks primary keys on the joined tables, if the recordset was opened as a snapshot, or if dbOpenDynaset was not explicitly declared in the OpenRecordset VBA method.

How do I reference column names using variables in Access VBA?

You can reference fields dynamically by passing a concatenated string into the Fields collection. For example, use rs.Fields("Q" & i).Value where 'i' is your loop counter.

What is the difference between dbOpenDynaset and dbOpenSnapshot in DAO?

dbOpenDynaset creates an updateable recordset that reflects additions, edits, and deletions made by others, while dbOpenSnapshot generates a static, read-only copy of the data which is useful for faster data retrieval when no updates are required.

Excel & Data Problems