2023年7月13日发(作者：)

Access建表语句创建⼀张空表：Sql="Create TABLE [表名]"创建⼀张有字段的表：Sql="Create TABLE [表名]([字段名1] MEMO NOT NULL, [字段名2] MEMO, [字段名3] COUNTER NOT NULL, [字段名4] DATETIME, [字段名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 默认值名======================================= connstrsql="alter table [tablename] add hehe char(20)"e(sql)("添加成功")ACCESS新建数据库和表还不简单，有了表字段初始化就更简单=======================================<%

session("tablen")="news"

'news是已存在的表名

session("fieldsn")="c"

'要添加的字段名

connectionstring="provider=.4.0;data source="&h("data/")

set conn=object("tion")

connectionstring

jhsql = " Alter Table "&session("tablen")&" add column "&session("fieldsn")&" real "

e(jhsql)

%>================================⽣成数据表，添加字段，其中id字段为⾃动增加，测试通过~~Sub GenAutoIncrementFld()set cn=Object("tion")set clx=Object("")set cat=Object("g")set tblnam=Object("")

"Provider=.4.0;Data Source=E:⽹站制作asp操作" Set Connection = cn = "Test" Catalog = cat

= 3 = "Id" ties("AutoIncrement") = true clx "DataField",130,20 tblnam Set clx = Nothing Set cat = Nothing Set cn = NothingEnd Subcall GenAutoIncrementFld===================================字段类型对应数值''---- DataTypeEnum Values ----

Const adEmpty = 0

Const adTinyInt = 16

Const adSmallInt = 2

Const adInteger = 3

Const adBigInt = 20

Const adUnsignedTinyInt = 17

Const adUnsignedSmallInt = 18

Const adUnsignedInt = 19

Const adUnsignedBigInt = 21

Const adSingle = 4

Const adDouble = 5

Const adCurrency = 6

Const adDecimal = 14

Const adNumeric = 131

Const adBoolean = 11

Const adError = 10

Const adUserDefined = 132

Const adVariant = 12 Const adIDispatch = 9

Const adIUnknown = 13

Const adGUID = 72 Const adDate = 7 Const adDBDate = 133 Const adDBTime = 134 Const adDBTimeStamp = 135 Const adBSTR = 8Const adChar = 129 Const adVarChar = 200 Const adLongVarChar = 201 Const adWChar = 130 Const adVarWChar = 202 ConstadLongVarWChar = 203 Const adBinary = 128 Const adVarBinary = 204 Const adLongVarBinary = 205 Const adChapter = 136 ConstadFileTime = 64 Const adPropVariant = 138 Const adVarNumeric = 139 Const adArray = &H2000