Vba – Access 2007 – Data provider could not be initialized

ms-accessvba

I converted Access 2000 app to Access 2007, the App is connect to MsSql-2000 with ADO.
2 Problems:
1. I can not do filter on the form (right click -> filter) – returns nothing.
2. I keep getting "Data provider could not be initialized" mostly when I'm trying to play with the filter

somebody has an idea?

Best Answer

In my experience, the error "Data provider could not be initialized" most often occurs because the connection string is not perfect for an ADO connection. The error is not realized until an ADO recordset that uses the faulty connection is created and bound to a form or report.

If working with an Access Project (See: Create An Access Project), ensure that the server and initial database (if provided) are verified to be correct in the Data Link Properties. In Access Projects, the connection string is baked into the project itself.

If using a connection string in VBA, ensure that the correct Data Provider is correct. For ADO connections in Access 2003 (MDB and ADP), the Provider must be Microsoft.Access.OLEDB.10.0, otherwise the recordsets cannot be bound to forms and reports.

Example:

' Bind an Access 2003 ADO recordset to an Access form
' Note that the Data Provider is SQL Server (because "Data Provider = SQLOLEDB")

' Declare objects
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection

With cn

     .CursorLocation = adUseClient

    ' LockType must be adLockOptimistic
    ' See: http://support.microsoft.com/kb/281998
    .LockType = adLockOptimistic

    .Open "Provider=Microsoft.Access.OLEDB.10.0;" & _
        "Data Source=ServerName;"
        "Initial Catalog=OptionalDatabaseName;" & _
        "Trusted_Connection=Yes;" & _
        "Data Provider=SQLOLEDB;"

    Set rs = .Execute("SELECT order_id FROM dbo.Orders")

End With

' This will throw the error "Data provider could not be initialized"
' if the Provider is incorrect
Set Me.Recordset = rs

Note that an Access project will create the error "Data provider could not be initialized" when the main connection (i.e., Data Link) is incorrect and an attempt was made with VBA to assign a recordset object to a form's recordset. So, the last line of the example would fail if the main connection of the Access project is incorrect. (This is the case even if the connection of the project is completely different from the connection of the recordset object.)

Related Topic