access数据类型大全

access数据类型大全ALTER TABLE tb ALTER COLUMN aa Byte 数字[字节]ALTER TABLE tb ALTER COLUMN aa Long 数字[长整型]ALTER TABLE tb ALTER COLUMN aa Short 数字[整型]ALTER TABLE tb ALTER COLUMN aa Single 数字[单精度]ALTER TABLE tb ALTER COLUMN aa Double 数字[双精度]ALTER TABLE tb ALTER COLUMN aa Currency 货币ALTER TABLE tb ALTER COLUMN aa Char 文本ALTER TABLE tb ALTER COLUMN aa Text(n) 文本，其中n表示字段大小ALTER TABLE tb ALTER COLUMN aa Binary 二进制ALTER TABLE tb ALTER COLUMN aa Counter 自动编号ALTER TABLE tb ALTER COLUMN aa Memo 备注ALTER TABLE tb ALTER COLUMN aa Time 日期/时间adBigInt: 20 ;adBinary : 128 ;adBoolean: 11 ;adChar: 129 ;adDBTimeStamp: 135 ;adEmpty: 0 ;adInteger: 3 ;adSmallInt: 2 ;adTinyInt: 16 ;adVarChar: 200 ;DataTypeEnum指定Field、Parameter或Property的数据类型。

在下表的“说明”列的括号中显示了相应的OLE DB 类型指示符。

有关 OLE DB 数据类型的详细信息，请参阅OLE DB Programmer's Reference 的Chapter 13和Appendix A。

ASP创建ACCESS表或添加字段2008-04-07 18:47创建一张空表：Sql="Create TABLE [表名]"创建一张有字段的表：Sql="Create TABLE [表名]([字段名1] MEMO NOT NULL, [字段名2] MEMO, [字段名3] COUNTER NOT NULL, [字段名4] DA TETIME, [字段名5] TEXT(200), [字段名6] TEXT(200))字段类型：2 : "SmallInt", // 整型3 : "Int", // 长整型4 : "Real", // 单精度型5 : "Float", // 双精度型6 : "Money", // 货币7 : "DateTime", // 日期时间11 : "Bit", // 是否13 : "TimeStamp",17 : "TinyInt", // 字节72 : "UniqueIdentifier", // 同步复制ID128 : "Binary",129 : "Char",130 : "NChar",131 : "Decimal", // 小数133 : "DateTime",135 : "SmallDateTime",200 : "VarChar",201 : "Text",202 : "VarChar", // 文本203 : "Text", // 备注204 : "Binary", // 二进制205 : "Image" // OLE 对象以下字段为无编码字段（NChar、NVarchar、NText型）8,128,130,202,203,204,205以下字段为按当前系统内码编码的字段（Asp中可用CodePage=936纠正为gb2312内码）129,200,201在现有的表中增加字段：Sql="alter table [表名] add column [字段名] varchar(200)"修改字段类型：Sql="alter table [表名] Alter COLUMN 字段名] varchar(50)"删除表：Sql="Drop table [表名]"删除字段：sql="alter table [表名] drop [字段名]"修改字段：Alter TABLE [表名] Alter COLUMN [字段名] 类型(大小) NULL新建约束：Alter TABLE [表名] ADD CONSTRAINT 约束名CHECK ([约束字段] <= '2007-1-1')删除约束：Alter TABLE [表名] Drop CONSTRAINT 约束名新建默认值：Alter TABLE [表名] ADD CONSTRAINT 默认值名DEFAULT '' FOR [字段名] 删除默认值：Alter TABLE [表名] Drop CONSTRAINT 默认值名=======================================conn.open connstrsql="alter table [tablename] add hehe char(20)"conn.execute(sql)response.write("添加成功")ACCESS新建数据库和表还不简单，有了表字段初始化就更简单=======================================<%session("tablen")="news"'news是已存在的表名session("fieldsn")="c"'要添加的字段名connectionstring="provider=microsoft.jet.oledb.4.0;data source="&server.MapPath("data/qq.mdb")set conn=server.createobject("adodb.connection")conn.OPEN connectionstringjhsql = " Alter Table "&session("tablen")&" add column "&session("fieldsn")&" real "conn.execute(jhsql)%>================================生成数据表，添加字段，其中id字段为自动增加，测试通过~~Sub GenAutoIncrementFld()set cn=server.CreateObject("ADODB.Connection")set clx=server.CreateObject("ADOX.Column")set cat=server.CreateObject("ADOX.Catalog")set tblnam=server.CreateObject("ADOX.Table")cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\网站制作\asp操作mdb\db\test.mdb"Set cat.ActiveConnection = cn = "Test"clx.ParentCatalog = catclx.Type = 3 = "Id"clx.Properties("AutoIncrement") = truetblnam.Columns.Append clxtblnam.Columns.Append "DataField",130,20cat.Tables.Append tblnamSet clx = NothingSet cat = Nothingcn.CloseSet cn = NothingEnd Subcall GenAutoIncrementFld===================================字段类型对应数值''---- DataTypeEnum Values ---- Const adEmpty = 0Const adTinyInt = 16Const adSmallInt = 2Const adInteger = 3Const adBigInt = 20Const adUnsignedTinyInt = 17 Const adUnsignedSmallInt = 18 Const adUnsignedInt = 19 Const adUnsignedBigInt = 21 Const adSingle = 4Const adDouble = 5Const adCurrency = 6Const adDecimal = 14Const adNumeric = 131Const adBoolean = 11Const adError = 10Const adUserDefined = 132 Const adVariant = 12Const adIDispatch = 9Const adIUnknown = 13 Const adGUID = 72Const adDate = 7Const adDBDate = 133Const adDBTime = 134Const adDBTimeStamp = 135 Const adBSTR = 8Const adChar = 129Const adVarChar = 200Const adLongVarChar = 201 Const adWChar = 130Const adVarWChar = 202 Const adLongVarWChar = 203 Const adBinary = 128Const adVarBinary = 204 Const adLongVarBinary = 205 Const adChapter = 136Const adFileTime = 64Const adPropV ariant = 138 Const adVarNumeric = 139 Const adArray = &H2000+)adBinary 128 BinaryTimeStampRaw * adBoolean 11 YesNo BitadChar 129 Char CharadCurrency 6 Currency MoneySmallMoneyadDate 7 Date DateTimeadDBTimeStamp 135 DateTime (Access 97 (ODBC)) DateTimeSmallDateTimeDate adDecimal 14 Decimal * adDouble 5 Double Float FloatadGUID 72 ReplicationID (Access 97(OLEDB)), (Access 2000(OLEDB)) UniqueIdentifier (SQL Server 7.0 +)adIDispatch 9adInteger 3 AutoNumberIntegerLong Identity (SQL Server6.5)IntInt *adLongVarBinary 205 OLEObject Image Long Raw *Blob (Oracle8.1.x)adLongVarChar 201 Memo (Access 97)Hyperlink (Access 97) Text Long *Clob (Oracle8.1.x)adLongVarWChar 203 Memo (Access 2000 (OLEDB))Hyperlink (Access 2000(OLEDB)) NText (SQL Server 7.0 +) NClob (Oracle8.1.x)adNumeric 131 Decimal (Access 2000(OLEDB)) DecimalNumericDecimalIntegerNumberSmallIntadSingle 4 Single RealadSmallInt 2 Integer SmallIntadUnsignedTinyInt 17 Byte TinyIntadVarBinary 204 ReplicationID (Access 97) VarBinaryadVarChar 200 Text (Access 97) VarChar VarCharVarChar2 adVariant 12 Sql_Variant (SQL Server2000 +)adVarWChar 202 Text (Access 2000 (OLEDB)) NVarChar (SQL ServerNVarChar27.0 +)adWChar 130 NChar (SQL Server 7.0 +)。