How to populate a ListBox with a ADODB.Recordset (Error 91) To Do Autocompletion in Access

Question

I work on an Access DB and I have to use a Datasource connection to a SQL Server.

To do that I use the ADODB object with :

-ADODB.Connection

-ADODB.Recordset

Code Up-to-date, following an observation of 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

(This code (a part of the code) is a way to do an Autocompletion in Access with a TextBox and a ListBox)

And I have an error 91 when I run this code : "Error 91: Object variable or With block variable not set" .

I don't understand how to resolve this issue.

Thanks in advance.

Answer 1

I solved my problem (Error 91), There was three problems : the creation of the ADODB.Connection, the * in the Select (Thanks to HansUp) and the Set for the listbox.recordset (Thanks to HansUp again)

I solved the error :

        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