Access和MySQL之间SQL语法的区别（来自使用ADO的VB6项目） [英] Difference in SQL Syntax between Access and MySQL (from VB6 project using ADO)
问题描述
你好
向很多团体发帖的道歉但是这个很难
catagorize正好
下面的代码通过连接到Access数据库工作得很好。
但是，我已经转换到MySQL并且我的代码收到了语法错误消息
。
adoSetRS.Open" select [SetIndex]，[Set] from tSet WHERE StatusID = 1 Order
by Set"，db，adOpenStatic，adLockOptimistic
>
我不明白为什么查询
Access而不是MySQL数据库的语法会有任何差异。毕竟它的所有SQL。
或者有没有人知道不同的
或经历过类似的事情
PS错误没有发生在它的任何特定部分
似乎很随意
非常感谢帮助
Ian
我打赌方括号是个问题。
为什么不尝试将sql直接放在MySql中？
< blockquote class =post_quotes>向这么多团体发帖的道歉但是这个很难精确地分类
啊，哎呀，你会认为你首先用原始的
sql尝试一个MySql新闻组...你不是吗？
（所以，是的......接受了空洞学...但是你做了更好的努力你的
部分......）..
你是否在MySql中尝试了实际的sql作为查询？
你先问过任何一个MySql人吗？
-
Albert D. Kal lal（访问MVP）
加拿大艾伯塔省埃德蒙顿
pl ***************** @ msn.com
http://www.members.shaw.ca/AlbertKallal
嗨
方括号最初没有。我让他们认为这是因为缺少它们导致错误。唉不。
我会尝试你的其他一些建议
Ian
" Albert D.Kallal" ; < PL ******************* @ msn.com>在消息中写道
news：eG ************** @ TK2MSFTNGP09.phx.gbl ...我打赌方括号是一个问题。
为什么不尝试将sql直接放在MySql中？
向这么多组发帖的道歉但是这个是难以精确地分类
啊，哎呀，你会认为你先用原始的sql尝试一个MySql新闻组...你不会吗？
（所以，是的......空洞接受了..但是你在你的部分做了一些更好的努力......）..
你有没有尝试过实际的sql作为MySql中的查询？
你先问过任何一个MySql用户吗？
- 阿尔伯特·卡尔拉尔（访问MVP）
埃德蒙顿，艾伯塔省加拿大
pl ************* ****@msn.com
http：// www.members.shaw.ca/AlbertKallal
Ha
似乎在我的第一个例子中''set''是一个保留字，但不能是
受封闭在[]中的保护。有没有人知道如何在我的声明中包含一个字段
没有错误？
那个解决了我现在得到了这个给出同样的问题
********************************** **************** ****************
strPupilSQL =" SELECT tPupilsSubject.PupSubIndex，tPupilsSubject .SubjectNo，
tPupilsSubject.TeacherID，tPupilsSubject.YrIndex，tPupilsSubject.BandIndex，
tPupilsSubject.SetIndex，[Surname]& ''''& [名字] AS PupilName"
strPupilSQL = strPupilSQL& "来自tPupils INNER JOIN tPupilsSubject ON
tPupils.PupilNo = tPupilsSubject.PupilNo"
strPupilSQL = strPupilSQL& "其中（（（tPupilsSubject.SubjectNo）& ParSub
&"）和（（tPupilsSubject.TeacherID）&& ParTeacher&"）和
（（tPupilsSubject.YrIndex）"& ParYr&"）和（（tPupilsSubject.BandIndex）&
ParBand&"）和（（tPupilsSubject.SetIndex） "& ParSet&"）和
（（tPupilsSubject.statusID）= 1）和（（tPupils.statusID）= 1））"
strPupilSQL = strPupilSQL& "订购tPupils.Surname
************************************ ************** *********
错误发生在第一行。我看不到它
ParSub，ParYr等来自
******************* ***********
如果cbdSubject.BoundText =""然后
ParSub ="比如''％''"
Else
ParSub =" =" &安培; cbdSubject.BoundText
结束如果
***************************** **
" Ian Davies" < IA ******** @ virgin.net>在消息中写道
新闻：oa *********** @ newsfe7-gui.ntli.net ...嗨
方括号最初没有。我让他们认为这是他们缺乏导致错误的原因。不是。
我会尝试你的其他一些建议
Ian
Albert D.Kallal < PL ******************* @ msn.com>在消息中写道
新闻：eG ************** @ TK2MSFTNGP09.phx.gbl ...我打赌方括号是个问题。
为什么不尝试将sql直接放在MySql中？
向这么多组发帖的道歉但是这个很难
啊，哎呀，你会认为你先用原始的sql尝试一个MySql新闻组...不是吗？
（所以，是的......空洞接受了..但是你在
上做了一些更好的努力......）..
你是否尝试过将实际的sql作为查询在MySql中？
你有没有问过任何一个MySql人？
- Albert D. Kallal（访问MVP）
加拿大艾伯塔省埃德蒙顿 pl ***************** @ msn.com
http：//www.members。 shaw.ca/AlbertKallal
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" <Pl*******************@msn.com> 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" <ia********@virgin.net> 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" <Pl*******************@msn.com> 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
这篇关于Access和MySQL之间SQL语法的区别（来自使用ADO的VB6项目）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
