创建使用SQL查询和默认位值Access表 [英] Creating an Access table with sql query and default bit values

问题描述

这是我的$ 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.

这篇关于创建使用SQL查询和默认位值Access表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！