这是我的$ C $下创建(在接入设计查询的SQL视图)表
This is my code for creating the table (in the sql view of designing a query in access)
CREATE table Track (WebCompareString CHAR(255), Master INT, Child INT,
Merged BIT NOT NULL DEFAULT 0, Children_Updated BIT NOT NULL DEFAULT 0,
Deleted BIT NOT NULL DEFAULT 0, TrackId INT PRIMARY KEY;
当试图为运行这个我碰到下面的错误! 在CREATE TABLE语句语法错误。
When trying to run! this I get the following error: Syntax error in CREATE TABLE statement.
缺少的结束括号是一个小问题。
The missing closing parenthesis is a minor problem.
的主要问题是在默认
条款使用SQL
您需要或者通过ADO连接运行SQL命令: SQL设置默认不工作在MS Access
You need to either run the SQL command via an ADO connection: SQL SET DEFAULT not working in MS Access
或设置访问选项的Unter查询设计为ANSI 92兼容: SQL添加列使用默认值
or set the Access option unter Query Design to ANSI 92 compatible: SQL to add column with default value
修改
是的,我测试了第二个选项(只有第二个),在一个.ACCDB Access 2010中。
我设置的选项对象设计 - 查询设计 - 的SQL Server兼容语法(ANSI 92) - 使用该数据库
。 (精确用词是猜,因为我有一个德国的访问)。
Yes, I tested the second option (only the second one), in Access 2010 in a .accdb.
I set the option Object Designer - Query design - SQL Server Compatible Syntax (ANSI 92) - Use in this database
. (Exact wording is guessed since I have a German Access).
访问显示一个警告,然后做一个自动精简和放大器;维修。从那以后,我可以从问题中一个新的查询执行的SQL,只需要添加右括号到SQL字符串。
Access showed a warning, then did an automatic Compact&Repair. After that I could execute the SQL from the question in a new query, only adding the closing parenthesis to the SQL string.
是/否场进行了与创建默认值= 0
。
The Yes/No fields were created with default value = 0
.
编辑2
随着的SQL Server兼容语法(ANSI 92)
选项回到未选中,我测试的第一个建议过,只需以下HansUp的code。
下面的子用默认值创建表。
With the SQL Server Compatible Syntax (ANSI 92)
option back to unchecked, I tested the first suggestion too, by simply following HansUp's code.
The following sub created the table with the default values.
Public Sub CreateTrackTable()
Dim S As String
S = "CREATE table Track (WebCompareString CHAR(255), Master INT, Child INT, " & _
"Merged BIT NOT NULL DEFAULT 0, Children_Updated BIT NOT NULL DEFAULT 0, " & _
"Deleted BIT NOT NULL DEFAULT 0, TrackId INT PRIMARY KEY);"
CurrentProject.Connection.Execute S
End Sub
所以你不能运行的查询设计此查询(除非你设置语法选项),但你可以从VBA做到这一点。
So you can't run this query from the Query designer (unless you set the syntax option), but you can do it from VBA.