ASP在access库里创建表,及判断表是否存在代码
Option Explicit
Const adSchemaTables = 20
Const ConnString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=data.mdb;"
Const TableNameToCreate = "TableDemo"
Const TableCreateSQL = "Create table TableDemo( id autoincrement(1,1) primary key, NumVal int, TxtVal Text );"
Const TableRemoveSQL = "drop table TableDemo;"
Dim TableCount
Dim Tables
Dim Conn
Set Conn = Server.CreateObject( "ADODB.Connection" )
Conn.Open ConnString
Response.Write "Table1 - " & isTableExists( Conn, "Table1" )
Response.Write "Table2 - " & isTableExists( Conn, "Table2" )
Response.Write "Table3 - " & isTableExists( Conn, "Table3" )
Response.Write ""
If Not isTableExists( Conn, TableNameToCreate ) Then
Conn.Execute( TableCreateSQL )
Response.Write ""
Response.Write "Creating table " & TableNameToCreate & "... Done!"
Response.Write ""
End if
Dim SQL, i
For i = 1 to 5
SQL = "insert into TableDemo( NumVal, TxtVal ) values( "& 1000+i &", '"& "Rec100" & i &"' )"
Conn.Execute( SQL )
Response.Write SQL
Next
Response.Write ""
SQL = "select id, NumVal, TxtVal from TableDemo"
Dim Rs, N, Rows
Set Rs = Server.CreateObject( "ADODB.RecordSet" )
Rs.Open SQL, Conn, 1, 1
N = Rs.RecordCount
if N > 0 Then
Rows = Rs.GetRows()
End if
Rs.Close
Set Rs = Nothing
Response.Write "Query table " & TableNameToCreate & " : " & N & " Record(s)."
Response.Write ""
Dim id
Dim NumVal
Dim TxtVal
If N > 0 Then
Response.Write "================="
Response.Write "id NumVal TxtVal"
Response.Write "--------ā---------"
For i = 0 to N-1
id = Rows( 0, i )
NumVal = Rows( 1, i )
TxtVal = Rows( 2, i )
Response.Write " " & id & " " & NumVal & " " & TxtVal
Next
Response.Write "================="
End if
If isTableExists( Conn, TableNameToCreate ) Then
Conn.Execute( TableRemoveSQL )
Response.Write ""
Response.Write "Removing table " & TableNameToCreate & "... Done!"
Response.Write ""
End if
Conn.Close
Set Conn = Nothing
Function isTableExists( DbConn, TableName )
Dim Rs
Set Rs = DbConn.openSchema( adSchemaTables )
Rs.MoveFirst
Do Until Rs.EOF
If Rs("TABLE_TYPE") = "TABLE" then
If Rs("TABLE_NAME") = TableName Then
isTableExists = True
Rs.Close
Set Rs = Nothing
Exit Function
End if
End if
Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing
isTableExists = False
End Function
|