ADO Error - ODBC Microsft Access Driver Toofew parameters. Expecte

[bobm]

Hello,

I get the subject error message when I run an ADO query from MS Access.

The query works ok if I don't include the WHERE statement. If I run the
query from Access query editor, it works ok.

SELECT * FROM tblNames WHERE tblNames.Name"Brown, John";

this is my code... appreciate any help given.

regards,

bobm

Sub test()

strName = "Brown, John"

strPath = "C:\mydatabase.mdb"
Set cn1 = New ADODB.Connection
Set rs1 = New ADODB.Recordset

'strSQL = "SELECT * FROM tblNames;"

strSQL = "SELECT * " & _
"FROM tblNames " & _
"WHERE tblNames.Name" & """" & strName & """" & ";"

Debug.Print strSQL

cn1.Open "Driver={Microsoft Access Driver (*.mdb)}; Dbq=" & strPath &
";Pwd=leave;"

rs1.Open strSQL, cn1, adOpenKeyset, adLockPessimistic, adCmdText

Do Until rs1.EOF
Debug.Print rs1.Fields("Name")
rs1.MoveNext
Loop

rs1.Close
cn1.Close
Set cn1 = Nothing
Set rrs1 = Nothing

End Sub

 

[RoyVidar]

Hello,

I get the subject error message when I run an ADO query from MS
Access.

The query works ok if I don't include the WHERE statement. If I run
the query from Access query editor, it works ok.

SELECT * FROM tblNames WHERE tblNames.Name"Brown, John";

this is my code... appreciate any help given.

regards,

bobm

Sub test()

strName = "Brown, John"

strPath = "C:\mydatabase.mdb"
Set cn1 = New ADODB.Connection
Set rs1 = New ADODB.Recordset

'strSQL = "SELECT * FROM tblNames;"

strSQL = "SELECT * " & _
"FROM tblNames " & _
"WHERE tblNames.Name" & """" & strName & """" & ";"

Debug.Print strSQL

cn1.Open "Driver={Microsoft Access Driver (*.mdb)}; Dbq=" &
strPath & ";Pwd=leave;"

rs1.Open strSQL, cn1, adOpenKeyset, adLockPessimistic, adCmdText

Do Until rs1.EOF
Debug.Print rs1.Fields("Name")
rs1.MoveNext
Loop

rs1.Close
cn1.Close
Set cn1 = Nothing
Set rrs1 = Nothing

End Sub

I think the concept of "doublequoting" is more a DAO phenomena than
ADO.

Try

strSQL = "SELECT * " & _
"FROM tblNames " & _
"WHERE tblNames.Name = '" & Replace(strName, "'", "''") & "'"

 

[John Spencer]

Where is the equal sign?

I would try
strSQL = "SELECT * " & _
"FROM tblNames " & _
"WHERE tblNames.Name =""" & strName & """;"

This should work except for names like "O'Casey, Bob" which may error
because of the embedded apostrophe
strSQL = "SELECT * " & _
"FROM tblNames " & _
"WHERE tblNames.Name ='" & strName & "';"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[bobm]

Where is the equal sign?

I would try
strSQL = "SELECT * " & _
"FROM tblNames " & _
"WHERE tblNames.Name =""" & strName & """;"

Thanks for the replies but I could not get both suggestions to work.
More information, I am running this query from Excel connection to Access on
the local file server. Sorry I missed the equal sign from the example code in
my original post. The actual code as follows:

strSQL = "SELECT tblLeaveRequest.Staff " & _
"FROM tblLeaveRequest " & _
"WHERE tblLeaveRequest.Staff=""" & strName & """;"

cn1.Open "Driver={Microsoft Access Driver (*.mdb)}; Dbq=" & strPath &
";Pwd=leave;"
rs1.Open strSQL, cn1, adOpenKeyset, adLockPessimistic, adCmdText

As stated if i run the query without the WHERE is runs as it should.
Appreicate any further help. Is the last line correct. Can you include a
Where clause with this type of query.

Regards,

Bobm

 

[bobm]

hello,

i managed to get this to work using OLE DB connection string instead of
ODBC. does ODBC have a limititation in using the where clause.

for your information this is my new code...

strName = "Brown, John"

Set cn1 = CreateObject("ADODB.Connection")
cn1.Provider = "Microsoft.Jet.OLEDB.4.0"

cn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\mydatabase.mdb;Jet OLEDBatabase Password=leave;"
Set rs1 = CreateObject("ADODB.recordset")

Sql = "SELECT tblLeaveRequest.Staff FROM tblLeaveRequest WHERE
tblLeaveRequest.Staff=""" & strName & """;"

rs1.Open Sql, cn1

Do Until rs1.EOF
Debug.Print rs1.Fields("Staff")
ctr = ctr + 1
rs1.MoveNext
Loop

rs1.Close
cn1.Close
Set cn1 = Nothing
Set rrs1 = Nothing

 

[RoyVidar]

.CommandText = _
"INSERT INTO Test (col1) VALUES (?);"

.Parameters _
.Append .CreateParameter(, 200, 1, 30, value)

Dim rowsAffected As Long
.Execute rowsAffected

Except that I use early binding, this is my preference too, when
working with dynamic SQl and ADO.

I'm more concerned that using doublequoting in dynamic SQL, I've found
there to be a difference of behaviour between ADO and DAO. But perhaps
that's only a problem on the setups I work?

On my setup, the doublequoting in the initial post will give the
parameter error when executed on an ODBC connection, and when executed
through a OLE DB provider, it will retrieve no records if the criterion
contains single quote, even if such exists in the table.

Doubling up the "offending character" through the replace function,
works on both (ODBC and OLE DB), and as such, is perhaps one step
further than the original code?

I don't know how many people have double quotes or other 'illegal'
characters in their name, but utilizing the parameters collection of
an ADO command object, would address that.