Access 2007 - 无法初始化数据提供程序 [英] Access 2007 - Data provider could not be initialized

问题描述

我将 Access 2000 应用程序转换为 Access 2007，该应用程序使用 ADO 连接到 MsSql-2000.
2 问题:
1. 我无法对表单进行过滤(右键单击 -> 过滤) - 什么都不返回.
2. 当我尝试使用过滤器时，我不断收到数据提供程序无法初始化"的消息

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

有人有想法吗?

推荐答案

根据我的经验，错误无法初始化数据提供程序"最常发生，因为连接字符串对于 ADO 连接来说并不完美.在创建使用错误连接的 ADO 记录集并将其绑定到窗体或报表之前，不会出现该错误.

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.

如果使用 Access 项目(请参阅:创建访问项目)，确保在数据链接属性中验证服务器和初始数据库(如果提供)是正确的.在 Access Projects 中，连接字符串被嵌入到项目本身中.

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.

如果在 VBA 中使用连接字符串，请确保正确的数据提供程序是正确的.对于 Access 2003(MDB 和 ADP)中的 ADO 连接，提供程序必须是 Microsoft.Access.OLEDB.10.0，否则记录集不能绑定到表单和报表.

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.

示例:

' 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

请注意，当主连接(即数据链接)不正确并且尝试使用 VBA 将记录集对象分配给表单的记录集时，Access 项目将创建错误数据提供程序无法初始化".因此，如果 Access 项目的主连接不正确，则示例的最后一行将失败.(即使项目的连接与记录集对象的连接完全不同也是如此.)

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.)

这篇关于Access 2007 - 无法初始化数据提供程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！