ADODB recordset empty - I'm stumped

[PacMedTran]

I'm populating a listbox with data from an Access table. I've done this
twice before with no problems, but this time I'm stuck. I need another pair
of eyes to tell me what I'm doing wrong.

UserForm_Activate has this code, copied directly from a macro that works:

Set con1 = New ADODB.Connection
Set cmd1 = New ADODB.Command
Set varRecordSet1 = New ADODB.Recordset
con1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & _
"User ID=Admin;" & "Data Source=" & _
"F:\CL\EML\Access\testMDsearch.mdb"

con1, cmd1 and varRecordSet1 are all declared as public variables.

This is the code to populate the listbox:

varCommand = "SELECT MDList.DocName FROM MDList "
varCommand = varCommand + "WHERE (((MDList.DocName) Like '"
varCommand = varCommand + tbLookup.Value + "*'));"
Selection.TypeText Text:=varCommand
cmd1.CommandText = varCommand
cmd1.ActiveConnection = con1
Set varRecordSet1 = cmd1.Execute
If varRecordSet1.BOF And varRecordSet1.EOF Then
MsgBox ("Error in accessing MDList table!")
Exit Sub
Else
lboResults.Clear
varRecordSet1.MoveFirst
Do While varRecordSet1.EOF = False
lboResults.AddItem varRecordSet1.Fields("DocName").Value
Loop
End If

....But running this always results in the MsgBox coming up. Note that I
print the contents of varCommand for debugging the SQL string; when I copy &
paste this into a query in Access, it runs fine. So as far as I can tell the
database is opening correctly, the SQL string is correct, the command
executes properly, but the recordset is still empty.

What do I kick?

TIA

 

[Peter Jamieson]

Try changing the "*" to a "%" - you are probably running into the problem
where Access understands "Jet" wildcards but OLE DB only understands SQL-92
wildcards (or whatever the standard is called)

Peter Jamieson

 

[PacMedTran]

Well, that, uh, certainly produced results. If you look at the code, you may
notice that there's no .MoveNext statement in the Do While loop.

I didn't notice.

After a long wait I got an "unspecified error" at the AddItem statement.
Trying to figure out what was going on, I added a line to print out the
DocName value. Kids, don't try this at home!

Long story short, the network is back up and running now. I'm off to
explain to my boss why I should be allowed to continue to work here.

Thanks for your help! }

Try changing the "*" to a "%" - you are probably running into the problem
where Access understands "Jet" wildcards but OLE DB only understands SQL-92
wildcards (or whatever the standard is called)

Peter Jamieson
news:[email protected]...

Well, that, uh, definitely produced results.