SQL statement needs to end with semi-colon?

[Steven Sutton]

I have been dabbling in writing VB for Access 2003 for a while now and a
question that has been bugging me is whether or not a SQL statement has to
end with a semi-colon. Any SQL I have looked at that Access generated (ie -
from a query) ends with a semi-colon. I have written a number of SQL
statements within my code, none of which ends with a semi-colon, and yet they
all seem to work just fine. Could someone please explain why or why not a SQL
statement needs the semi-colon? I wouldn't want all my work to just quit
working someday because I forgot a semi-colon or two! ;-)

 

[Rick Brandt]

I have been dabbling in writing VB for Access 2003 for a while now
and a question that has been bugging me is whether or not a SQL
statement has to end with a semi-colon. Any SQL I have looked at that
Access generated (ie - from a query) ends with a semi-colon. I have
written a number of SQL statements within my code, none of which ends
with a semi-colon, and yet they all seem to work just fine. Could
someone please explain why or why not a SQL statement needs the
semi-colon? I wouldn't want all my work to just quit working someday
because I forgot a semi-colon or two! ;-)

For SQL in code I almost never include the semi-colon and have never had any
problems. The few places that I have one is where I copied and pasted from
a query's SQL view.

 

['69 Camaro]

Hi, Steven.

Could someone please explain why or why not a SQL
statement needs the semi-colon?

The semicolon is required by Jet to indicate the end of the SQL statement.
Fortunately, if one forgets to type the semicolon at the end of the
statement, the Jet Expression Service inserts the semicolon in the proper
place before passing the string to Jet. So don't worry if you've forgotten
it in the past, but type it in for future queries, because now you know
better.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

The semi-colon is used in SQL to separate SQL statements:

INSERT INTO fred ( idx, n )
SELECT 1 AS Expr1, "a" AS Expr2;
INSERT INTO fred ( idx, n )
SELECT 1 AS Expr1, "b" AS Expr2;
etc.

Access/JET SQL does not allow you to have multiple
SQL Statements (or even comments) in the same query,
so the semi-colon is purely optional.

The rules are different for ADO/ODBC/Passthrough/Other SQL

(david)

 

[David W. Fenton]

Could someone please explain why or why not a SQL
statement needs the semi-colon?

In official SQL, you use it to declare "this is the end of this SQL
statement." In Access, since you can only have one SQL statement
executed at a time, this is really no big deal, and is there likely
for compatibility.

But many server databases can be sent a series of SQL statements in
one operation and execute them one after the other. In that case,
the semicolon is essential to indicate where one SQL statement ends
and the next begins.