Using MS Access to create tables using SQL statements?

Question

I go to Create tab and hit Query Design and then SQL view and enter the following. But it doesn't run. I've tried googling and I follow those rules and I still get errors. I've change the statement every way I know and it still doesn't run. What am I doing wrong? Note: Last Name has to be VARCHAR and Commission and Rate both have to be NUMBER.

I redid it with some of the suggests and changed it to this.

CREATE TABLE SALES_REP
(REP_NUM CHAR(2) PRIMARY KEY,
LAST_NAME VARCHAR(15),
FIRST_NAME CHAR(15),
STREET CHAR(15),
CITY CHAR(15),
STATE CHAR(2),
POSTAL_CODE CHAR(5),
COMMISSION NUMBER(7,2),
RATE NUMBER(3,2) );

Syntax error in CREATE TABLE STATEMENT

Which errors are you getting exactly? Edit your question with that please. — Gustavo Straube
– Gustavo Straube, Commented Sep 3, 2015 at 21:43
Surround your table name in brackets: [SALES REP]. Table names are allowed to have spaces, but only if encased in brackets. — Lynn Crumbling
– Lynn Crumbling, Commented Sep 3, 2015 at 21:44
Which RDBMS are you targetting? An Access back-end, MySql, or MSSQL? You can't possibly be working in all 3. — Lynn Crumbling
– Lynn Crumbling, Commented Sep 3, 2015 at 21:46

HansUp · Accepted Answer · 2015-09-03 22:35:38Z

In MS Access, if you want DDL to specify Scale and Precision for a numeric field, use DECIMAL as the field type.

CREATE TABLE SALES_REP
(REP_NUM CHAR(2) PRIMARY KEY,
LAST_NAME VARCHAR(15),
FIRST_NAME CHAR(15),
STREET CHAR(15),
CITY CHAR(15),
STATE CHAR(2),
POSTAL_CODE CHAR(5),
COMMISSION DECIMAL(7,2),
[RATE] DECIMAL(3,2));

Notice I bracketed the field name [RATE] so the db engine won't confuse it with the VBA Rate() function.

Beware in order for DECIMAL to be supported you must execute the statement from ADO/OleDb. I executed it withCurrentProject.Connection.Execute, which is an ADO method, like this ...

Dim strDdl As String
strDdl = "CREATE TABLE SALES_REP" & vbCrLf & _
    "(REP_NUM CHAR(2) PRIMARY KEY," & vbCrLf & _
    "LAST_NAME VARCHAR(15)," & vbCrLf & _
    "FIRST_NAME CHAR(15)," & vbCrLf & _
    "STREET CHAR(15)," & vbCrLf & _
    "CITY CHAR(15)," & vbCrLf & _
    "STATE CHAR(2)," & vbCrLf & _
    "POSTAL_CODE CHAR(5)," & vbCrLf & _
    "COMMISSION DECIMAL(7,2)," & vbCrLf & _
    "[RATE] DECIMAL(3,2));"
Debug.Print strDdl
CurrentProject.Connection.Execute strDdl

Executing the same statement from DAO (CurrentDb.Execute strDdl or from the query designer) will trigger a syntax error.

Laura Morris · Accepted Answer · 2015-09-03 21:49:41Z

0

Use double instead of number:

CREATE TABLE SALES_REP
(REP_NUM CHAR(2) PRIMARY KEY,
LAST_NAME VARCHAR(15),
FIRST_NAME CHAR(15),
STREET CHAR(15),
CITY CHAR(15),
STATE CHAR(2),
POSTAL_CODE VARCHAR(5),
COMMISSION DOUBLE,
RATE DOUBLE );

answered Sep 3, 2015 at 21:49

Laura Morris

2221 silver badge10 bronze badges

2 Comments

user5274869 Over a year ago

Why will Number not work? It's working due to the change to DOUBLE, but the book wants it listed as NUMBER?

Laura Morris Over a year ago

It isn't a valid Ms access datatype

Collectives™ on Stack Overflow

Using MS Access to create tables using SQL statements?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related