如何使用ADODB.Recordset填充ListBox（错误91）在Access中进行自动完成

Question

我在处理一个Access数据库时需要使用数据源连接到SQL Server。为了实现这个目的，我使用ADODB对象，其中包括：

- ADODB.Connection - ADODB.Recordset 根据Ian Kenney的观察，代码已经更新

   Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection
   Dim rs As ADODB.Recordset

   cnn.ConnectionString = "driver={SQL Server};provider=SQLOLEDB;server=10.****;uid=****readonly;pwd=****readonly;database=****"
   cnn.Open

  Set rs = cnn.Execute("SELECT [MATRI], [NOMPRE] FROM SCHEME_DB.TABLE WHERE NOMPRE LIKE '*" & Me.Textbox_recherche.Text & "*'")



  Me.Liste_choix.RowSourceType = "Table/List"
  Me.Liste_choix.Recordset = rs

  rs.Close
  cnn.Close

这段代码（部分代码）是使用TextBox和ListBox在Access中进行自动完成的一种方式。

但是当我运行这段代码时，出现了错误91：“错误91：对象变量或With块变量未设置”。

我不明白如何解决这个问题。

谢谢您提前。

- Quentin T.

Answer 1

我解决了我的问题（错误91），其中有三个问题：ADODB.Connection的创建，Select中的*（感谢HansUp）以及listbox.recordset的Set（再次感谢HansUp）。

我解决了这个错误：

        Private Sub Textbox_recherche_Change()

                Dim cnn As ADODB.Connection
                Set cnn = New ADODB.Connection
                Dim rs As ADODB.Recordset

'A important point to solve the Error 91 is to declare your ADODB.Connection with .Properties like that : (I don't use Windows NT authentification but the SQL Server authentification)


                With cnn
                    .Provider = "Microsoft.Access.OLEDB.10.0"
                    .Properties("Data Provider").Value = "SQLOLEDB"
                    .Properties("Data Source").Value = "10.******"
                    .Properties("User ID").Value = "*****readonly"
                    .Properties("Password").Value = "*****readonly"
                    .Open
                End With

    'The second point is to replace the * in the search for the autocompletion by the %

              Set rs = cnn.Execute("SELECT [NOMPRE] FROM ****.***** WHERE NOMPRE LIKE '%" & Me.Textbox_recherche.Text & "%'")

    'You have to declare the RowSourceType of your listbox to "Table/Query"

            Me.Liste_choix.RowSourceType = "Table/Query"

    'And Finally to SET your recordset like that:

            Set Me.Liste_choix.Recordset = rs

               rs.Close
               cnn.Close

               Set cnn = Nothing
               Set rs = Nothing              

            End Sub

- Quentin T.

Answer 2

您告诉我们代码抛出错误91，"对象变量或 With 块变量未设置"。不幸的是，您没有指示哪一行触发了错误。这迫使我们猜测问题所在。

一个可能的问题出现在这里：

Me.Liste_choix.Recordset = rs

这是一种将一个对象赋值给另一个对象的尝试。对于简单数据类型的赋值，如MyVariable = 2，使用等号=即可完成。但是对于对象赋值，必须包含关键字Set。

Set Me.Liste_choix.Recordset = rs

尽管您应该进行更改，但我不确定那是错误91的原因； 我本以为Access会抱怨“属性无效”。

"SELECT"语句是另一个问题，但同样我不确定它是否导致了您报告的错误。 "WHERE"子句使用"Like"比较模式中的"*"通配符。 当您从DAO运行查询时，该查询可能返回您所期望的结果。 但是您正在使用将"*"视为普通字符而没有任何特殊含义的ADO。 因此，当您从ADO运行它时，该查询可能不返回任何行。 将"*"替换为"%"。

作为一般建议，如果您的代码模块在其声明部分中尚未包括"Option Explicit"，请添加它。 然后从VB编辑器的主菜单中运行Debug-> Compile。 修复编译器抱怨的任何内容。 在进一步进行故障排除之前，请确保已完成这些操作。

- HansUp

---

谢谢，我给你奖励了，但我认为一个重要的问题是如何声明ADODB.Connection对象。我们必须使用.Properties来声明这个对象（不太清楚为什么...）就像在我的答案中一样... - Quentin T.

Answer 3

在使用记录集之前，您已经关闭了记录集和连接

在此处关闭了rs

   rs.Close  

连接在此处关闭

   cnn.Close

Me.Liste_choix.RowSourceType = "Table/List"

这里使用了rs

Me.Liste_choix.Recordset = rs

更新 来自文档：

使用Close方法关闭Connection对象也会关闭与该连接关联的任何活动Recordset对象。与您要关闭的Connection对象相关联的Command对象将持久存在，但它将不再与Connection对象关联；也就是说，其ActiveConnection属性将设置为Nothing。此外，Command对象的Parameters集合将清除任何提供程序定义的参数。

使用Close方法关闭Recordset、Record或Stream对象会释放相关数据以及您可能通过此特定对象对数据的独占访问权。稍后可以调用Open方法以相同或修改后的属性重新打开对象。在Recordset对象关闭时，调用任何需要活动游标的方法都会生成错误。

SQL注入 直接从用户输入构建sql也存在SQL注入风险。
这个问题(MS Access prepared statements)展示了如何使用参数化查询-值得一看。

- Ian Kenney

---

这不是错误的原因，当我将rs.Close和cnn.Close移动到Me.Liste_choix.Recordset = rs下面时，我已经得到了错误。 - Quentin T.

你尝试过将rs.close / cnn.close移动到使用记录集之后吗？ - Ian Kenney

是的...Me.Liste_choix.RowSourceType = "Table/List" Me.Liste_choix.Recordset = rsrs.Close cnn.Close - Quentin T.

ADODB.Connection 对象与 DAO.Database 对象不同... 我必须使用 ADODB 对象，因为它连接到 SQL Server 的数据源。 - Quentin T.

假设你已经检查了连接是否打开正常，记录集中有一些数据，并且Me.Textbox_recherche.Text和Me.Liste_choix是有效的控件，那么我认为我不能提供更多帮助，因为我手边没有访问权限。 - Ian Kenney

这个视频介绍了一些调试访问代码的方法-可能会对你有帮助。 - Ian Kenney