Hello
Apologies for posting to so many groups but this one is difficult to
catagorize precisely
The following code worked fine with a connection to an Access database.
However, I have changed over to MySQL and am getting syntax error messages
with my code.
adoSetRS.Open "select [SetIndex], [Set] from tSet WHERE StatusID = 1 Order
by Set", db, adOpenStatic, adLockOptimistic
I cannot see why there would be any differences in the syntax to query an
Access as opposed to a MySQL database. After all its all SQL.
Or does anyone know different
or have experienced similar
PS The errors are not occuring on any particular part of the statment it
seems quite random
Help greatly appreciated
Ian
解决方案I bet the square brackets are a problem.
Why don''t you try putting the sql directly in the MySql?
Apologies for posting to so many groups but this one is difficult to
catagorize precisely
Ah, gee, you would think you try a MySql newsgroup first with the raw
sql...would you not?
(so, yea..apology accepted..but you got make a bit better efforts on your
part...)..
Did you try the actual sql as a query in MySql?
Did you ask any of the MySql people first?
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
Hi
The square brackets werent in initially. I put them in thinking it was the
lack of them causing the errors. Alas NOT.
I will try some of your other suggestions
Ian
"Albert D.Kallal" wrote in message
news:eG**************@TK2MSFTNGP09.phx.gbl...I bet the square brackets are a problem.
Why don''t you try putting the sql directly in the MySql?
Apologies for posting to so many groups but this one is difficult to
catagorize precisely
Ah, gee, you would think you try a MySql newsgroup first with the raw
sql...would you not?
(so, yea..apology accepted..but you got make a bit better efforts on your
part...)..
Did you try the actual sql as a query in MySql?
Did you ask any of the MySql people first?
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
Ha
Seems that in my first example ''set'' is a reserved word but cannot be
protected by enclosing in []. Does anyone know how I can include a field
called ''set'' in my statment without the error?
That one solved I now got this one giving the same problem
************************************************** ****************
strPupilSQL = "SELECT tPupilsSubject.PupSubIndex, tPupilsSubject.SubjectNo,
tPupilsSubject.TeacherID, tPupilsSubject.YrIndex, tPupilsSubject.BandIndex,
tPupilsSubject.SetIndex, [Surname] & '' '' & [Firstname] AS PupilName"
strPupilSQL = strPupilSQL & " FROM tPupils INNER JOIN tPupilsSubject ON
tPupils.PupilNo = tPupilsSubject.PupilNo"
strPupilSQL = strPupilSQL & " Where (((tPupilsSubject.SubjectNo) " & ParSub
& ") And ((tPupilsSubject.TeacherID) " & ParTeacher & ") And
((tPupilsSubject.YrIndex) " & ParYr & ") And ((tPupilsSubject.BandIndex) " &
ParBand & ") And ((tPupilsSubject.SetIndex) " & ParSet & ") And
((tPupilsSubject.statusID) = 1) And ((tPupils.statusID) = 1))"
strPupilSQL = strPupilSQL & " ORDER BY tPupils.Surname"
************************************************** *********
The error is on the first line. I cant see it
The ParSub, ParYr etc comes from
******************************
If cbdSubject.BoundText = "" Then
ParSub = " Like ''%''"
Else
ParSub = "=" & cbdSubject.BoundText
End If
*******************************
"Ian Davies" wrote in message
news:oa***********@newsfe7-gui.ntli.net...Hi
The square brackets werent in initially. I put them in thinking it was the
lack of them causing the errors. Alas NOT.
I will try some of your other suggestions
Ian
"Albert D.Kallal" wrote in message
news:eG**************@TK2MSFTNGP09.phx.gbl...I bet the square brackets are a problem.
Why don''t you try putting the sql directly in the MySql?
Apologies for posting to so many groups but this one is difficult to
catagorize precisely
Ah, gee, you would think you try a MySql newsgroup first with the raw
sql...would you not?
(so, yea..apology accepted..but you got make a bit better efforts on
your part...)..
Did you try the actual sql as a query in MySql?
Did you ask any of the MySql people first?
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal