ADO Error - ODBC Microsft Access Driver Toofew parameters. Expecte

[Guest]

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
..

 

[Guest]

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

 

[Guest]

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

 

[onedaywhen]

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

Try

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

That handles single quotes but what about escaping double quotes plus
any other 'illegal' characters? Is it practical/safe to try to escape
all these characters yourself?

I think the better approach is to let the system (engine, parser,
provider, driver, whatever) do the work and the best way I know of
achieving this is to use Parameter objects; ADO is my preference but
perhaps DAO can do the same. This approach has the added advantage of
making one think about converting dynamic/ad hoc SQL into engine-level
PROCEDUREs ;-)

Quick example:

Sub paramtest()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")

Dim value As String
value = "Double "" quote Single ' quote"

With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Test (" & _
" col1 VARCHAR(30));"

' Try the 'manual escape' approach
On Error Resume Next
.Execute _
"INSERT INTO Test (col1) VALUES (" & _
Replace(value, "'", "''") & ");"
If Err.Number <> 0 Then
MsgBox _
Err.Description, , _
"Manual escape approach"
End If
On Error GoTo 0

End With

'Try the 'parameters' approach
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = .ActiveConnection
With cmd
.CommandText = _
"INSERT INTO Test (col1) VALUES (?);"

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

Dim rowsAffected As Long
.Execute rowsAffected

MsgBox _
"Rows affected: " & CStr(rowsAffected), _
, "Parameters approach"
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[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.

 

[onedaywhen]

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

I generally use late binding when posting, to aid 'copy and paste'
i.e. no need for instructions about setting references etc.

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?

Thanks, good info. I've no experience to add, though (I *think* I

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.

Eddie "The Eagle" Edwards springs to mind <g>. From a usability
perspective, you don't want bad data (e.g. someone's typo of a double
quote character) to result in a non-specific failure message, of
course.

Jamie.

--