Full Version: ADO syntax problem, doesn't make sense to me
UtterAccess Forums > Microsoft® Access > Access Forms
valkster
Hello, everyone. I have a problem that has beggin me on the syntax of an ADO lookup. I am still learning, and I fnally got it to work but here is what I had to do. Originally, I had this code when a form opened. This is just the guts of the original code block I wrote:
s.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenDynamic
rs.Open "SELECT * from tblUsers"
SQLStmt = "[UserLogin1] = " & GBL_UserName
rs.Find SQLStmt

In the debug it failed when it hit the FIND segment...stating that "arguments are of the wrong type, are out of acceptable range, or are in conflict".
Ogot it to work by doing this below (adding the extra single/double quotes around the GBL_UserName field).
rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenDynamic
rs.Open "SELECT * from tblUsers"
SQLStmt = "[UserLogin1] = " & "'" & GBL_Username & "'"
rs.Find SQLStmt

Can someone educate me why this is? I am learning ADO from a book, and the first lines of code I posted are from examples in the text. Any help would be appreciated.
Sincerely,
Mark
vtd
Your [UserLogin1] is a Text value so the explicit Text value you use to compare must be enclosed in the Text delimiter (single quote). If you check the result of the concatenation for SQLStmnt, say with GBL_UserName = "Mark", the first one gives:
UserLogin1]=Mark
which won't be accepted by ADO. The seocnd one gives the required criterion correctly as:
[UserLogin1]='Mark'
datAdrenaline
Because you are creating a literal string for the Find, so you need the single hash marks surrounding your literal text ...

Assume the column [UserLogin1] has a value of datAdrenline ...
With the method that errored out, Access would have resolved your code and Find like this:

CODE
SQLStmt =  "[UserLogin1] = " & GBL_UserName
QLStmt = "[UserLogin1] = datAdrenline"
rs.Find "[UserLogin1] = datAdrenline"


Well the JET/ACE database engine has NO idea what datAdrenaline is ... is it a parameter? a string? a column ?

So ... you HAVE to build a true literal string ...
CODE
SQLStmt =  "[UserLogin1] = '" & GBL_UserName & "'"
SQLStmt = "[UserLogin1] = 'datAdrenline'"
rs.Find "[UserLogin1] = 'datAdrenline'"


Now JET/ACE knows exactly what you want ... the field [UserLogin1] to equal the string value 'datAdrenaline'. Just so you know, you have to do the same type of thing when you use DAO .... actually you use this same philosophy ANYTIME you are building a STRING that is used for criteria or a SQL statement ... like in a DLookup() or other methods that use the equivalent of an SQL Where clause.

Now, with all that said, when build SQL statments, you can get away without adding the literal designators if you use a parameter ... but maybe that should be saved for a different thread. I personally prefer to create the literal string with the values I have, and thus not use parameters.

EDITS ADDED >>>
Sorry for the dup info ... Van posted while I was composing! ...
Edited by: datAdrenaline on Mon Feb 18 22:54:20 EST 2008.
valkster
Thanks so much for your insight, I understand what both of you have explained but am having trouble "seeing" when your explaination would NOT apply. Could you give me an example of the type of field that would work with the first code I posted? What I mean is, when would I not need the added quotes? I know that I have overlooked something in the text book I am reading (field type or how it was handed to the ADO string) and I would like to get a better understanding of it.
incerely,
Mark
vtd
Numeric values do not have delimiter so it is valid to use code like:

SQLStmt = "[NumericFieldName] = " & {Explicit numeric value or a numeric variable}
datAdrenaline
>> how it was handed to the ADO string <<
Just a quick note ... this may have got lost in my previuos post ... but ... the concept of building an SQL type string is the same from object library to object library (ie: DAO, ADO, or ??) ... when you are creating a literal string, then you need to designate literal values with delimiters.
valkster
Thanks everyone. That helped...I had run accross this problem in past databases and got them to work (with help) but I never really understood why. I was using DAO on past databases and this was my first stab with using ADO.
incerely,
Mark
vtd
You're welcome ... Glad we could help ...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.