Fix E_FAIL Errors When Opening an Access ADODB Recordset
Question details
Open an ADODB recordset in Access when a query that works through DAO fails with E_FAIL.

- 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.
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.
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.
In Access, press Alt+F11 and open the module containing the rs.Open call.
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.
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.

Test the SQL and Provider Compatibility
Use this approach if explicit cursor and lock settings do not resolve the error.
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.

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.




