座右铭:
只做有益人类的事
不做有害人类的事
|
|
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
|
|
