vba Access 2007 - 无法初始化数据提供程序

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/1939821/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 11:08:02  来源:igfitidea点击:

Access 2007 - Data provider could not be initialized

ms-accessvba

提问by Noam

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

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

somebody has an idea?

有人有想法吗?

回答by Mike

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.

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

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.

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

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.

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

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

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

回答by Ted Boosalis

Also, using Teradata ODBC Connection (Teradata, not Microsoft for Teradata), you can bind to forms using ADO. The data provider not initialized error will come up if you use a datasheet as the form and you attempt to filter on the sheet.

此外,使用 Teradata ODBC 连接(Teradata,不是 Microsoft for Teradata),您可以使用 ADO 绑定到表单。如果您使用数据表作为表单并尝试在表单上进行过滤,则会出现数据提供者未初始化错误。