创建表语法错误-MS Access，SQL视图 [英] Create Table Syntax Error - MS Access, SQL view

问题描述

我不断收到CREATE TABLE语法错误，但没有看到该错误！是什么原因导致错误?

I keep getting CREATE TABLE Syntax Error, but I don't see the error! What is causing the error?

我的SQL:

CREATE TABLE my_employee
(
employee_id INTEGER PRIMARY KEY NOT NULL,
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(30) NOT NULL,
address VARCHAR(10) NOT NULL,
birthdate DATE,
salary NUMERIC(8,2) DEFAULT 15000,
marital_status CHAR(1)
);

推荐答案

由于DDL语句包含DEFAULT，因此必须使用ADO执行它.我将您的语句加载到字符串变量中，并从Access 2007中执行，如下所示:

Since your DDL statement includes DEFAULT, you must execute it with ADO. I loaded your statement into a string variable and executed it from Access 2007 like this:

CurrentProject.Connection.Execute strSql

薪金字段为十进制，精度为8，小数位数为2，默认值为15000.

The salary field is decimal with precision 8, scale 2, and default 15000.

DEFAULT是Jet 4.0附带的Access SQL功能之一.这些功能不适用于从DAO执行的语句.如果您使用Access的查询设计器来创建和执行语句，则使用DAO.如果使用CurrentDb.Execute，则相同.但是CurrentProject.Connection是ADO对象，因此它可以具有.Execute Jet 4.0功能.

DEFAULT is one of the Access SQL features added with Jet 4.0. Those features are not available for a statement executed from DAO. If you are using Access' query designer to create and execute the statement, you're using DAO. Same if you were using CurrentDb.Execute. But CurrentProject.Connection is an ADO object, so it can .Execute Jet 4.0 features.

注意NOT NULL在PRIMARY KEY之后不是必需的，因为PRIMARY KEY表示NOT NULL.但是，PRIMARY KEY NOT NULL不会触发错误.只要您从ADO执行该语句，该语句便会按照您最初编写的方式工作.

Note NOT NULL is not necessary after PRIMARY KEY since PRIMARY KEY implies NOT NULL. However PRIMARY KEY NOT NULL does not trigger an error. The statement works as you originally wrote it as long as you execute it from ADO.

这篇关于创建表语法错误-MS Access，SQL视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！