SQL Code syntax
-
Hi
I am getting an error message when I run a report - "Incorrect Syntax near '12345' (the Person_id number). The code is as follows, and I can't spot the error. I'd be grateful for a fresh pair of eyes to look at it.
Dim Payme As ADODB.Recordset
Set Payme = New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tblUsers where team ='" & Forms!frmstudents!rTeam & "'" & _
"and Person_id " & Person_ID
Payme.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
Many thanks
Paul
-
Is there a space here before the and??
"and Person"
Also if it's really an id as a number of some sort, then you really don't need to add the quotation marks.
-
Excellent. All now working.
I just needed someone to have a fresh look...
Many thanks
Paul
-
HTH.
-
shouldn't it be:
" and Person_ID =" & Person_ID
I think the error message came from not having an equal sign (=) AND not having the space.
-
Yes, that's exactly what it was.
The great thing about this site is that any kind of helpful prompt puts your brain back into gear after a hard day's slog. I knew the answer, but my eyes couldn't see it.
Many thanks
Paul
-
Adding my 2 bits worth
You might try putting troublesome SQL like this in a messagebox to show you strSQL, or print it to the debug console, then you could put it in query analyser and perhaps get a better error message.
"Don`t try to engage my enthusiasm, I don`t have one."(Marvin)
-
Question, is this a .adp? If so, one very easy shortcut (works in .adp or .mdb) is to follow this syntax:
dim rs as adodb.recordset
dim strsql as string
strsql = "Select * "
strsql = strsql & "from tblMyEmployees E "
strsql = strsql & "inner join tblMyCompany c "
strsql = strsql & "on c.co_id = e.co_ID "
strsql = strsql & "where co_id = " & me.txtCo_ID & " "
set rs = currentproject.connection.execute (strsql)
debug.print (strsql)
3 things:
1 - the strsql command allows you to break the syntax of the statement into logical sections:
a - Select statement first
b - From Statement second
c - Join statements next (followed by join fields on next line)
Make sure that there is always a space at the end of the line between the last character and the close double-quote "
2 - The "currentproject.connection.execute(strsql) " is a much faster way to execute / open a recordset. As long as you have a valid connection in the .adp then you only have to use the syntax above.
3 - The debug statement prints the strsql to the immediate window where you can copy and paste it into either query analyzer or an Access query sql statement and test.
Just a thought.
Mike
-
Thanks Mike
That's teriffic. I shall certainly be using that.
Paul
P.S I have just added another thread 'Lock adp'. You obviously have an excellent knowledge of adps, and I wondered if you could spot the flaw....
Thanks again
Paul
-
Clarification, Mike:
You’re saying that
Set rs=CurrentProject.Connection.Execute(strSQL)
Is faster than
Set rs=New ADODB.Recordset
rs.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
What if you want a different connection other than CurrentProject? Most of my connections are OLE DB and do not reference the project connection. I presume you’re stuck with the first methodology.
Thanks,
Sam
-
Sam,
Excellent Point. I should have thought to include that. In a lot of the projects that we do, we have to make cross-server / cross-database connections. To do this, we build clsObjects that set the connection to the project that we want to work on.
For Example:
The primary db that I am connecting to is automatically connected at startup to Server 1/db Test1
I know that I will need to access 3-5 other servers / (could be an Oracle connected server) throughout the course of my reporting / data gathering. For each source, I build the connection string into the clsConnect object and call it just before I execute the strsql and "currentproject.connect.execute" statement.
eg: call csServer2
strsql = "select * "
strsql = strsql & " from Server2.test2.dbo.tblemployees e "
strsql = strsql & "where employeeid = " & me.txtEmpID & " "
then I execute the currentproject statement. You really only need to do the set different connections if you are going to access them under a different login / authentication. Otherwise, just call explicitly declare the database object (as in Server2 above) with the full syntax.
There are two limitations to this. One, you cannot create a table in a cross-server object if you are not in that object. Two, if the time to return the data is > 30 seconds, you will time out. That brings up a whole nother discussion about how to make sure that your queries are not only right but also fast. We have to access millions of records from 300 nodes on the systems every second. We can return 1 record from 14 million records with 800 lines of validation code in under 1 second. It is all about building the statement right. (One hint: print out the code and look at it upside down. Read it in reverse)
To prove what I say, set up a scenario and create a temporary table to store the begin and end times for each call. See which one is faster, yours or mine.
Have a great night.
Mike
-
Paul,
Do you have a link to the post that you are referencing? I couldn't find it. Would like to see it.
Thanks,
Mike
-
Mike: Thanks so much. This is really helpful.
Sam
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply