Few Access Database Operations, ASP, create database files, create tables, create fields, ADOX

Last Update:2018-12-08 Source: Internet

Author: User

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

CopyCode The Code is as follows: <% @ Language = "VBScript" codePage = "936" %>
<%
If trim (request. Form ("tablename "))

'---- Set to open the database connection
DBS = "Data Source =" + server. mappath ("database. mdb") + "; provider = Microsoft. Jet. oledb.4.0 ;"
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open DBS

'Define ADOX and specify the connection
Set ADOX = server. Createobject ("ADOX. catalog ")
Set ADOX. activeconnection = Conn

'Create a table and write Properties
Set objtable = server. Createobject ("ADOX. Table ")
'Objtable. Name = "table3"
Objtable. Name = trim (request. Form ("tablename") 'form acquisition
'Objtable. parentcatalog = ADOX

'Define the first field
Set objcolumn = server. Createobject ("ADOX. Column ")
'Set objcolumn. parentcatalog = ADOX
Objcolumn. Name = "column1"
Objcolumn. type = 202 'data type 202 indicates text,
Objcolumn. Attributes = 2' 1 -- required field, 2 -- not required
Objtable. Columns. append objcolumn
Set objcolumn = nothing 'clear information about the first field

'Define the Second Field
Set objcolumn = server. Createobject ("ADOX. Column ")
'Set objcolumn. parentcatalog = ADOX
Objcolumn. Name = "column2"
Objcolumn. type = 3
Objcolumn. Attributes = 2
Objtable. Columns. append objcolumn
Set objcolumn = nothing

ADOX. Tables. append objtable

Set ADOX = nothing

Create a table and field

<Body>
 'Use the attributes and set of the column object. You can: 
'Use the name attribute to identify the column. 
'Use the type attribute to specify the Data Type of the column. 
'Use the attributes attribute to determine whether the column is of a fixed length or whether it can contain null values. 
'Use the definedsize attribute to specify the maximum column size. 
'For numeric data values, use the numericscale attribute to specify the range. 
'For numeric data values, use the precision attribute to specify the maximum precision. 
'Specify the parent catalog of a column using the parentcatalog attribute. 
'For key columns, use the relatedcolumn attribute to specify the names of related columns in the relevant table. 
'For index columns, use the sortorder attribute to specify whether the sorting order is ascending or descending. 
 data types: 
<Table border = 1 cellpadding = 4 cellspacing = 4 Cols = 4 frame = Box rules = all width = 100%>

<Tr valign = "TOP">
<TH width = 32%> constant </Th>
<TH width = 13%> value </Th>
<TH width = 55%> description </Th>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adarray 
 (not applicable to ADOX .) </TD>
<TD class = T width = 13%> 0x2000
 
</TD>
<TD class = T width = 55%> A flag value, usually combined with another data type constant, indicates an array of this data type. </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adbigint 
 
</TD>
<TD class = T width = 13%> 20 </TD>
<TD class = T width = 55%> indicates the signed integer (dbtype_i8) of an eight-character section ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adbinary 
 
</TD>
<TD class = T width = 13%> 128 </TD>
<TD class = T width = 55%> indicates a binary value (dbtype_bytes ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adboolean 
 
</TD>
<TD class = T width = 13%> 11 </TD>
<TD class = T width = 55%> indicates a Boolean value (dbtype_bool ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adbstr 
 
</TD>
<TD class = T width = 13%> 8 </TD>
<TD class = T width = 55%> indicates the string (UNICODE) (dbtype_bstr) terminated with null ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adchapter 
 
</TD>
<TD class = T width = 13%> 136 </TD>
<TD class = T width = 55%> indicates a four-byte subset value that identifies the row (dbtype_hchapter) in the <a href = "mddefrowset.htm"> row set </a> ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adchar 
 
</TD>
<TD class = T width = 13%> 129 </TD>
<TD class = T width = 55%> indicates a string value (dbtype_str ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adcurrency 
 
</TD>
<TD class = T width = 13%> 6 </TD>
<TD class = T width = 55%> indicates a currency value (dbtype_cy ). Currency is a fixed-point number with four digits to the right of the decimal point. The value is a signed integer with an octal section and a range of 10,000. </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> addate 
 
</TD>
<TD class = T width = 13%> 7 </TD>
<TD class = T width = 55%> indicates the date value (dbtype_date ). The date is saved as a double-precision number. The integer part of the number is the number of days counted from January 1, December 30, 1899, and the fractional part is the part time of the day. </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> addbdate 
 
</TD>
<TD class = T width = 13%> 133 </TD>
<TD class = T width = 55%> indicates the date value (yyyymmdd) (dbtype_dbdate ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> addbtime 
 
</TD>
<TD class = T width = 13%> 134 </TD>
<TD class = T width = 55%> indicates the time value (hhmmss) (dbtype_dbtime ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> addbtimestamp 
 
</TD>
<TD class = T width = 13%> 135 </TD>
<TD class = T width = 55%> indicates the date/time stamp (yyyymmddhhmmss plus a decimal point in one thousandth) (dbtype_dbtimestamp ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> addecimal 
 
</TD>
<TD class = T width = 13%> 14 </TD>
<TD class = T width = 55%> indicates the exact numeric value (dbtype_decimal) with fixed precision and range ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> addouble 
 
</TD>
<TD class = T width = 13%> 5 </TD>
<TD class = T width = 55%> indicates a double-precision floating point value (dbtype_r8 ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adempty 
 
</TD>
<TD class = T width = 13%> 0 </TD>
<TD class = T width = 55%> no value (dbtype_empty) is specified ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> aderror 
 
</TD>
<TD class = T width = 13%> 10 </TD>
<TD class = T width = 55%> indicates a 32-bit error code (dbtype_error ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adfiletime 
 
</TD>
<TD class = T width = 13%> 64 </TD>
<TD class = T width = 55%> indicates a 64-bit value, indicating the number of interval (dbtype_filetime) between January 1, 1601 million and one thousandth in one second since January 1, 100 ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adguid 
 
</TD>
<TD class = T width = 13%> 72 </TD>
<TD class = T width = 55%> indicates a globally unique identifier (guid) (dbtype_guid ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adidispatch 
 
</TD>
<TD class = T width = 13%> 9 </TD>
<TD class = T width = 55%> pointer to the idispatch interface on the COM Object (dbtype_idispatch ).
 note ADO currently does not support this data type. Using it may lead to unexpected results. 
</TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adinteger 
 
</TD>
<TD class = T width = 13%> 3 </TD>
<TD class = T width = 55%> indicates a four-byte signed integer (dbtype_i4 ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adiunknown 
 
</TD>
<TD class = T width = 13%> 13 </TD>
<TD class = T width = 55%> pointer to the iunknown interface on the COM Object (dbtype_iunknown ).
 note ADO currently does not support this data type. Using it may lead to unexpected results. 
</TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adlongvarbinary 
 
</TD>
<TD class = T width = 13%> 205 </TD>
<TD class = T width = 55%> indicates a long binary value (only for parameter objects ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adlongvarchar 
 
</TD>
<TD class = T width = 13%> 201 </TD>
<TD class = T width = 55%> indicates a long string value (only for parameter objects ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adlongvarwchar 
 
</TD>
<TD class = T width = 13%> 203 </TD>
<TD class = T width = 55%> indicates a long Unicode string value terminated with null (limited to parameter objects ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adnumeric 
 
</TD>
<TD class = T width = 13%> 131 </TD>
<TD class = T width = 55%> indicates the exact numeric value (dbtype_numeric) with fixed precision and range ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adpropvariant 
 
</TD>
<TD class = T width = 13%> 138 </TD>
<TD class = T width = 55%> indicates an automation propvariant (dbtype_prop_variant ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adsingle 
 
</TD>
<TD class = T width = 13%> 4 </TD>
<TD class = T width = 55%> indicates a single precision floating point value (dbtype_r4 ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adsmallint 
 
</TD>
<TD class = T width = 13%> 2 </TD>
<TD class = T width = 55%> indicates a two-byte signed integer (dbtype_i2 ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adtinyint 
 
</TD>
<TD class = T width = 13%> 16 </TD>
<TD class = T width = 55%> indicates a signed integer (dbtype_i1) in a single byte ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adunsignedbigint 
 
</TD>
<TD class = T width = 13%> 21 </TD>
<TD class = T width = 55%> indicates the unsigned integer (dbtype_ui8) of an eight-character section ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adunsignedint 
 
</TD>
<TD class = T width = 13%> 19 </TD>
<TD class = T width = 55%> indicates a four-byte unsigned integer (dbtype_ui4 ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adunsignedsmallint 
 
</TD>
<TD class = T width = 13%> 18 </TD>
<TD class = T width = 55%> indicates a two-byte unsigned integer (dbtype_ui2 ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adunsignedtinyint 
 
</TD>
<TD class = T width = 13%> 17 </TD>
<TD class = T width = 55%> indicates an unsigned integer (dbtype_ui1) in a single byte ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> aduserdefined 
 
</TD>
<TD class = T width = 13%> 132 </TD>
<TD class = T width = 55%> indicates a user-defined variable (dbtype_udt ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> advarbinary 
 
</TD>
<TD class = T width = 13%> 204 </TD>
<TD class = T width = 55%> indicates a binary value (only for parameter objects ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> advarchar 
 
</TD>
<TD class = T width = 13%> 200 </TD>
<TD class = T width = 55%> indicates a string value (only for parameter objects ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> advariant 
 
</TD>
<TD class = T width = 13%> 12 </TD>
<TD class = T width = 55%> indicates an automation variant (dbtype_variant ).
 note ADO currently does not support this data type. Using it may lead to unexpected results. 
</TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> advarnumeric 
 
</TD>
<TD class = T width = 13%> 139 </TD>
<TD class = T width = 55%> indicates a numeric value (only for parameter objects ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> advarwchar 
 
</TD>
<TD class = T width = 13%> 202 </TD>
<TD class = T width = 55%> indicates a unicode string terminated with null (only for parameter objects ). </TD>
</Tr>

<Tr valign = "TOP">
<TD class = T width = 32%> adwchar 
 
</TD>
<TD class = T width = 13%> 130 </TD>
<TD class = T width = 55%> indicates a unicode string (dbtype_wstr) terminated with null ). </TD>
</Tr>
</Table>
</Body>
</Html>

Create a field in an existing table. Copy codeThe Code is as follows: <%
DBS = "Data Source =" + server. mappath ("database. mdb") + "; provider = Microsoft. Jet. oledb.4.0 ;"
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open DBS
Set cat = server. Createobject ("ADOX. catalog ")
Set cat. activeconnection = Conn
Set TBL = cat. Tables ("BBS ")
Set objcolumn = server. Createobject ("ADOX. Column ")
Set objcolumn. parentcatalog = cat
Objcolumn. Name = "time1"
Objcolumn. types = "130"
Objcolumn. Attributes = 2
TBL. Columns. append objcolumn

Set objtable = nothing
Set objcat = nothing

Delete and modify FieldsCopy codeThe Code is as follows: <%
DBS = "Data Source =" + server. mappath ("database. mdb") + "; provider = Microsoft. Jet. oledb.4.0 ;"
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open DBS
Set cat = server. Createobject ("ADOX. catalog ")
Set cat. activeconnection = Conn
Set TBL = cat. Tables ("BBS ")
TBL. Columns. Delete "time"

Set field = TBL. Columns ("time2 ")

Field. Name = "time"
%>

Create Database FilesCopy codeThe Code is as follows: <% @ Language = "VBScript" codePage = "936" %>
<%
Option explicit
Dim databasename 'defines the Database Name
Databasename = "database. mdb" 'Database Name
Dim databasepath 'defines the database storage path
Databasepath = "E: \ Zhang Wei \ module development \ News Module \" 'database absolute path
Dim databasever 'defines the database version 2000 or 97
Databasever = "2000"

Function createdfile (filepath, filename, Ver)
Dim ca, dbver
Select case ver
Case "97"
Dbver = "3.51"
Case "2000"
Dbver = "4.0"
End select
If dbver <> "" then
Set CA = server. Createobject ("ADOX. catalog ")
Call ca. Create ("provider = Microsoft. Jet. oledb." & dbver & "; Data Source =" & filepath & filename)
End if
End Function

Createdfile databasepath, databasename, databasever

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More

Few Access Database Operations, ASP, create database files, create tables, create fields, ADOX

Contact Us

What's Trending

Top 10 Tags

Top 10 Keywords

A Free Trial That Lets You Build Big!

Sales Support

After-Sales Support

Few Access Database Operations, ASP, create database files, create tables, create fields, ADOX

Contact Us

What's Trending

Top 10 Tags

Top 10 Keywords

Trending Topic

A Free Trial That Lets You Build Big!

Sales Support

After-Sales Support