ACCESS数据库表的创建代码

<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<%
 Dim  TableFields1,TableFields2,TableFields3,TableFields4,TableName,MdbFileName
 TableFields1  ="[id] integer IDENTITY(1,1) PRIMARY KEY NOT NULL ,"
 TableFields1  =TableFields1 & "[gradename] char (255) NOT NULL,"
 TableFields1  =TableFields1 & "[reglist] char (255) NULL ,"
 TableFields1  =TableFields1 & "[rolelist] char (255) NULL ,"
 TableFields1  =TableFields1 & "[roleid] Double ,"
 TableFields1  =TableFields1 & "[allowreg] Double ,"
 TableFields1  =TableFields1 & "[shenhe] Double ,"
 TableFields1  =TableFields1 & "[foldersize] Double ,"
 TableFields1  =TableFields1 & "[regcontent] char (255) NULL ,"
 TableFields1  =TableFields1 & "[FileFlag] char (255) NULL "
 
 TableFields2  ="[ID] integer IDENTITY(1,1) PRIMARY KEY NOT NULL ,"
 TableFields2  =TableFields2 & "[User] char (255) NOT NULL,"
 TableFields2  =TableFields2 & "[Pass] char (255) NULL ,"
 TableFields2  =TableFields2 & "[Question] char (255) NULL ,"
 TableFields2  =TableFields2 & "[Answer] char (255) NULL ,"
 TableFields2  =TableFields2 & "[tm] char (255) NULL ,"
 TableFields2  =TableFields2 & "[Qymc] char (255) NULL ,"
 TableFields2  =TableFields2 & "[Qylb] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[Frdb] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[Zycp] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[Qyjj] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[Country] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[Sf] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[City] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[Post] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[Address] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[Phone] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[Fax] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[mobile] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[Email] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[Web] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[name] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[ch] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[bm] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[zw] char (255) NULL ,"
 TableFields2  =TableFields2 & "[Sortid] Double ,"
 TableFields2  =TableFields2 & "[Typeid] Double ,"
 TableFields2  =TableFields2 & "[typeid_2] Double ,"
 'TableFields2  =TableFields2 & "[Tjbz] char (255) NULL ,"
 TableFields2  =TableFields2 & "[Hybz] Double ,"
 TableFields2  =TableFields2 & "[Flag] Double ,"
 TableFields2  =TableFields2 & "[Cflag] Double ,"
 TableFields2  =TableFields2 & "[Uflag] Double ,"
 'TableFields2  =TableFields2 & "[C_logo] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[cimg] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[cimg_title] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[url] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[isournotify] Double ,"
 'TableFields2  =TableFields2 & "[isothernotify] Double ,"
 TableFields2  =TableFields2 & "[click] Double ,"
 TableFields2  =TableFields2 & "[idate] date ,"
 TableFields2  =TableFields2 & "[BeginDate] date ,"
 TableFields2  =TableFields2 & "[EndDate] date ,"
 TableFields2  =TableFields2 & "[trust_score] Double ,"
 'TableFields2  =TableFields2 & "[zczj] char (255) NULL ,"
 TableFields2  =TableFields2 & "[p_z_Z_LegalStatus] Double ,"
 TableFields2  =TableFields2 & "[p_z_Z_EmployeesCount] Double ,"
 TableFields2  =TableFields2 & "[p_z_Z_AnnualRevenue] Double ,"
 TableFields2  =TableFields2 & "[p_z_Z_RegCapital] Double ,"
 TableFields2  =TableFields2 & "[p_z_Z_AnnualExportAmount] Double ,"
 TableFields2  =TableFields2 & "[p_z_Z_AnnualImportAmount] Double ,"
 'TableFields2  =TableFields2 & "[p_z_Z_RndStaffNum] Double ,"
 'TableFields2  =TableFields2 & "[p_z_Z_ProductionCapacity] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[p_z_Z_OemOdm] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[p_z_Z_QaQc] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[p_z_Z_FoundedPlace] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[p_z_Z_BizPlace] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[p_z_Z_EstablishedYear] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[p_z_Z_BrandName] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[p_z_Z_KeyClients] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[p_z_Z_Certification] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[p_z_Z_Bank] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[p_z_Z_Account] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[p_z_Z_FactorySize] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[html_pass] Double ,"
 'TableFields2  =TableFields2 & "[yx_userskin] Double ,"
 'TableFields2  =TableFields2 & "[weburl_2] Double ,"
 'TableFields2  =TableFields2 & "[Exd] Double ,"
 'TableFields2  =TableFields2 & "[msn] char (255) NULL ,"
 'TableFields2  =TableFields2 & "[foldersize] Double ,"
 'TableFields2  =TableFields2 & "[gradeid] Double ,"
 'TableFields2  =TableFields2 & "[despic] char (255) NULL ,"
 TableFields2  =TableFields2 & "[mark] Double "
 
 TableFields3  ="[id] integer IDENTITY(1,1) PRIMARY KEY NOT NULL ,"
 TableFields3  =TableFields3 & "[gradename] char (255) NOT NULL,"
 TableFields3  =TableFields3 & "[reglist] char (255) NULL ,"
 TableFields3  =TableFields3 & "[rolelist] char (255) NULL ,"
 TableFields3  =TableFields3 & "[roleid] Double ,"
 TableFields3  =TableFields3 & "[allowreg] Double ,"
 TableFields3  =TableFields3 & "[shenhe] Double ,"
 TableFields3  =TableFields3 & "[foldersize] Double ,"
 TableFields3  =TableFields3 & "[regcontent] char (255) NULL ,"
 TableFields3  =TableFields3 & "[FileFlag] char (255) NULL "
 
 TableFields4  ="[id] integer IDENTITY(1,1) PRIMARY KEY NOT NULL ,"
 TableFields4  =TableFields4 & "[gradename] char (255) NOT NULL,"
 TableFields4  =TableFields4 & "[reglist] char (255) NULL ,"
 TableFields4  =TableFields4 & "[rolelist] char (255) NULL ,"
 TableFields4  =TableFields4 & "[roleid] Double ,"
 TableFields4  =TableFields4 & "[allowreg] Double ,"
 TableFields4  =TableFields4 & "[shenhe] Double ,"
 TableFields4  =TableFields4 & "[foldersize] Double ,"
 TableFields4  =TableFields4 & "[regcontent] char (255) NULL ,"
 TableFields4  =TableFields4 & "[FileFlag] char (255) NULL "
 
 TableName1   ="vipgrade"
 TableName2   ="corporation"
' TableName3   ="guestbook"
 'TableName4   ="reply"
 
 MdbFileName ="db/Accessmdb.mdb"
 
Function CreateMDBTable(TableName,MdbFileName,TableFields)
  Dim SQL,RS,Conn,Strcon
  TableFieldsArr = split(TableFields,",")
  TableFieldsCount = Ubound(TableFieldsArr)
  TableArrMax = Cint((TableFieldsCount / 10) +1)
  ReDim TableField(TableArrMax)
  j=0
  k=0
  response.Write TableFieldsCount & TableFieldsArr(0) & ",..." & TableArrMax
  response.Write "<br>"
  'Response.End()
  for i = 1 to TableFieldsCount+1   
          TableField(j) = TableField(j) & ","& TableFieldsArr(i-1)
        if i mod 10 = 0 then
            j = j+1
        end if
        if i mod 11 = 0 then
            k = k + 1
        end if
  next
  for i = 0 to k
      Response.Write Mid(TableField(i),2) & "<br>"& k &"<br>"
  next
  'Response.End()
  Set Conn = Server.CreateObject("ADODB.Connection")
  Strcon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(MdbFileName)
  Conn.open Strcon
  if k = 0 then
  '创建表
  SQL = "CREATE TABLE [" & TableName & "] ("& TableFields & ")"
  response.Write SQL
  Set RS = Conn.Execute(SQL)
  else
  '创建表
  SQL = "CREATE TABLE [" & TableName & "] ("& Mid(TableField(0),2) & ")"
  response.Write SQL & "<br>"
  'Response.End()
  Set RS = Conn.Execute(SQL)
  for i = 1 to k
  SQL = "ALTER TABLE [" & TableName & "] ADD "& Mid(TableField(i),2)
  Response.Write SQL & "<BR>"
  'Set RS = Conn.Execute(SQL)
  next
  end if
  '显示创建成功信息。
  Response.Write ("<br><font color=""#ff0000"">" & TableName & "</font> 表创建成功！")
end Function

Function DropMDBTable(TableName,MdbFileName)
    Dim RS,Conn,Strcon
    Set Conn = Server.CreateObject("ADODB.Connection")
      Strcon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(MdbFileName)
     Conn.open Strcon
    Set RS = Conn.Execute("Drop TABLE [" & TableName & "]")
    RS.close
    Conn.Close
    set rs = nothing
    set conn = nothing
    '显示删除成功信息。
      Response.Write ("<br><font color=""#ff0000"">" & TableName & "</font> 表删除成功！")
End Function

Function getTablname(dbpath)
dim rs
dim Conn
set conn = server.CreateObject("ADODB.Connection")
conn.Open "Dbq=" & Server.MapPath(dbpath) & ";Defaultdir=;Driver={Microsoft Access Driver (*.Mdb)};Driverid=25;Fil=Ms Access;Implicitcommitsync=Yes;Maxbuffersize=512;Maxscanrows=8;Pagetimeout=5; Safetransactions=0;Threads=3;Usercommitsync=Yes;", "Administrator", "adminadmin"
set rs = Conn.OpenSchema(20)

do while not rs.eof
if rs("table_type") ="TABLE" then
getTablname =getTablname & "," & rs("TABLE_NAME")
end if
rs.moveNext
loop
rs.close
conn.Close
set rs = nothing
set conn = nothing
end function

getTablname1 =getTablname(MdbFileName)  '获取数据库表名

if  instr(getTablname1,TableName1) <> 0 then
    'DropMDBTable TableName1,MdbFileName
    'CreateMDBTable TableName1,MdbFileName,TableFields1
else
    'CreateMDBTable TableName1,MdbFileName,TableFields1
end if

if  instr(getTablname1,TableName2) <> 0 then
    DropMDBTable TableName2,MdbFileName
    CreateMDBTable TableName2,MdbFileName,TableFields2
else
    CreateMDBTable TableName2,MdbFileName,TableFields2
end if

'CreateMDBTable TableName2,MdbFileName,TableFields2
'CreateMDBTable TableName3,MdbFileName,TableFields3
'CreateMDBTable TableName4,MdbFileName,TableFields4
%>