Others

Fix E_FAIL Errors When Opening an Access ADODB Recordset

Phi Hung VoPhi Hung Vo Nov 22, 2026 868 views

Question details

Open an ADODB recordset in Access when a query that works through DAO fails with E_FAIL.

Fix E_FAIL Errors When Opening an Access ADODB Recordset
Product
Microsoft Access with ADODB
Device & OS
not provided
Scenario
A VBA procedure opens a lengthy SELECT query using an ADODB connection and recordset.
Observed behavior
The ADODB connection or recordset fails with E_FAIL even though the same SQL works through DAO.
Before you start

Open the VBA procedure that creates the recordset and identify the SQL string and connection it uses. In the VBA editor, check Tools > References for a Microsoft ActiveX Data Objects library if you use ADODB types and constants.

Solution 1Recommended

Open the ADODB Recordset with Explicit Cursor and Lock Types

Use explicit cursor and locking settings when the recordset fails to open with its current settings.

1
Open the VBA procedure

In Access, press Alt+F11 and open the module containing the rs.Open call.

2
Set the cursor and lock types

Update the call to use a keyset cursor and optimistic locking: rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic. Here, pstrSQL is the SQL string and rs is an ADODB.Recordset.

3
Run the procedure

Run the procedure again and check whether the recordset opens. If it still fails, test the SQL separately and check whether the provider supports the requested cursor and lock types.

Open the ADODB Recordset with Explicit Cursor and Lock Types
Check RecordCount: RecordCount may not be accurate until the records are fetched. If the recordset is not empty and supports moving to the last record, use rs.MoveLast before checking rs.RecordCount.
Free Microsoft Office alternative

Work with Office Files in WPS Office

WPS Office is a free, lightweight alternative for working with documents, spreadsheets, and presentations in a familiar interface. It supports common Office formats for an easier transition, but it does not fix Access ADODB errors or replace Access database functionality.

Open and edit Microsoft Word, Excel, and PowerPoint formats, including DOCX, XLSX, and PPTXUse a familiar interface for everyday document and spreadsheet workReview spreadsheet data alongside your Access troubleshooting workflowWork with a lightweight office suite available for free
microsoft office alternative - wps office

Frequently Asked Questions

Why does my SQL work in DAO but fail in ADODB?

DAO and ADODB can use different connection and provider behavior. Test a simple SELECT through the same ADODB connection, then check the original query's functions, parameters, and table references against the provider's supported syntax.

Which cursor and lock types should I try first?

Try adOpenKeyset and adLockOptimistic in the rs.Open call. If the provider does not support that combination or the query is not updatable, test settings supported by the provider and the intended operation.

Why is ADODB RecordCount not accurate immediately?

Some cursor and provider combinations do not know the total until records are fetched. If the recordset is not empty and supports moving to the last record, call rs.MoveLast before checking rs.RecordCount.

Can WPS Office fix an Access ADODB E_FAIL error?

No. The error must be investigated in Access, VBA, the SQL, and the ADODB provider. WPS Office can open common Office documents and spreadsheets but does not replace Access database functionality.