Others

How to Use an ADODB Recordset as an Access Subform Data Source

Maira Mehtab Sep 28, 2026 869 views

Question details

The user wants to display records retrieved by an ADODB recordset within a Microsoft Access subform after a specific category is selected.

Product: Microsoft Access
Device & OS: not provided
Scenario: Filtering database records based on user selection and displaying the returned ADODB recordset in an Access subform using VBA.
Observed behavior: Attempting to assign the recordset object to the RecordSource property fails because RecordSource expects a string (table, query, or SQL statement), not an ADODB recordset object.

Before you start

Ensure you have correctly established your ADODB database connection in VBA and that your SQL query successfully returns records before attempting to bind it to the subform.

Solution 1Recommended

Assign the ADODB Recordset to the Form's Recordset Property

Use the VBA 'Set' keyword to assign your ADODB recordset object directly to the subform's Recordset property instead of using RecordSource.

Microsoft Access forms have both a RecordSource and a Recordset property. The RecordSource property is designed to accept text values such as table names, saved queries, or SQL statements. When working with an ADODB recordset object in VBA, you cannot pass it to RecordSource; instead, you must assign it to the Recordset property.

Open the VBA Editor

Press ALT + F11 to open the VBA editor and navigate to the code module for your form where the category selection event occurs.

Locate the Assignment Code

Find the line of code where you are attempting to bind the ADODB recordset to the subform.

Use the Recordset Property

Replace any code attempting to use Me.RecordSource with the correct object assignment using the Set keyword: Set Me.Recordset = rsMoldsByType

Refresh the Data

If necessary, ensure your subform updates by calling Me.Requery or Me.Refresh after assigning the recordset.

Correct Syntax Matters: Always remember to use the 'Set' keyword when assigning objects in VBA. Missing 'Set' will result in an Invalid Use of Property error.

Free Microsoft Office alternative

Discover WPS Office for Your Daily Productivity Needs

While Microsoft Access handles complex database management, for your everyday document, spreadsheet, and presentation tasks, WPS Office offers a free, lightweight, and highly compatible alternative to Microsoft Office. Enjoy a familiar interface and seamless migration without the heavy subscription costs.

1. Download the Installer: Visit the official WPS website and download the free WPS Office installer for your operating system.
2. Install the Suite: Run the setup file and follow the quick on-screen instructions to install Writer, Spreadsheets, and Presentation tools.
3. Open Office Files Instantly: Launch WPS Office and open your existing Microsoft Office documents to continue working with perfect format retention.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats (.docx, .xlsx, .pptx).Lightweight installation with minimal system resource consumption.Built-in advanced PDF editing and conversion tools.Familiar user interface requiring zero learning curve for Office users.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a Type Mismatch error when assigning a recordset to RecordSource?

The RecordSource property is designed to accept string values (like a table name or SQL statement). An ADODB recordset is a data object, so it must be assigned to the Recordset property using the 'Set' keyword in VBA.

Can I use DAO recordsets instead of ADODB for Access subforms?

Yes, Microsoft Access forms seamlessly support DAO recordsets. Similar to ADODB, you must use 'Set Me.Recordset = yourDAORecordset' to bind the data correctly to the form.

Do I need to rebind the recordset every time a category changes?

Yes, if your ADODB recordset is rebuilt or filtered based on a new category selection, you will need to execute the 'Set Me.Recordset' assignment again in your event handler to update the subform's displayed data.

Office Quick Solutions