MDB数据文件操作

<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<%
'新建数据库
if request("act")="CreateDB" then
PathName=trim(request("PathName"))
DbVersion= trim(request("DbVersion"))
if PathName="" then
response.write("<script>alert('要压缩的数据库的路径不能为空！');history.back(1);</script>")
end if

Call FindPathName(PathName)
Call CreateDB(PathName,DbVersion)
end if

'压缩数据库
if request("act")="CompactDB" then
PathName=trim(request("PathName"))
DbVersion=trim(request("DbVersion"))
if PathName="" then
response.write("<script>alert('要压缩的数据库的路径不能为空！');history.back(1);</script>")
end if
Call FindPathName(PathName)
Call ComPactDB(PathName,DbVersion)
end if

'删除数据库
if request("act")="DeleteDB" then
PathName=trim(request("PathName"))
if PathName="" then
response.write("<script>alert('要压缩的数据库的路径不能为空！');history.back(1);</script>")
end if
Call FindPathName(PathName)
Call DeleteDB(PathName)
end if

' 查询所有数据表名称
if request("act")="FindTableList" then
PathName=trim(request("PathName"))
if PathName="" then
response.write("<script>alert('数据库的路径不能为空！');history.back(1);</script>")
end if
Call FindPathName(PathName)
Call FindTableList(PathName,TableListString)
end if

' 查询数据表名称
if request("act")="FindTableName" then
PathName=trim(request("PathName"))
TableName=cstr(trim(request("TableName")))
TableName1=cstr(trim(request("TableName1")))
if PathName="" then
response.write("<script>alert('数据库的路径不能为空！');history.back(1);</script>")
end if
if TableName="" then
response.write("<script>alert('原数据表名称不能为空！');history.back(1);</script>")
end if
Call FindPathName(PathName)
Call FindTableName2(PathName,TableName)
end if

'更新数据表名称
if request("act")="AlterTableName" then
PathName=trim(request("PathName"))
TableName=cstr(trim(request("TableName")))
TableName1=cstr(trim(request("TableName1")))
if PathName="" then
response.write("<script>alert('数据库的路径不能为空！');history.back(1);</script>")
end if
if TableName="" then
response.write("<script>alert('原数据表名称不能为空！');history.back(1);</script>")
end if
if TableName1="" then
response.write("<script>alert('新数据表名称不能为空！');history.back(1);</script>")
end if
Call FindPathName(PathName)
Call FindTableName(PathName,TableName)
Call FindTableName1(PathName,TableName1)
Call AlterTableName(PathName,TableName,TableName1)

end if

'删除数据表名称
if request("act")="DropTableName" then
PathName=trim(request("PathName"))
TableName=cstr(trim(request("TableName")))
if PathName="" then
response.write("<script>alert('数据库的路径不能为空！');history.back(1);</script>")
end if
if TableName="" then
response.write("<script>alert('要删除的数据表名称不能为空！');history.back(1);</script>")
end if
Call FindPathName(PathName)
Call FindTableName(PathName,TableName)
Call DropTableName(PathName,TableName)
end if

'查询数据表字段
if request("act")="FindTableColumnList" then
PathName=trim(request("PathName"))
TableName=cstr(trim(request("TableName")))
if PathName="" then
response.write("<script>alert('数据库的路径不能为空！');history.back(1);</script>")
end if
if TableName="" then
response.write("<script>alert('要查询的数据表名称不能为空！');history.back(1);</script>")
end if
Call FindPathName(PathName)
Call FindTableName(PathName,TableName)
Call FindTableColumnList(PathName,TableName)
end if



Sub CreateDB(PathName,DbVersion)
on error resume next
select case DbVersion
case "97"
DbVersion1 = "3.51"
case "2000"
DbVersion1 = "4.0"
end select
Set Cat = Server.CreateObject("ADOX.Catalog")
call Cat.Create("Provider=Microsoft.Jet.OLEDB." & DbVersion1 & ";Data Source=" & server.MapPath(PathName))

if err then
response.write("<script>alert('新建Access"&DbVersion&"数据库"&PathName&"时发生错误，错误代码"&err.description&" ,即将返回');history.go(-1);</script>")
Response.end
else
response.write("<script>alert('新建Access"&DbVersion&"数据库"&PathName&"操作成功,系统即将返回');history.go(-1);</script>")
response.End()
end if

End Sub


Sub FindPathName(PathName)
on error resume next
x=0
Set fso = CreateObject("Scripting.FileSystemObject")
if fso.fileexists(server.MapPath(PathName))=true then
x=1
set fso=nothing
if err then
response.write("<script>alert('查找数据库"&PathName&"时发生错误，错误代码"&err.description&" ,即将返回');history.go(-1);</script>")
Response.end
else
if x=0 then
response.write("<script>alert('数据库"&PathName&"不存在,系统即将返回');history.go(-1);</script>")
response.End()
end if
end if
end if
End Sub

'查询指定名称的数据表,找到了继续,找不到返回
Sub FindTableName(PathName,TableName)
strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath(PathName)
set objConn=server.createobject("Adodb.connection")
objConn.open strConn
rem x=0 表示不存在,x=1 表示存在
set rsSchema=objConn.openSchema(20)
rsSchema.movefirst

x=0
Do Until rsSchema.EOF
if rsSchema("TABLE_TYPE")="TABLE" then
if rsSchema("TABLE_NAME")=tablename then
x=1
exit do
end if
end if
rsSchema.movenext
Loop
if x=0 then
response.write("<script>alert('数据表"&tablename&"不经存在,系统即将返回');location.href='AlterDatabase.asp';</script>")
response.End()
end if
set objConn=nothing
End Sub

'查询指定名称的数据表,找到了返回,找不到继续
Sub FindTableName1(PathName,TableName)
strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath(PathName)
set objConn=server.createobject("Adodb.connection")
objConn.open strConn
rem x=0 表示不存在,x=1 表示存在
set rsSchema=objConn.openSchema(20)
rsSchema.movefirst
x=0
Do Until rsSchema.EOF
if rsSchema("TABLE_TYPE")="TABLE" then
if rsSchema("TABLE_NAME")=tablename then
x=1
exit do
end if
end if
rsSchema.movenext
Loop
if x=1 then
response.write("<script>alert('新数据表"&tablename&"已经存在,系统即将返回');location.href='AlterDatabase.asp';</script>")
response.End()
end if
set objConn=nothing
End Sub

'查询指定的数据表
Sub FindTableName2(PathName,TableName)
strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath(PathName)
set objConn=server.createobject("Adodb.connection")
objConn.open strConn
rem x=0 表示不存在,x=1 表示存在
set rsSchema=objConn.openSchema(20)
rsSchema.movefirst
x=0
Do Until rsSchema.EOF
if rsSchema("TABLE_TYPE")="TABLE" then
if rsSchema("TABLE_NAME")=tablename then
x=1
exit do
end if
end if
rsSchema.movenext
Loop
if x=1 then
response.write("<script>alert('数据表"&tablename&"找到了,系统即将返回');location.href='AlterDatabase.asp';</script>")
response.End()
else
response.write("<script>alert('找不到数据表"&tablename&",系统即将返回');location.href='AlterDatabase.asp';</script>")
response.End()
end if
set objConn=nothing
End Sub


'查询指定数据库中的所有数据表
Sub FindTableList(PathName,TableListString)
TableListString=""
strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath(PathName)
set objConn=server.createobject("Adodb.connection")
objConn.open strConn
rem x=0 表示不存在,x=1 表示存在
set rsSchema=objConn.openSchema(20)
rsSchema.movefirst
x=0
Do Until rsSchema.EOF
if rsSchema("TABLE_TYPE")="TABLE" then
x=x+1
TableListString=TableListString&rsSchema("TABLE_NAME")&";"
end if
rsSchema.movenext
Loop
if x>0 then
response.write("<script>alert('找到了"&x&"个用户定义的数据表"&TableListString&"!,系统即将返回');location.href='AlterDatabase.asp';</script>")
response.End()
else
response.write("<script>alert('找不到任何用户定义的数据表!系统即将返回');location.href='AlterDatabase.asp';</script>")
response.End()
end if
set objConn=nothing
End Sub
'删除数据库
Sub DeleteDB(PathName)
on error resume next
x=0
Set fso = CreateObject("Scripting.FileSystemObject")
if fso.fileexists(server.MapPath(PathName))=true then
fso.deletefile(server.MapPath(PathName))
set fso=nothing
if err then
response.write("<script>alert('删除数据库"&PathName&"时发生错误，错误代码"&err.description&" ,即将返回');history.go(-1);</script>")
Response.end
else
response.write("<script>alert('删除数据库"&PathName&"成功,系统即将返回');history.go(-1);</script>")
response.End()
end if
end if
End Sub
'修改数据表名称
Sub AlterTableName(PathName,TableName,TableName1)
'Dim MyTable,MyField ,pro
On Error resume next
strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath(PathName)
set Conn=server.createobject("Adodb.connection")
Conn.open strConn
set mydb=server.createobject("adox.catalog")
set mytable=server.createobject("adox.table")
set myfield =server.createobject("adox.column")
MyDB.ActiveConnection =Conn
For Each MyTable In MyDB.Tables
if MyTable.Name=TableName then
MyTable.Name=TableName1
exit for
end if
Next
conn.close
set Conn=nothing
if err then
response.write("<script>alert('原数据表名称"&tablename&"修改成新数据表名称"&tablename1&"时发生错误，错误代码"&err.description&" ,即将返回');history.go(-1);</script>")
Response.end
else
response.write("<script>alert('原数据表名称"&tablename&"修改成新数据表名称"&tablename1&"成功!,系统即将返回');location.href='AlterDatabase.asp';</script>")
Response.end
end if
End Sub
'删除数据表名称
Sub DropTableName(PathName,TableName)
'Dim MyTable,MyField ,pro
On Error resume next
strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath(PathName)
set Conn=server.createobject("Adodb.connection")
Conn.open strConn
set mydb=server.createobject("adox.catalog")
set mytable=server.createobject("adox.table")
set myfield =server.createobject("adox.column")
MyDB.ActiveConnection =Conn
For Each MyTable In MyDB.Tables
if MyTable.Name=TableName then
MyDB.Tables.delete(TableName)
exit for
end if
Next
conn.close
set Conn=nothing
if err then
response.write("<script>alert('删除数据表"&tablename&"时发生错误，错误代码"&err.description&" ,即将返回');history.go(-1);</script>")
Response.end
else
response.write("<script>alert('删除数据表"&tablename&"成功!,系统即将返回');location.href='AlterDatabase.asp';</script>")
Response.end
end if
End Sub



Sub CompactDB(PathName, DbVersion)
on error resume next
strPathName = left(server.MapPath(PathName),instrrev(server.Mappath(PathName),"/"))
Set fso = CreateObject("Scripting.FileSystemObject")
Set Engine = CreateObject("JRO.JetEngine")
If DbVersion = 97 Then
Engine.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath(PathName), _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPathName & "temp.mdb;" _
& "Jet OLEDB:Engine Type=" & 4
Else
Engine.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath(PathName), _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPathName & "temp.mdb"
End If
fso.CopyFile strPathName & "temp.mdb",server.MapPath(PathName)
fso.DeleteFile(strPathName & "temp.mdb")
Set fso = nothing
Set Engine = nothing
if err then
response.write("<script>alert('压缩数据库"&server.MapPath(PathName)&"时发生错误，错误代码"&err.description&" ,即将返回');history.go(-1);</script>")
Response.end
else
response.write("<script>alert('压缩数据库"&server.MapPath(PathName)&"成功!,系统即将返回');location.href='AlterDatabase.asp';</script>")
Response.end
end if
end sub

Sub FindTableColumnList(PathName,TableName)
ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath(PathName)
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.open ConnStr
Const adSchemaTables = 20
adSchemaColumns = 4
Set rstSchema = oConn.OpenSchema(adSchemaColumns)
response.write "<table border=1 align=center>"
response.write "<tr><td>Table name</td><td>field name</td><td>field type</td><td>is nullable</td><td>field size</td></tr>"
'tablename=""
Do Until rstSchema.EOF
if rstSchema("Table_name") =tablename then
response.write "<tr><td>"
response.write rstSchema("Table_name")
response.write "</td><td>"& rstschema("column_Name") & "</td><td>"
select case rstschema("data_type")
case 130
if rstschema("CHARACTER_MAXIMUM_LENGTH") = 1073741823 then
response.write "Memo"
else
response.write "Text"
end if
case 135
response.write "Date/Time"
case 3
response.write "Long Integer"
case 11
response.write "Yes/No"
case 131
response.write "Currency"
case else
response.write rstschema("data_type")
end select
response.write "</td><td>" & rstschema("is_nullable") & "</td><td>"
if rstschema("CHARACTER_MAXIMUM_LENGTH") <> 1073741823 then
response.write rstschema("CHARACTER_MAXIMUM_LENGTH")
else
response.write "&nbsp;"
end if
response.write "</td></tr>"
end if
rstSchema.MoveNext
Loop
response.write "</table>"
response.Write("<div align=center><a href='javascript:history.go(-1);' >点这里返回</a></div>")
response.End()
end sub

%>

<br>
<table width="739" border="0" align="center" cellpadding="0" cellspacing="0">
<form action="?act=CreateDB" method="post" >
<tr>
<td height="35" colspan="2"><div align="center"><font size="4"><strong><font color="#FF0000">新建数据库（ASO+ADOX）</font></strong></font></div></td>
</tr>
<tr>
<td width="153" height="35">数据库库路径:</td>
<td width="586" height="35"><input name="PathName" type="text" id="PathName"> &nbsp;</td>
</tr>
<tr>
<td height="35">Access数据库格式</td>
<td height="35"><input name="DbVersion" type="radio" value="2000" checked>
Access 2000&nbsp; <input name="DbVersion" type="radio" value="97">
Access 97</td>
</tr>
<tr align="center">
<td height="35" colspan="2"><input name="submit1" type="submit" id="submit1" value="新建数据库">
&nbsp;</td>
</tr>
</form>
</table>
<br>
<table width="739" border="0" align="center" cellpadding="0" cellspacing="0">
<form action="?act=DeleteDB" method="post" >
<tr>
<td height="35" colspan="2"><div align="center"><font size="4"><strong><font color="#FF0000">删除数据库（FSO）</font></strong></font></div></td>
</tr>
<tr>
<td width="153" height="35">数据库库路径:</td>
<td width="586" height="35"><input name="PathName" type="text" id="PathName"> &nbsp;</td>
</tr>
<tr align="center">
<td height="35" colspan="2"><input name="submit1" type="submit" id="submit1" value="删除数据库">
&nbsp;</td>
</tr>
</form>
</table>
<br>
<table width="739" border="0" align="center" cellpadding="0" cellspacing="0">
<form action="?act=CompactDB" method="post" >
<tr>
<td height="35" colspan="2"><div align="center"><font size="4"><strong><font color="#FF0000">压缩数据库（ASO+JRO）</font></strong></font></div></td>
</tr>
<tr>
<td width="153" height="35">数据库库路径:</td>
<td width="586" height="35"><input name="PathName" type="text" id="PathName"> &nbsp;</td>
</tr>
<tr>
<td height="35">Access数据库格式</td>
<td height="35"><input name="DbVersion" type="radio" value="2000" checked>
Access 2000&nbsp; <input name="DbVersion" type="radio" value="97">
Access 97</td>
</tr>
<tr align="center">
<td height="35" colspan="2"><input name="submit1" type="submit" id="submit1" value="开始压缩数据库">
&nbsp;</td>
</tr>
</form>
</table>
<br>
<table width="739" border="0" align="center" cellpadding="0" cellspacing="0">
<form action="?act=FindTableList" method="post" >
<tr>
<td height="35" colspan="2"><div align="center"><font size="4"><strong><font color="#FF0000">查询所有数据库中的所有表（FSO+ADOX）</font></strong></font></div></td>
</tr>
<tr>
<td width="153" height="35">数据库库路径:</td>
<td width="586" height="35"><input name="PathName" type="text" id="PathName"> &nbsp;</td>
</tr>
<tr align="center">
<td height="35" colspan="2"><input name="submit1" type="submit" id="submit1" value="查询所有数据库中的所有表">
&nbsp;</td>
</tr>
</form>
</table>
<br>
<table width="739" border="0" align="center" cellpadding="0" cellspacing="0">
<form action="?act=FindTableName" method="post" >
<tr>
<td height="30" colspan="2"><div align="center"><strong><font color="#FF0000">查询指定数据表名称（AODX）</font></strong></div></td>
</tr>
<tr>
<td width="154" height="23">数据库路径:</td>
<td ><input name="PathName" type="text" id="PathName">
( 相对路径 )</td>
</tr>
<tr>
<td height="23">数据表名称:</td>
<td ><input name="TableName" type="text" id="TableName" size="15" maxlength="255"> </td>
</tr>

<tr align="center">
<td colspan="2"><input type="submit" name="Submit" value="查询指定数据表名称">
&nbsp;&nbsp; </td>
</tr>
</form>
</table>
<br>
<table width="739" border="0" align="center" cellpadding="0" cellspacing="0">
<form action="?act=AlterTableName" method="post" >
<tr>
<td height="30" colspan="2"><div align="center"><strong><font color="#FF0000">修改数据库表名称（AODX）</font></strong></div></td>
</tr>
<tr>
<td width="154" height="23">数据库路径:</td>
<td ><input name="PathName" type="text" id="PathName">
( 相对路径 )</td>
</tr>
<tr>
<td height="23">原数据表名称:</td>
<td ><input name="TableName" type="text" id="TableName" size="15" maxlength="255"> </td>
</tr>
<tr align="center">
<td height="25" align="left" ><p>新数据表名称:</p>
</td>
<td width="585" height="25" align="left"><input name="TableName1" type="text" id="TableName1" value="" size="15" maxlength="255"></td>
</tr>
<tr align="center">
<td colspan="2"><input type="submit" name="Submit" value="修改数据库表名称">
&nbsp;&nbsp; </td>
</tr>
</form>
</table>
<br>
<table width="739" border="0" align="center" cellpadding="0" cellspacing="0">
<form action="?act=FindTableColumnList" method="post" >
<tr>
<td height="30" colspan="2"><div align="center"><strong><font color="#FF0000">查询指定数据表所有字段（AODX）</font></strong></div></td>
</tr>
<tr>
<td width="154" height="23">数据库路径:</td>
<td ><input name="PathName" type="text" id="PathName">
( 相对路径 )</td>
</tr>
<tr>
<td height="23">数据表名称:</td>
<td ><input name="TableName" type="text" id="TableName" size="15" maxlength="255"> </td>
</tr>

<tr align="center">
<td colspan="2"><input type="submit" name="Submit" value="查询指定数据表所有字段">
&nbsp;&nbsp; </td>
</tr>
</form>
</table>