Access SQL statements Create tables and field types

Create a blank 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",//integral type
3: "Int",//Long Integer
4: "Real",//single-precision
5: "Float",//double-precision
6: "Money",//Currency
7: "DateTime",//Date Time
One: "Bit",//whether
: "TimeStamp",
Page: "TinyInt",//bytes
"UniqueIdentifier",//synchronous replication ID
"Binary",
129: "Char",
"NChar",
131: "Decimal",//Decimal
133: "DateTime",
135: "smalldatetime",
$: "VarChar",
201: "Text",
202: "VarChar",//Text
203: "Text",//Notes
204: "Binary",//Binary
205: "Image"//OLE Object

The following fields are non-encoded fields (NChar, NVarchar, ntext) 8,128,130,202,203,204,205 fields that are encoded by the current system code (the codepage=936 that are available in the ASP are corrected to the gb2312 inner code) 129,200,201

To add a field to an existing table:
sql= "ALTER TABLE [table name] Add column [Field name] varchar (200)"

To modify a field type:
sql= "ALTER TABLE [table name] alter COLUMN field name] varchar (50)"

To delete a table:
sql= "Drop table [table name]"

To delete a field:
sql= "ALTER TABLE [table name] drop [field name]"

To modify a field:
ALTER TABLE [table name] Alter COLUMN [field name] Type (size) NULL

New constraint:
Alter table [table name] ADD CONSTRAINT constraint name CHECK ([constraint field] <= ' 2007-1-1 ')

To delete a constraint:
Alter table [table name] Drop CONSTRAINT constraint name

New default value:
Alter table [table name] ADD CONSTRAINT Default value name ' Gziu.com ' for [field name]

Delete default values:
Alter table [table name] Drop CONSTRAINT Default value name

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

Conn.Open connstr sql= "ALTER TABLE [tablename] add hehe char ()" Conn.execute (SQL) Response.Write ("Add success")

Access new database and table is not simple, it is easier to initialize with table fields

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

<%
Session ("Tablen") = "News"
' News is a table name that already exists
Session ("FIELDSN") = "C"
' The name of the field 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.execute (Jhsql)
%>

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

Generate Data table, add field, where ID field is automatically incremented, test through ~ ~

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:\ website Production \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", 130,20
Cat. Tables.append Tblnam

Set CLX = Nothing
Set cat = Nothing
cn. Close
Set cn = Nothing End Sub

Call GENAUTOINCREMENTFLD

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

field type corresponding numeric 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
Const Adguid = 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 adbinary = 128
Const advarbinary = 204
Const Adlongvarbinary = 205
Const adchapter = 136
Const Adfiletime = 64
Const adpropvariant = 138
Const advarnumeric = 139
Const Adarray = &h2000

Access SQL statements Create tables and field types