Programming and Application(编程与应用)

Content(目录)

Linux

MySQL
Office

Create an MS Access database by ASP VBScript

Zhanshan Dong

Long time ago I ever dreamed of creating MS Access databases through VBScript for ASP server. But I do not know how to create AutoNumber field. I spent two night at home to write this vbscrip because I got a bad cold and did not want to go to office. At the beginning, I tried to use ADO functions to fullfil this goal. I failed to do it. Until the last moment that I almost gave up the idea, I read the SQL help in Access thoroughly, I found the following information for data type in Equivalent ANSI SQL Data Types of Microsoft Jet SQL reference.

ANSI SQL data type	Microsoft Jet SQL data type	Synonym	Microsoft SQL Server data type
BIT, BIT VARYING	BINARY (See Notes)	VARBINARY, BINARY VARYING BIT VARYING	BINARY, VARBINARY
Not supported	BIT (See Notes)	BOOLEAN, LOGICAL, LOGICAL1, YESNO	BIT
Not supported	TINYINT	INTEGER1, BYTE	TINYINT
Not supported	COUNTER (See Notes)	AUTOINCREMENT	(See Notes)
Not supported	MONEY	CURRENCY	MONEY
DATE, TIME, TIMESTAMP	DATETIME	DATE, TIME (See Notes)	DATETIME
Not supported	UNIQUEIDENTIFIER	GUID	UNIQUEIDENTIFIER
DECIMAL	DECIMAL	NUMERIC, DEC	DECIMAL
REAL	REAL	SINGLE, FLOAT4, IEEESINGLE	REAL
DOUBLE PRECISION, FLOAT	FLOAT	DOUBLE, FLOAT8, IEEEDOUBLE, NUMBER (See Notes)	FLOAT
SMALLINT	SMALLINT	SHORT, INTEGER2	SMALLINT
INTEGER	INTEGER	LONG, INT, INTEGER4	INTEGER
INTERVAL	Not supported		Not supported
Not supported	IMAGE	LONGBINARY, GENERAL, OLEOBJECT	IMAGE
Not supported	TEXT (See Notes)	LONGTEXT, LONGCHAR, MEMO, NOTE, NTEXT (See Notes)	TEXT
CHARACTER, CHARACTER VARYING, NATIONAL CHARACTER, NATIONAL CHARACTER VARYING	CHAR (See Notes)	TEXT(n), ALPHANUMERIC, CHARACTER, STRING, VARCHAR, CHARACTER VARYING, NCHAR, NATIONAL CHARACTER, NATIONAL CHAR, NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING (See Notes)	CHAR, VARCHAR, NCHAR, NVARCHAR

That is very important information. Now I know the SQL data type for autonumber is counter/AUTOINCREMENT. I created a SQL query in Access and tried it to see whether it works. That is true it worked well. Then I modified my creating database class and tested through internet. Perfect. I made it. Here I list the source code for this class and the testing code. If you like you can download a source code and demo meta database by click the bottom link. To be noticed, the meta database was created under MS Access 2000. So you need use MS Access 2000 to run the demo code. But you can create your meta database in MS Access 97/2000/XP, then you can use this class under these environments.

<%
Option Explicit

Class OFCDB
Public DBName
Public MetaDBName
Public MetaTableName
Private Conn, MetaConn
Private RS
Private SQL
Private TableName(10)
Private NumTables

Private Sub Class_Initialize()
MetaDBName = "OFC.MDB"
DBName = "test.mdb"
MetaTableName = "tblMeta"
End Sub

Private Sub Class_Terminate()
RS.close
MetaConn.Close
Conn.Close
End Sub

' get table names
Private sub GetTableName
dim i
sql = "SELECT distinct myTableName FROM " & MetaTableName & ";"
Set rs= Server.CreateObject("ADODB.Recordset")
rs.open sql, MetaConn, 1, 1
' the array, TableName, is predifined that has 10 elements
' if the actual number of tables greater than 10
' redim the array to accommodate more table name
if rs.recordcount > 10 then redim TableName(rs.recordcount)
i = 1
do while not (rs.eof or rs.bof)
TableName(i)=rs("myTableName")
rs.movenext
i = i + 1
loop
NumTables = i -1
rs.close
set rs=nothing
End Sub

' Create a new database
Public Sub NewDB
Dim appAccess, dbs
Dim strDB
dim i,j

' Initialize string to database path.
strDB = server.mappath(DBNAME)
' Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application.9")
' Open database in Microsoft Access window.
appAccess.NewCurrentDatabase strDB
' Get Database object variable.
Set dbs = appAccess.CurrentDb

' Create tables according to definitions in META DataBese
OpenMetaDB
GetTableName
for i = 1 to NumTables
' Create new table.
sql = "SELECT distinct * FROM " & MetaTableName & " WHERE mytablename= '" & TableName(i) & "';"
Set rs= Server.CreateObject("ADODB.Recordset")
rs.open sql, MetaConn, 1, 1
SQL = "CREATE TABLE " & TableName(i) & " ("
j = 1
do while not (rs.eof or rs.bof)
' create SQL command
if j > 1 then SQL = SQL + ", "
SQL = SQL + rs("myFieldName") + " " + rs("myFieldType") + " "
if rs("myFieldSize")<>0 then SQL = SQL + "(" + cstr(rs("myFieldSize")) +")"
if rs("myPrimarykey") then SQL =SQL + " Primary key "
' move to next record
rs.movenext
j = j + 1
loop
SQL = SQL + ");"
rs.close
set rs=nothing
dbs.Execute SQL
next
CloseMetaDB
' close the new created database
appAccess.CloseCurrentDataBase
set dbs = Nothing
appAccess.Quit
Set appAccess = Nothing
End Sub

Private Sub OpenMetaDB
dim ConnStr
connstr = "DBQ="+server.mappath(MetaDBName)+";defaultdir=;DRIVER={Microsoft Access Driver (*.mdb)};"
set MetaConn=server.createobject("ADODB.Connection")
MetaConn.open connstr
End Sub

Private Sub CloseMetaDB
MetaConn.close
set MetaConn = nothing
End Sub

Public Sub OpenDB
Dim ConnStr
connstr = "DBQ="+server.mappath(DBName)+";defaultdir=;DRIVER={Microsoft Access Driver (*.mdb)};Username=;Password=;"
set conn=server.createobject("ADODB.Connection")
conn.open connstr
End Sub

Public Sub CloseDB
conn.close
set conn = nothing
End Sub

End Class
%>
<html>
<body>
<%
Dim myOFCDB
Set MyOFCDB = New OFCDB
MyOFCDB.DBName = "test1.mdb"
MyOFCDB.NewDB
%>
Test1.mdb has been created successfully in your web server.
</body>
</html>

Download source code

Post comments(留言)

由Google提供