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

Answer 2

You told us that code throws Error 91, "Object variable or With block variable not set". Unfortunately, you didn't indicate which line triggers the error. That forces us to guess where the problem lies.

One issue is here:

Me.Liste_choix.Recordset = rs

That attempts an assignment of one object to another. The = sign is sufficient for assignments with simple data types ... ie MyVariable = 2. However you must include the Set keyword with object assignments.

Set Me.Liste_choix.Recordset = rs

Although you should make that change, I'm not certain that was the cause of error 91; I would have guessed Access would complain "Invalid use of property" instead.

The SELECT statement is another problem, but again I'm uncertain whether it contributes to the error you reported. The WHERE clause uses a Like comparison with a pattern which has * as the wild card character. That query might return what you expect when you run it from DAO. But you're using ADO which treats * as just an asterisk character without any special meaning. So that query probably returns no rows when you run it from ADO. Replace * with %.

As general advice, if your code module does not already include Option Explicit in its Declarations section, add it. Then run Debug->Compile from the VB Editor's main menu. Fix anything the compiler complains about. Make sure you've done those things before any further troubleshooting.