Problem with ADO (Lack of information)

  • Thread starter Thread starter Joe Harman
  • Start date Start date
J

Joe Harman

I am attempting to use ADO in a form module to run an SQL
query that I build on the fly. The SQL query should
build a new table (tblSrch0) with all the records from
the sr13FOOD_DES table containing the characters in the
textbox named txtInput. Here is a code snippet:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "SELECT sr13FOOD_DES.NDB_NO, " & _
"sr13FOOD_DES.Description " & _
"INTO [tblSrch0] FROM sr13FOOD_DES " & _
"WHERE (((sr13FOOD_DES.Description) " & _
"LIKE '*" & _
Me!txtInput & "*')) AND " & _
"(((sr13FOOD_DES.NDB_NO) " & _
"LIKE 'X*')) " & _
"ORDER BY sr13FOOD_DES.Description;"
cmd.CommandType = adCmdText
cmd.Execute

I have stepped thru the code with the debugger and the
text appears to be ok. It fails on cmd.Execute with the
following error message:

-2147217904 No value given for one or more required
parameters.

In looking at the cmd.Execute info when I type it in, I
have options of "cmd.Execute ([Records Affected],
[parameters], [Options as Long = -1]) as recordset".
Examples I've seen do not use anything but cmd.Execute
for this type of thing. I cannot find a good source for
the information that I am expected to include. Most of
what I know about ADO (which is very little but I'm
trying to learn) is from the examples in the Access 2000
Developer's Handbook, Volume 1.
 
I haven't found anything that says wildcard is the
percent sign in my books, but asterisk doesn't work and
percent sign does. So I'll accept it. LOL.

In addition, I had to put in two dim statements:

Dim prm as ADODB.Parameter
DIM intPrmCount as Integer

Then between the line that starts "cmd.CommandText" and
the line that starts "cmd.Execute", I had to put in:

intPrmCount = cmd.Parameters.Count
If intPrmCount > 0 Then
For Each prm In cmd.Parameters
prm.Value = Eval(prm.Name)
Next prm
End If

Then it worked as expected.
 
Back
Top