Create an MS Access database by ASP VBScript
I’ve been dreaming of creating MS Access databases through VBScript for ASP server for a long time. But I did not know how to create AutoNumber field. I spent two nights at home to write this vbscrip because I got a bad cold and did not go to office. At the beginning, I tried to use ADO functions to fullfil this goal. I failed. Until the last moment when I almost gave up. I read the SQL help in Access thoroughly and 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. It works. Here I list the source code for this class and the testing code. 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 -dbcreate
10x for the code! cheers!
[...] http://www.sunfinedata.com/tips/create-an-ms-access-database-by-asp-vbscript/ Share and [...]