Access Table creation statement

Create an empty table:
SQL = "CREATE TABLE [Table name]"

Create a table with fields:
SQL = "CREATE TABLE [Table name] ([field name 1] Memo not null, [field name 2] Memo, [field name 3] counter not null, [field name 4] datetime, [field name 5] Text (200), [field name 6] Text (200 ))

Field Type:
2: "smallint", // integer
3: "int", // long integer
4: "real", // single precision type
5: "float", // Double Precision type
6: "Money", // currency
7: "datetime", // Date and Time
11: "bit", // whether
13: "timestamp ",
17: "tinyint", // byte
72: "uniqueidentifier", // synchronous replication ID
128: "binary ",
129: "char ",
130: "nchar ",
131: "decimal", // decimal
133: "datetime ",
135: "smalldatetime ",
200: "varchar ",
201: "text ",
202: "varchar", // text
203: "text", // remarks
204: "binary", // binary
205: "image" // OLE object
The following fields are unencoded (nchar, nvarchar, and ntext)
8,128,130,202,203,204,205
The following fields are encoded according to the current system internal code (codePage = 936 can be used in ASP to correct it to gb2312 internal code)
129,200,201

Add fields to the existing table:
SQL = "ALTER TABLE [Table name] add column [field name] varchar (200 )"

Modify Field Type:
SQL = "ALTER TABLE [Table name] alter column field name] varchar (50 )"

Delete table:
SQL = "Drop table [Table name]"

Delete field:
SQL = "ALTER TABLE [Table name] Drop [field name]"

Modify Field: alter table [Table name] alter column [field name] type (size) null

Create constraint: alter table [Table name] add constraint name check ([constraint field] <= '2017-1-1 ')

Delete constraint: alter table [Table name] Drop constraint name

New default value: alter table [Table name] add constraint Default Value Name default 'gziu. com' for [field name]

Delete default value: alter table [Table name] Drop constraint Default Value Name

========================================================

Conn. Open connstr
SQL = "ALTER TABLE [tablename] add hehe char (20 )"
Conn.exe cute (SQL)
Response. Write ("added successfully ")

Access is not easy to create databases and tables. It is easier to initialize table fields.

========================================================

<%
Session ("tablen") = "news"
'News is an existing table name
Session ("fieldsn") = "C"
'Field name to be added
Connectionstring = "provider = Microsoft. Jet. oledb.4.0; Data Source =" & server. mappath ("Data/QQ. mdb ")
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open connectionstring
Jhsql = "alter table" & SESSION ("tablen") & "add column" & SESSION ("fieldsn") & "real"
Conn.exe cute (jhsql)
%>

======================================

Generate a data table and add fields. The ID field is automatically added. The test passes ~~

Sub genautoincrement.pdf ()
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: \ website creation \ ASP operation MDB \ dB \ test. mdb"
Set cat. activeconnection = Cn

Tblnam. Name = "test"
Clx. parentcatalog = cat

Clx. type = 3
Clx. Name = "ID"
Clx. properties ("autoincrement") = true
Tblnam. Columns. append clx
Tblnam. Columns. APPEND "datafield ",
Cat. Tables. append tblnam

Set clx = nothing
Set cat = nothing
CN. Close
Set Cn = nothing
End sub

Call genautoincrement.pdf

==========================================

Field Type Value

''---- 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
ConstAdguid= 72
Const addate = 7
Const addbdate = 133
Const addbtime = 134
Const addbtimestamp = 135
Const adbstr = 8
Const adchar = 129
Const advarchar = 200
Const adlongvarchar = 201
Const adwchar= 130
Const advarwchar= 202
Const adlongvarwchar= 203
Const ad binary = 128
Const advarbinary = 204
Const adlongvarbinary = 205
Const adchapter = 136
Const adfiletime = 64
Const adpropvariant= 138
Constadvarnumeric = 139
Const adarray = & h2000