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

%>