Syntax error in CREATE TABLE statement using VBA/SQL

[tbiceps]

The following line of code was entered in the Immediate Window of Access. I'm following an online tutorial on SQL and VBA coding. When I press enter I get the following error:

Run-time error '3290'
Syntax error in CREATE TABLE statement.

Any clues?

DoCmd.RunSQL "CREATE TABLE tblTest ([StaffID] COUNTER CONSTRAINT ndxStaffID PRIMARY KEY, [FirstName] TEXT(25), [LastName] TEXT(30), [BirthDate] DATETIME);”

 

[Remou]

I do not think it likes the semicolon:
[tt]DoCmd.RunSQL "CREATE TABLE tblTest ([StaffID] COUNTER CONSTRAINT ndxStaffID PRIMARY KEY, [FirstName] TEXT(25), [LastName] TEXT(30), [BirthDate] DATETIME)"[/tt]

 

[PHV]

Which version of MS-Access ?
works for me (ac2003) with a proper ending double-quote.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tbiceps]

Excellent! It did not like the semi-colon. I can continue with the tutorial.

 

[tbiceps]

I get the following errors when I follow an online tutorial teaching SQL. Each piece of code was typed continuously in the Immediate window of Access 2003. Any Ideas?

I get a Run-time error '3075':
Syntax error (missing operator) in query expression
"Martin', 'Green'. For the code listed below.

DoCmd.RunSQL "INSERT INTO tblTest ([FirstName], [LastName], [BirthDate]) VALUES ('Martin’, 'Green’, #09/27/1950#)”

I get a Run-time error '3292':
Syntax error in field definition:
DoCmd.RunSQL "ALTER TABLE tblTest ADD COLUMN [Age] BYTE”

 

[Remou]

It is always worth listening to PHV. It is the closing ” quote that is causing your problems, it seems. I ran the first bit again with a semi-colon, and it worked fine.

 

[tbiceps]

How should I adjust? When I add the semi-colon, I still get an error. How does the closing quote cause problems.

 

[tbiceps]

When I eliminate the first two parameters ('Martin’, 'Green’) it works. I don't quite understand. Sometimes I need a semicolon, but at other times I don't.

Remou (TechnicalUser) 7 Mar 06 10:13
I do not think it likes the semicolon:

DoCmd.RunSQL "CREATE TABLE tblTest ([StaffID] COUNTER CONSTRAINT ndxStaffID PRIMARY KEY, [FirstName] TEXT(25), [LastName] TEXT(30), [BirthDate] DATETIME)"

In this example the semicolon isn't needed. The syntax doesn't seem consistent, but I'm sure that I'm missing some important concept.

 

[Remou]

Because it is not a regular quote:
asc("”") = 148
chr(34) = "

 

[tbiceps]

Okay Remou! For my first piece of code, my problem was with the single quotes around the names 'Martin’, 'Green’. It should be 'Martin', 'Green'. I copied code directly from online tutorial.

For the second piece of code, the problem was with the quotation mark at the end of the sentence.
DoCmd.RunSQL "ALTER TABLE tblTest ADD COLUMN [Age] BYTE;”

It should be: Notice the slight difference.
DoCmd.RunSQL "ALTER TABLE tblTest ADD COLUMN [Age] BYTE;"

By the way, I listen to both you and PHV very intensely. Your advice always lead me to the right solution. However, why would I use a semicolon in the previous two pieces of code, but not in the following piece of code. Is this some type of SQL rule?

DoCmd.RunSQL "CREATE TABLE tblTest ([StaffID] COUNTER CONSTRAINT ndxStaffID PRIMARY KEY, [FirstName] TEXT(25), [LastName] TEXT(30), [BirthDate] DATETIME)"

 

[PHV]

I personally NEVER use a trailing semicolon in access.
The semicolon is an instructions delimiter and access admits only single instruction ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tbiceps]

Understood, Tested, and Verified! Thanks for your patience, as I learn SQL and VBA. I hope to help you two help other users in the future, as I get up to speed.