CODEWITHSUNDEEP
sqlms-accessms-access-2010ddl
Harry
Harry

Reputation: 35

Syntax error in create table statement - Access 2010

Create table Customers 
(
customerid              number(10) PRIMARY KEY; 
First_name              varchar2(100); 
last_name               varchar2(100); 
house_number            number(10); 
street                  number(10);  
postcode                number(5); 
home_phone_number       number(10);  
mobile_phone_number     number(10);  
email_address           varchar2(100); 
DOB                     date
);

Upvotes: 0

Views: 1817

Answers (2)

HansUp
HansUp

Reputation: 97131

Your DDL statement, with the field type substitutions @Mureinik suggested, works in Access 2010, but only if you execute it from ADO:

Create table Customers (
    customerid              numeric(10) PRIMARY KEY,
    First_name              varchar(100),
    last_name               varchar(100), 
    house_number            numeric(10),
    street                  numeric(10),  
    postcode                numeric(5),
    home_phone_number       numeric(10),
    mobile_phone_number     numeric(10),  
    email_address           varchar(100),
    DOB                     datetime
    );

I loaded the statement into a string variable, strCreate, and executed it like this ...

CurrentProject.Connection.Execute strCreate

CurrentProject.Connection is an ADO object.

That statement will throw a syntax error when executed via DAO ...

CurrentDb.Execute strCreate  ' <-- "Syntax error"

If you're executing the statement from the Access query designer, that also uses DAO and the statement will fail.

Although the statement executed successfully from ADO, I'm unsure the resulting data types for those Numeric() fields are what you actually want. I'm not accustomed to seeing Decimal field type used for a primary key. More often, people choose AutoNumber, which is based on Long Integer. (But I'm not saying Decimal is a bad choice if that's what you really want.)

Customers table in Table Design view

Upvotes: 2

Mureinik
Mureinik

Reputation: 312279

After each column definition you should have a comma (,), not a semicolon (;). Additionally, this script looks as though it was borrowed from Oracle. In access, the corresponding types for varchar2, number and date are varchar, numeric and datetime, respectively:

Create table Customers 
(
customerid              numeric(10) PRIMARY KEY,
First_name              varchar(100),
last_name               varchar(100), 
house_number            numeric(10),
street                  numeric(10),  
postcode                numeric(5),
home_phone_number       numeric(10),
mobile_phone_number     numeric(10),  
email_address           varchar(100),
DOB                     datetime
);

Upvotes: 1

Related Questions