Http://www.cnblogs.com/hnyei/archive/2012/02/23/2364812.html
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 is a desktop database, single-user, able to identify most of the SQL code, create a table similar to SQL Server, give you an example:
CREATE TABLE Test
(
ID int identity () not NULL,
name varchar () is not null unique,
did int,
flag bit de Fault 0,
Num int default 0,
CDate date default now (),
Primary key (ID)
)
This code you want to use it in the program, go directly to access , it is not acceptable to do so.
want to help you