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.

Surf

Answer 1

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.

Boyd

Thanks, I give you the bounty but I think an important problem is the way how to declare the ADODB.Connection object. We have to declare this object with .Properties (don't know exactly why...) like in my answer... – Surf

Answer 2

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

Surf

Answer 3

You have closed the recordset and connection before you use it

rs closed here

   rs.Close  

and the connection is closed here

   cnn.Close

Me.Liste_choix.RowSourceType = "Table/List"

rs used here

Me.Liste_choix.Recordset = rs

Update From the docs:

Using the Close method to close a Connection object also closes any active Recordset objects associated with the connection. A Command object associated with the Connection object you are closing will persist, but it will no longer be associated with a Connection object; that is, its ActiveConnection property will be set to Nothing. Also, the Command object's Parameters collection will be cleared of any provider-defined parameters.

Using the Close method to close a Recordset, Record, or Stream object releases the associated data and any exclusive access you may have had to the data through this particular object. You can later call the Open method to reopen the object with the same, or modified, attributes. While a Recordset object is closed, calling any methods that require a live cursor generates an error.

SQL INJECTION There is also an sql injection risk by building sql directly from user input.
This question (MS Access prepared statements) shows how to use a parametrised query - might be worth a look.

Pretender

This is not the reason of the error, when I move the rs.Close and cnn.Close under Me.Liste_choix.Recordset = rs, I already got the error. – Surf

did you try move the rs.close / cnn.close to after you use the recordset? – Pretender

Yes... Me.Liste_choix.RowSourceType = "Table/List" Me.Liste_choix.Recordset = rs rs.Close cnn.Close – Surf

The ADODB.Connection item is different than the DAO.Database object... And I have to use this ADODB item because of the datasource connection to a SQL Server. – Surf

Well assuming you have checked the connection opened ok and the recordset has some data in, that Me.Textbox_recherche.Text and Me.Liste_choix are valid controls, then I don't think I can help much more, as don't have access to hand. – Pretender

This video walks you through some ways to debugging access code - might help you – Pretender