等效数据类型:MS Access 表 ↔ 'CREATE TABLE' 查询 ↔ ODBC SQL [英] Data type equivalents: MS Access Tables ↔ 'CREATE TABLE' Queries ↔ ODBC SQL
问题描述
在 Access 中使用 SQL 创建表时,正确的语法是什么?我已经尝试过 DECIMAL、DOUBLE、NUMBER、INT... 没有什么可以让我创建一个带限制器的整数类别.
示例:
创建表非名称(ITEM_NUM CHAR(4) NOT NULL PRIMARY KEY,说明 CHAR(30),ON_HAND NUMBER(4), <------- 不起作用!类别字符(3),十进制价格 (6,2), <------- 不起作用!任何 DOUBLE(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_INTEGERCURRENCY CURRENCY SQL_NUMERIC日期/时间 DATETIME SQL_TIMESTAMPGUID GUID SQL_GUIDLONG BINARY LONGBINARY SQL_LONGVARBINARYLONG TEXT LONGTEXT 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]任意变量 SQL_VARBINARY[1] 仅访问 4.0 应用程序.最大 4000 B. 行为类似于 LONGBINARY.[2] 仅限 ANSI 应用程序.[3] 仅限 Unicode 和 Access 4.0 应用程序.
<块引用>
注意: SQLGetTypeInfo
返回 ODBC
数据类型.如果多个 Microsoft Access 类型被映射到相同的 ODBC
SQL
,它将不会返回所有的 Microsoft Access 数据类型> 数据类型.附录 D.>
<小时>
Microsoft Access 数据类型的限制
BINARY**
、**VARBINARY**
和 **VARCHAR
:创建BINARY
、VARBINARY
或VARCHAR
列为零或未指定长度实际上返回一个 510 字节的列.BYTE
:即使 Microsoft AccessNUMBER
字段的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
TIMESTAMP
数据类型无法通过 CONVERT 函数转换为自身.TINYINT
:TINYINT 值总是无符号的.零长度字符串
:当使用 dBASE、Microsoft Excel、Paradox 或 Textdriver 时,将零长度字符串插入到一列实际上插入了一个NULL
.
(来源)
<小时>更多信息:
- MSDN:使用 Access SQL 创建和删除表和索引
- MSDN:
创建表代码> 语句
(Microsoft Access SQL) - 微软文档:Microsoft Access 数据类型
- Microsoft Docs:数据类型限制
- 微软文档:ODBC 和 SQL Server 数据类型之间的转换
- 微软文档:SQL ODBC 桌面驱动程序的限制
- 维基百科:开放数据库连接 (ODBC)立>
Erik 的小附录:
您实际上可以在 CREATE TABLE
查询中使用 Decimal
数据类型.但是,这需要使用 ADO 或在已设置为使用 ANSI-92 兼容语法的数据库上执行您的语句.
要将您的数据库设置为与 ANSI-92 兼容的语法:
转到文件 -> 选项.打开标签对象设计器.转到查询设计器,然后在 SQL Server 兼容语法 (ANSI 92) 下,选中此数据库.现在您可以执行查询.请注意,这会影响数据库中的所有查询,并以各种方式影响查询.
使用 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
returnsODBC
data types. It will not return all Microsoft Access data types if more than one Microsoft Access type is mapped to the sameODBC
SQL
data type. All conversions in Appendix D of the ODBC Programmer's Reference are supported for theSQL
data types listed in the previous table.
Limitations on Microsoft Access data types
BINARY**
, **VARBINARY**
, and **VARCHAR
: Creating aBINARY
,VARBINARY
, orVARCHAR
column of zero or unspecified length actually returns a 510-byte column.BYTE
: Even though a Microsoft AccessNUMBER
field with aFieldSize
equal toBYTE
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 only03/05/1993
is submitted, Microsoft Access will evaluate this as3 ÷ 5 ÷ 1996
. This value rounds up to the integer0
, and since the zero day maps to1899-12-31
, this is the date used. - A pipe character (
|
) cannot be used in a date value, even if enclosed in back quotes.
- For example, the date "
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-lengthBINARY
column actually returns a 255-byteBINARY
column.DATE
TheDATE
data type cannot be converted to another data type (or itself) by theCONVERT
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
TheTIMESTAMP
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 aNULL
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屋!