数据类型等效项:MS Access 表 ↔ 'CREATE TABLE' 查询 ↔ ODBC SQL [英] Data type equivalents: MS Access Tables ↔ 'CREATE TABLE' Queries ↔ ODBC SQL

问题描述

使用 SQL 在 Access 中创建表时的正确语法是什么?我已经尝试过 DECIMAL、DOUBLE、NUMBER、INT... 没有什么可以让我创建带有限制器的整数类别.

例子:

CREATE TABLE NONGAME (ITEM_NUM CHAR(4) 非空主键，描述 CHAR(30),ON_HAND NUMBER(4), <-------- 不起作用！类别字符(3)，PRICE DECIMAL(6,2), <-------- 不起作用！任何双 (4,2) <-------- 都不起作用！);

解决方案

MICROSOFT ACCESS DATA TYPES

下表显示了 Microsoft Access 数据类型、用于创建表的数据类型和 ODBC SQL 数据类型.某些类型有限制，如下表所示.

Microsoft Access 数据类型 数据类型 (CREATE TABLE) ODBC SQL 数据类型~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~BIGBINARY[1] LONGBINARY SQL_LONGVARBINARYBINARY BINARY SQL_BINARYBIT BIT SQL_BIT计数器 计数器 SQL_INTEGER货币 货币 SQL_NUMERIC日期/时间 DATETIME SQL_TIMESTAMPGUID GUID SQL_GUIDLONG BINARY LONGBINARY SQL_LONGVARBINARY长文本长文本 SQL_LONGVARCHAR[2]备忘 LONGTEXT SQL_LONGVARCHAR[2]NUMBER (FieldSize= SINGLE) SINGLE SQL_REALNUMBER (FieldSize= DOUBLE) DOUBLE SQL_DOUBLENUMBER (FieldSize= BYTE) UNSIGNED BYTE SQL_TINYINTNUMBER (FieldSize= INTEGER) SHORT SQL_SMALLINTNUMBER (FieldSize= LONG INTEGER) LONG SQL_INTEGERNUMERIC NUMERIC SQL_NUMERICOLE LONGBINARY SQL_LONGVARBINARY文本 VARCHAR SQL_VARCHAR[1]ARBINARY VARBINARY SQL_VARBINARY[1] 仅访问 4.0 应用程序.Max 4000 B. 类似于 LONGBINARY 的行为.[2] 仅限 ANSI 应用程序.[3] 仅限 Unicode 和 Access 4.0 应用程序.

<块引用>

注意: SQLGetTypeInfo 返回ODBC 数据类型.如果多个 Microsoft Access 类型映射到同一个 ODBC SQLMicrosoft Access 数据类型> 数据类型.ODBC 程序员参考的附录 D.

<小时>

Microsoft Access 数据类型的限制

• BINARY**、**VARBINARY** 和 **VARCHAR:创建 BINARY、VARBINARY 或 VARCHAR 列为零或未指定长度实际上返回一个 510 字节的列.
• BYTE:即使 Microsoft Access NUMBER 字段的 FieldSize 等于 BYTE 是无符号的，使用 Microsoft Access 驱动程序时可以在字段中插入负数.
• CHAR**、**LONGVARCHAR** 和 **VARCHAR:字符串文字可以包含任何 ANSI 字符(十进制 1-255).使用两个连续的单引号('')来表示一个单引号(').在字符数据类型列中使用任何特殊字符时，应使用过程来传递字符数据.
• DATE:日期值必须根据 ODBC 规范日期格式分隔或由日期时间分隔符 (#) 分隔.否则，Microsoft Access 会将值视为算术表达式，并且不会引发警告或错误.
  • 例如，日期March 5, 1996"必须表示为 {d '1996-03-05'} 或 #03/05/1996#;否则，如果仅提交 03/05/1993，Microsoft Access 会将其评估为 3 ÷ 5 ÷ 1996.该值向上取整为整数 0，由于零日映射到 1899-12-31，因此这是使用的日期.
  • 管道字符 (|) 不能用于日期值，即使用反引号括起来.
• GUID:数据类型仅限于 Microsoft Access 4.0.
• NUMERIC:数据类型仅限于 Microsoft Access 4.0.

^{(更多信息请参见 来源)}

<小时>

对 ODBC 桌面驱动程序数据类型的限制

Microsoft ODBC 桌面数据库驱动程序对数据类型施加了以下限制:

• 所有数据类型 类型转换失败可能会导致受影响的列被设置为 NULL.
• BINARY 创建一个长度为零的 BINARY 列实际上会返回一个 255 字节的 BINARY 列.
• DATE DATE 数据类型无法通过 CONVERT 转换为另一种数据类型(或自身)功能.
• DECIMAL(精确数字)** 不支持.
• 浮点数据类型 浮点数的小数位数可能受Windows 控制面板的国际部分.
• NUMERIC 支持最大精度和 28 位.
• TIMESTAMP CONVERT 函数无法将 TIMESTAMP 数据类型转换为自身.
• TINYINT:TINYINT 值始终是无符号的.
• 零长度字符串:当使用 dBASE、Microsoft Excel、Paradox 或 Textdriver 时，将零长度字符串插入列实际上会插入 NULL.

^(来源)

<小时>

更多信息:

• MSDN:使用 Access SQL 创建和删除表和索引
• MSDN:创建表 语句 (Microsoft Access SQL)
• Microsoft 文档:Microsoft Access 数据类型
• Microsoft 文档:数据类型限制
• Microsoft 文档:在 ODBC 和 SQL Server 数据类型之间转换
• Microsoft 文档:SQL ODBC 桌面驱动程序的限制
• 维基百科:开放式数据库连接 (ODBC)李>

<小时>

Erik 的小附录:

您实际上可以在 CREATE TABLE 查询中使用 Decimal 数据类型.但是，这需要使用 ADO 或在设置为使用 ANSI-92 兼容语法的数据库上执行您的语句.

要将您的数据库设置为 ANSI-92 兼容的语法:

转到文件 -> 选项.打开选项卡对象设计器.转到查询设计器，在 SQL Server Compatible Syntax (ANSI 92) 下，选中This Database.现在您可以执行查询.请注意，这会影响数据库中的所有查询，并以多种方式影响查询.

使用 ADO 执行查询:

在 VBA 即时窗口中，执行以下行:

CurrentProject.Connection.Execute "CREATE TABLE NONGAME (ITEM_NUM CHAR(4) NOT NULL PRIMARY KEY, PRICE DECIMAL(6,2));"

当然，您可以使用 ADO 执行更复杂的查询.

What is the correct syntax when creating a table in Access with SQL? I have tried DECIMAL, DOUBLE, NUMBER, INT... nothing lets me create an integer category with limiters.

Example:

CREATE TABLE NONGAME (
  ITEM_NUM CHAR(4) NOT NULL PRIMARY KEY,
  DESCRIPTION CHAR(30),
  ON_HAND NUMBER(4),   <------- DOES NOT WORK!
  CATEGORY CHAR(3),
  PRICE DECIMAL(6,2),  <------- DOES NOT WORK!
  ANYTHING DOUBLE(4,2) <------- DOES NOT WORK!
);

解决方案

MICROSOFT ACCESS DATA TYPES

The following table shows the Microsoft Access data types, data types used to create tables, and ODBC SQL data types. Some types have limitations, outlined following the table.

Microsoft Access data type        Data type (CREATE TABLE) ODBC SQL data type  
~~~~~~~~~~~~~~~~~~~~~~~~~~        ~~~~~~~~~~~~~~~~~~~~~~~  ~~~~~~~~~~~~~~~~~~  
BIGBINARY[1]                      LONGBINARY               SQL_LONGVARBINARY   
BINARY                            BINARY                   SQL_BINARY          
BIT                               BIT                      SQL_BIT             
COUNTER                           COUNTER                  SQL_INTEGER         
CURRENCY                          CURRENCY                 SQL_NUMERIC         
DATE/TIME                         DATETIME                 SQL_TIMESTAMP       
GUID                              GUID                     SQL_GUID            
LONG BINARY                       LONGBINARY               SQL_LONGVARBINARY   
LONG TEXT                         LONGTEXT                 SQL_LONGVARCHAR[2]  
MEMO                              LONGTEXT                 SQL_LONGVARCHAR[2]  
NUMBER (FieldSize= SINGLE)        SINGLE                   SQL_REAL            
NUMBER (FieldSize= DOUBLE)        DOUBLE                   SQL_DOUBLE          
NUMBER (FieldSize= BYTE)          UNSIGNED BYTE            SQL_TINYINT         
NUMBER (FieldSize= INTEGER)       SHORT                    SQL_SMALLINT        
NUMBER (FieldSize= LONG INTEGER)  LONG                     SQL_INTEGER         
NUMERIC                           NUMERIC                  SQL_NUMERIC         
OLE                               LONGBINARY               SQL_LONGVARBINARY   
TEXT                              VARCHAR                  SQL_VARCHAR[1]      
ARBINARY                          VARBINARY                SQL_VARBINARY       

[1] Access 4.0 applications only. Max 4000 B. Behaviour similar to LONGBINARY.
[2] ANSI applications only.
[3] Unicode and Access 4.0 applications only. 

Note: SQLGetTypeInfo returns ODBC data types. It will not return all Microsoft Access data types if more than one Microsoft Access type is mapped to the same ODBC SQL data type. All conversions in Appendix D of the ODBC Programmer's Reference are supported for the SQL data types listed in the previous table.

---

Limitations on Microsoft Access data types

• BINARY**, **VARBINARY**, and **VARCHAR: Creating a BINARY, VARBINARY, or VARCHAR column of zero or unspecified length actually returns a 510-byte column.
• BYTE: Even though a Microsoft Access NUMBER field with a FieldSize equal to BYTE is unsigned, a negative number can be inserted into the field when using the Microsoft Access driver.
• CHAR**, **LONGVARCHAR**, and **VARCHAR: A character string literal can contain any ANSI character (1-255 decimal). Use two consecutive single quotation marks ('') to represent one single quotation mark ('). Procedures should be used to pass character data when using any special character in a character data type column.
• DATE: Date values must be either delimited according to the ODBC canonical date format or delimited by the datetime delimiter (#). Otherwise, Microsoft Access will treat the value as an arithmetic expression and will not raise a warning or error.
  • For example, the date "March 5, 1996" must be represented as {d '1996-03-05'} or #03/05/1996#; otherwise, if only 03/05/1993 is submitted, Microsoft Access will evaluate this as 3 ÷ 5 ÷ 1996. This value rounds up to the integer 0, and since the zero day maps to 1899-12-31, this is the date used.
  • A pipe character (|) cannot be used in a date value, even if enclosed in back quotes.
• GUID: Data type limited to Microsoft Access 4.0.
• NUMERIC: Data type limited to Microsoft Access 4.0.

^{(More information at the Source)}

---

Limitations on ODBC Desktop Driver data types

The Microsoft ODBC Desktop Database Drivers impose the following limitations on data types:

• All data types Type conversion failures might result in the affected column being set to NULL.
• BINARY Creating a zero-length BINARY column actually returns a 255-byte BINARY column.
• DATE The DATE data type cannot be converted to another data type (or itself) by the CONVERT function.
• DECIMAL (Exact Numeric)** Not supported.
• Floating-Point Data Types The number of decimal places in a floating-point number may be limited by the number format set in the International section of the Windows Control Panel.
• NUMERIC Supports maximum precision and a scale of 28.
• TIMESTAMP The TIMESTAMP data type cannot be converted to itself by the CONVERT function.
• TINYINT: TINYINT values are always unsigned.
• Zero-Length Strings: When a dBASE, Microsoft Excel, Paradox, or Textdriver is used, inserting a zero-length string into a column actually inserts a NULL instead.

^(Source)

---

More Information:

• MSDN : Create and Delete Tables and Indexes Using Access SQL
• MSDN : CREATE TABLE Statement (Microsoft Access SQL)
• Microsoft Docs : Microsoft Access Data Types
• Microsoft Docs : Data Type Limitations
• Microsoft Docs : Converting between ODBC and SQL Server data types
• Microsoft Docs : Limitations of SQL ODBC Desktop Drivers
• Wikipedia : Open Database Connectivity (ODBC)

---

Small addendum by Erik:

You can actually use the Decimal data type in CREATE TABLE queries. However, this requires your statement to be executed either using ADO, or on a database that's been set to use ANSI-92 compatible syntax.

To set your database to ANSI-92 compatible syntax:

Go to File -> Options. Open the tab Object Designers. Go to Query Designer, and under SQL Server Compatible Syntax (ANSI 92), check This Database. Now you can just execute the query. Note that this affects all queries in the database, and affects queries in various ways.

To execute a query using ADO:

In the VBA Immediate Window, execute the following line:

CurrentProject.Connection.Execute "CREATE TABLE NONGAME (ITEM_NUM CHAR(4) NOT NULL PRIMARY KEY,  PRICE DECIMAL(6,2));"

Of course, you can execute more complex queries using ADO.

这篇关于数据类型等效项:MS Access 表 ↔ 'CREATE TABLE' 查询 ↔ ODBC SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！