access和mysql内容,Access和MySQL之间SQL语法的区别（来自使用ADO的VB6项目）

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