SQL syntax error in create table statement

Question

I'm just starting out experimenting with SQL on MS Access. I'm trying to insert data into a table, and I can't figure out what I'm doing wrong here. I know it's probably something simple so I apologize in advance. Thanks for any help you can give me!

Here's what I've got:

CREATE TABLE DeathDay
  (
     ID        INT PRIMARY KEY NOT NULL,
     LastName  CHAR(25),
     FirstName CHAR(10),
     DoB       DATE,
     DoD       DATE
  );

INSERT INTO DeathDay
VALUES      (1,
             'Breitenbach',
             'Max',
             '1991-05-17',
             '2022-12-31');

I can create the table fine, but it gives me the following error message when I try to insert the data.

"Syntax error in CREATE TABLE statement"

Are you guessing when someone will die for `DoD`? Perhaps that's why you're receiving an error. — Kermit, Jan 28 '13 at 21:23
The delimiter for literal values inserted into date fields is #. Try using #1991-05-17# and #2002-12-31# instead. — Tim Lentine, Jan 28 '13 at 21:24
I just tested both in MS Access 2010 and received no error. What is the error? — Taryn, Jan 28 '13 at 21:25
The error is "Syntax error in CREATE TABLE statement" when I highlight and run the insert into command. Maybe I'm not running it correctly? — user2019632, Jan 28 '13 at 21:25
I don't think you can highlight and run a command in the Access query editor. Are you sure it's not trying to re-create the table? — maelstrom, Jan 28 '13 at 21:26
Might be a dupe of [this question](http://stackoverflow.com/questions/771861/insert-sql-command-with-datetime-in-ms-access) — Mike Christensen, Jan 28 '13 at 21:27
Access 2010. Am I supposed to open a new query window for each command? I used the same query window for both here. — user2019632, Jan 28 '13 at 21:27

score 3 · Accepted Answer · answered Jan 28 '13 at 21:28

3

I just ran your scripts in separate query windows in MS Access 2010 and there was no error. However, if you try running them at the same time, you get an error:

Syntax error in CREATE TABLE statement

Based on my experience you cannot run multiple queries in the same query window.

If you want to run multiple queries then you want to use a script to process it.

answered Jan 28 '13 at 21:28

Taryn

242,637
56
362
405

That was it, thanks! Thought it might be something obvious like that. Just opened a new query window and it worked. Can I delete old query windows after I've run the commands? It seems like it would get rather cluttered if I keep all the old queries. – user2019632 Jan 28 '13 at 21:33
Yes, you don't need to keep it unless you have reason to. – Taryn Jan 28 '13 at 21:34
In my experience you can't even add multiple records at the same time, let alone queries. ...anything to keep the workday at 40hrs I guess. – Ben Alan Mar 09 '21 at 15:33

score 0 · Answer 2 · answered Oct 17 '13 at 08:49

In case anyone else comes to this question with the same problem as me, I found the issue to be keywords in my SQL statement. In my case "Currency". A list of reserved keywords is available here: http://allenbrowne.com/AppIssueBadWord.html the ones that are relevant to SQL are the Jet keywords.

SQL syntax error in create table statement

2 Answers2