最新MDB数据库操作大全（自己搜集整理基本上很全了）

<%
if request("act")="FindMdbFileList" then
RootPath=trim(request("RootPath"))
FileName=trim(request("FileName"))
TruePath=trim(request("TruePath"))
FindType=trim(request("FindType"))
if RootPath="" then
response.write("<script>alert('数据库所在的路径不能为空！');history.back(-1);</script>")
response.End()
else
if TruePath=0 then
FolderPath=server.MapPath(RootPath)
else
FolderPath=RootPath
end if
set fso=server.CreateObject("Scripting.FileSystemObject")
if fso.folderexists(FolderPath)=false then
response.write("<script>alert('数据库路径错误，找不到该文件夹！');history.back(-1);</script>")
response.End()
end if
set fso=nothing
if FileName="" then
response.write("<script>alert('数据库名称不能为空！');history.back(-1);</script>")
response.End()
end if
call FindMdbFileList(FolderPath,FileName,FindType)
end if
end if

if request("act")="ChangPathName" then
Session("PathName")=trim(request("PathName"))
response.Write("最新的数据库路径是："&Session("PathName"))
Session("TableName")=""
Session("ColumnName")=""
Session("ColumnProperty")=""
response.Redirect("TableManage.asp")
response.End()
end if
if request("act")="ChangTableName" then
Session("TableName")=trim(request("TableName"))
Session("ColumnName")=""
Session("ColumnProperty")=""
response.Redirect("TableManage.asp")
response.End()
end if
if request("act")="ChangColumnName" then
Session("ColumnName")=trim(request("ColumnName"))
Session("ColumnProperty")=""
response.Redirect("TableManage.asp")
response.End()
end if

'复制数据表结构

if request("act")="CopyTableConstruct" then
PathName=trim(request("PathName"))
PathName1=trim(request("PathName1"))
TableName=cstr(trim(request("TableName")))
TableName1=cstr(trim(request("TableName1")))
Call FindPathName(PathName)
Call FindTableName(PathName,TableName)
Call CopyTableConstruct(PathName,PathName1,TableName,TableName1)
end if
'复制整个数据表
if request("act")="CopyTableName" then
PathName=trim(request("PathName"))
PathName1=trim(request("PathName1"))
TableName=cstr(trim(request("TableName")))
TableName1=cstr(trim(request("TableName1")))
Call FindPathName(PathName)
Call FindTableName(PathName,TableName)
Call CopyTableName(PathName,PathName1,TableName,TableName1)
end if

'删除整个数据表的数据内容
if request("act")="DropTableContent" then
PathName=trim(request("PathName"))
TableName=cstr(trim(request("TableName")))
Call FindPathName(PathName)
Call FindTableName(PathName,TableName)
Call DropTableContent(PathName,TableName)
end if

'删除整个数据表结构
if request("act")="DropTableName" then
PathName=trim(request("PathName"))
TableName=cstr(trim(request("TableName")))
Call FindPathName(PathName)
Call FindTableName(PathName,TableName)
Call DropTableName(PathName,TableName)
end if

'更新整个字段常用属性
if request("act")="AlterTableColumn" then
' Dim PathName,TableName,RsName,RsType,RsNull,RsDefault,RsPrimary
PathName=trim(request("PathName"))
TableName=cstr(trim(request("TableName")))
ColumnName=cstr(trim(request("ColumnName")))
ColumnType=cstr(trim(request("RsType")))
ColumnLength=cstr(trim(request("RsLength")))
ColumnDefault=cstr(trim(request("RsDefault")))
ColumnDescription=cstr(trim(request("RsDescription")))
ColumnNullable=cstr(trim(request("RsNullable")))
ColumnValidRule=cstr(trim(request("RsValidRule")))
ColumnValidText=cstr(trim(request("RsValidText")))
ColumnZeroLength=cstr(trim(request("RsZeroLength")))
ColumnUnicode=cstr(trim(request("RsUnicode")))
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 ColumnName="" then
response.write("<script>alert('字段名称不能为空！');history.back(1);</script>")
end if
if ColumnType="" then
response.write("<script>alert('字段类别不能为空！');history.back(1);</script>")
end if
response.write "<br>提交的数据"
response.write "<br>ColumnType:"&ColumnType
response.write "<br>ColumnLength:"&ColumnLength
response.write "<br>ColumnDefault:"&ColumnDefault
response.write "<br>ColumnDescription:"&ColumnDescription
response.write "<br>ColumnNullable:"&ColumnNullable
response.write "<br>ColumnValidRule:"&ColumnValidRule
response.write "<br>ColumnValidText:"&ColumnValidText
response.write "<br>ColumnZeroLength:"&ColumnZeroLength
response.write "<br>ColumnUnicode:"&ColumnUnicode

Call FindTableName(PathName,TableName)
Call FindTableColumn(PathName,TableName,ColumnName)
Call AlterTableColumn(PathName,TableName,ColumnName,ColumnType,ColumnLength,ColumnDefault,ColumnDescription,ColumnNullable,ColumnValidRule,ColumnValidText,ColumnZeroLength,ColumnUnicode)
end if

Sub FindPathName(PathName)
on error resume next
x=0
Set fso = CreateObject("Scripting.FileSystemObject")
if fso.fileexists(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=" &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&"不经存在,系统即将返回');history.go(-1);</script>")
response.End()
end if
set objConn=nothing
End Sub

Sub CopyTableConstruct(PathName,PathName1,TableName,TableName1)

On Error resume next
if PathName1 <>"" then
strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & PathName1
set Conn=server.createobject("Adodb.connection")
Conn.open strConn
sql="SELECT * INTO " & TableName1 & " IN """&PathName1&""" from "&TableName&" WHERE 1 = 0"
Conn.execute(sql)
Conn.Close
set Conn=nothing
else
strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & PathName
set Conn=server.createobject("Adodb.connection")
Conn.open strConn
sql="SELECT * INTO ["&TableName1&"] FROM ["&TableName&"] WHERE 1 = 0"
Conn.execute(sql)
Conn.Close
set Conn=nothing
end if

if err then
response.write("<br>复制数据表"&tablename&"表结构到新数据表 "&TableName1&"时发生错误！错误代码<font color=#ff0000>"&Err.Description&"</fonct>")
response.Write("<br>sql语句：<font color=#ff0000>"&sql&"</font>")
response.Write("<br><div><a href='javascript:history.go(-1);'>点这里返回</a></div>")
Response.end
else
response.write("<script>alert('复制数据表"&tablename&"表结构到新数据表 "&TableName1&"成功 ,系统即将返回');location.href='TableManage.asp';</script>")
Response.end
end if
End Sub

'复制数据表全部内容
Sub CopyTableName(PathName,TableName,TableName1)
On Error resume next
if PathName1 <>"" then
strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & PathName1
set Conn=server.createobject("Adodb.connection")
Conn.open strConn
sql="SELECT * INTO " & TableName1 & " IN """&PathName1&""" from "&TableName
Conn.execute(sql)
Conn.Close
set Conn=nothing
else
strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & PathName
set Conn=server.createobject("Adodb.connection")
Conn.open strConn
sql="SELECT * INTO ["&TableName1&"] FROM ["&TableName&"] "
Conn.execute(sql)
Conn.Close
set Conn=nothing
end if

if err then
response.write("<br>复制数据表"&tablename&"全部内容到新数据表 "&TableName1&"时发生错误！错误代码<font color=#ff0000>"&Err.Description&"</fonct>")
response.Write("<br>sql语句：<font color=#ff0000>"&sql&"</font>")
response.Write("<br><div><a href='javascript:history.go(-1);'>点这里返回</a></div>")
Response.end
else
response.write("<script>alert('复制数据表"&tablename&"全部内容到新数据表 "&TableName1&"成功 ,系统即将返回');location.href='TableManage.asp';</script>")
Response.end
end if
End Sub

'删除整个数据表数据内容
Sub DropTableContent(PathName,TableName)
strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & PathName
set Conn=server.createobject("Adodb.connection")
Conn.open strConn
On Error resume next

'读取自动编号字段名称
Res=0
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
For Each MyField In MyTable.Columns
if MyField.Properties("AutoIncrement").Value=true then
ColumnName=MyField.Name
Res=1
exit for
end if
next
exit for
end if
next

If Res=1 then
sql="Alter Table "&TableName&" Drop Column "&ColumnName
Conn.execute(sql)
if err then
response.write("<br>删除数据表"&tablename&"的自动编号字段时发生错误！错误代码<font color=#ff0000>"&Err.Description&"</fonct>")
response.Write("<br>sql语句：<font color=#ff0000>"&sql&"</font>")
response.Write("<br><div><a href='javascript:history.go(-1);'>点这里返回</a></div>")
err.clear
Response.end
end if
'添加自动编号字段名称
sql="Alter Table "&TableName&" Add Column "&ColumnName&" AutoIncrement"
Conn.execute(sql)
if err then
response.write("<br>添加数据据表"&tablename&"的自动编号字段"&ColumnName&"时发生错误！错误代码<font color=#ff0000>"&Err.Description&"</fonct>")
response.Write("<br>sql语句：<font color=#ff0000>"&sql&"</font>")
response.Write("<br><div><a href='javascript:history.go(-1);'>点这里返回</a></div>")
Response.end
end if

sql="delete * FROM ["&TableName&"] "
Conn.execute(sql)
if err then
response.write("<br>删除整个数据表"&tablename&"的全部内容时发生错误！错误代码<font color=#ff0000>"&Err.Description&"</fonct>")
response.Write("<br>sql语句：<font color=#ff0000>"&sql&"</font>")
response.Write("<br><div><a href='javascript:history.go(-1);'>点这里返回</a></div>")
err.clear
Response.end
end if
else
sql="delete * FROM ["&TableName&"] "
Conn.execute(sql)
if err then
response.write("<br>删除整个数据表"&tablename&"的全部内容时发生错误！错误代码<font color=#ff0000>"&Err.Description&"</fonct>")
response.Write("<br>sql语句：<font color=#ff0000>"&sql&"</font>")
response.Write("<br><div><a href='javascript:history.go(-1);'>点这里返回</a></div>")
err.clear
Response.end
end if
end if
response.write("<script>alert('整个数据表"&tablename&"的全部内容删除成功 ,系统即将返回');location.href='TableManage.asp';</script>")
Response.end
Conn.Close
set Conn=nothing
End Sub

Sub DropTableName(PathName,TableName)
strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &PathName
set objConn=server.createobject("Adodb.connection")
objConn.open strConn
sql="Drop Table "&TableName
objConn.execute(sql)
if err then
response.write("<br>删除数据表"&tablename&"时发生错误！错误代码<font color=#ff0000>"&Err.Description&"</fonct>")
response.Write("<br>sql语句：<font color=#ff0000>"&sql&"</font>")
response.Write("<br><div><a href='javascript:history.go(-1);'>点这里返回</a></div>")
Response.end
else
response.write("<script>alert('数据表"&tablename&"删除成功,系统即将返回');location.href='TableManage.asp';</script>")
response.End()
end if
set objConn=nothing
End Sub

Sub FindTableColumn(PathName,TableName,ColumnName)
x=0
strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & PathName
set objConn=server.createobject("Adodb.connection")
objConn.open strConn
On Error resume next
Set primary = objConn.OpenSchema(adSchemaPrimaryKeys, Array(empty, empty, TableName))
sql1="select * from ["&TableName&"] "
set rs=objConn.execute(sql1)
for i=0 to rs.fields.count-1
if rs(i).name=ColumnName then
x=1
end if
next
set rs=nothing
set objConn=nothing
if x=0 then
response.write("<script>alert('数据表"&tablename&"表中字段 "&ColumnName&" 不存在');history.go(-1);</script>")
response.End()
end if
End Sub

Sub FindMdbFileList(PathName,FileName,FindType)
'findtype=1 查询整个站点下的所有数据库文件
'findtype=2 查询当前目录下以及所有子目录中的数据库文件
'findtype=3 查询站点外部的数据库文件

set FsoObject=server.CreateObject("scripting.FileSystemObject")
folderpath=PathName
FindType=1
select case FindType
case 1

Set sfolder= FsoObject.GetFolder(folderpath)
x=0
Session("MdbFileList")=""
set Files=sfolder.Files
for each FileItem in Files
fname=PathName&"/"& FileItem.Name
if instr(fname,FileName)>0 then
x=x+1
if x=1 then
if Session("PathName")="" then
Session("PathName")=fname
end if
end if
Session("MdbFileList")=Session("MdbFileList")&fname&";"
end if
next
Set sfolder=nothing

set sfolder=FsoObject.GetFolder(folderpath).SubFolders
for each FolderItem in sfolder
set Files=FolderItem.Files
for each FileItem in Files
fname=PathName&"/"& FolderItem.Name & "/" & FileItem.Name
if instr(fname,FileName)>0 then
x=x+1
if x=1 then
if Session("PathName")="" then
Session("PathName")=fname
end if
end if
Session("MdbFileList")=Session("MdbFileList")&fname&";"
end if
next
next

Session("MdbFileTotal")=x
response.Redirect("TableManage.asp")
case 2

x=0
sfolder=FsoObject.GetFolder(folderpath)
for each FileItem in sfolder
fname=PathName&"/"& FileItem.Name
if instr(fname,FileName)>0 then
x=x+1
response.Write("在文件夹"&PathName&"下找到第"&x&"个数据库文件:"&fname&"<br>")
end if
next
set sfolder=nothing

set sfolder=FsoObject.GetFolder(folderpath).SubFolders
for each FolderItem in sfolder
set Files=FolderItem.Files
for each FileItem in Files
fname=PathName&"/"& FolderItem.Name & "/" & FileItem.Name
if instr(fname,FileName)>0 then
x=x+1
response.Write("在文件夹"&folderpath&"下找到第"&x&"个数据库文件:"&fname&"<br>")
response.Write("<a href='TableManage.asp'>点这里返回</a>")
end if
next
next
case 3

end select
end sub

Sub AlterTableColumn(PathName,TableName,ColumnName,ColumnType,ColumnLength,ColumnDefault,ColumnDescription,ColumnNullable,ColumnValidRule,ColumnValidText,ColumnZeroLength,ColumnUnicode)
'Dim MyTable,MyField ,pro
On Error resume next
strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & 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

For Each MyField In MyTable.Columns
if MyField.Name=ColumnName Then
Res=1
if MyField.Properties("Default").Value<>ColumnDefault then
MyField.Properties("Default").Value=ColumnDefault
end if
if MyField.Properties("Description").Value<>ColumnDescription then
MyField.Properties("Description").Value=ColumnDescription
end if
on error resume next
if MyField.Properties("Jet OLEDB:Column Validation Rule").Value<>ColumnValidRule then
MyField.Properties("Jet OLEDB:Column Validation Rule").Value=ColumnValidRule
end if
on error resume next
if MyField.Properties("Jet OLEDB:Column Validation Text").Value<>ColumnValidText then
MyField.Properties("Jet OLEDB:Column Validation Text").Value=ColumnValidText
end if
on error resume next
if MyField.Properties("Nullable").Value<>ColumnNullable then
MyField.Properties("Nullable").Value=ColumnNullable
end if
on error resume next
if MyField.Properties("Jet OLEDB:Allow Zero Length").Value<>ColumnZeroLength then
MyField.Properties("Jet OLEDB:Allow Zero Length").Value=ColumnZeroLength
end if
on error resume next
if MyField.Properties("Jet OLEDB:Compressed UNICODE Strings").Value<>ColumnUnicode then
MyField.Properties("Jet OLEDB:Compressed UNICODE Strings").Value=ColumnUnicode
end if
exit for
end if
Next
if Res=1 then exit for
end if
if Res=1 then exit for
Next
if ColumnNullable=True then
ColumnNullable=" Null "
else
ColumnNullable=" Not Null "
end if

sql="Alter Table "&TableName&" Alter Column "

select case ColumnType
case "AutoIncrement"
sql=sql&ColumnName&" AutoIncrement "&ColumnNullable

case "varchar"
if ColumnLength="" then
sql=sql&ColumnName&" varchar(50) "&ColumnNullable
else
sql=sql&ColumnName&" varchar("&cint(ColumnLength)&") "&ColumnNullable
end if
if ColumnDefault<>"" then
sql=sql&" default "&ColumnDefault
else
sql=sql
end if

case "memo"
if ColumnDefault<>"" then
sql=sql&ColumnName&" memo "&" default "&ColumnDefault
else
sql=sql&ColumnName&" memo "&ColumnNullable
end if

case "integer"
if ColumnLength="" then
sql=sql&ColumnName&" integer "&ColumnNullable
else
sql=sql&ColumnName&" integer("&ColumnLength&") "&ColumnNullable
end if
if ColumnDefault<>"" then
sql=sql&" default "&ColumnDefault
else
sql=sql
end if

case "number"
if ColumnLength="" then
sql=sql&ColumnName&" number "&ColumnNullable
else
sql=sql&ColumnName&" number("&ColumnLength&") "&ColumnNullable
end if
if ColumnDefault<>"" then
sql=sql&" default "&ColumnDefault
else
sql=sql
end if

case "short"
if ColumnLength="" then
sql=sql&ColumnName&" short "&ColumnNullable
else
sql=sql&ColumnName&" short("&ColumnLength&") "&ColumnNullable
end if
if ColumnDefault<>"" then
sql=sql&" default "&ColumnDefault
else
sql=sql
end if
case "long"
if ColumnLength="" then
sql=sql&ColumnName&" long "&ColumnNullable
else
sql=sql&ColumnName&" long("&ColumnLength&") "&ColumnNullable
end if
if ColumnDefault<>"" then
sql=sql&" default "&ColumnDefault
else
sql=sql
end if

case "double"
if ColumnLength="" then
sql=sql&ColumnName&" double "&ColumnNullable
else
sql=sql&ColumnName&" double("&ColumnLength&") "&ColumnNullable
end if
if ColumnDefault<>"" then
sql=sql&" default "&ColumnDefault
else
sql=sql
end if

case "real"
if ColumnLength="" then
sql=sql&ColumnName&" real "&ColumnNullable
else
sql=sql&ColumnName&" real("&ColumnLength&") "&ColumnNullable
end if
if ColumnDefault<>"" then
sql=sql&" default "&ColumnDefault
else
sql=sql
end if

case "numeric"
if ColumnLength="" then
sql=sql&ColumnName&" numeric "&ColumnNullable
else
sql=sql&ColumnName&" numeric("&ColumnLength&") "&ColumnNullable
end if
if ColumnDefault<>"" then
sql=sql&" default "&ColumnDefault
else
sql=sql
end if

case "byte"
if ColumnLength="" then
sql=sql&ColumnName&" byte "&ColumnNullable
else
sql=sql&ColumnName&" byte("&ColumnLength&") "&ColumnNullable
end if
if ColumnDefault<>"" then
sql=sql&" default "&ColumnDefault
else
sql=sql
end if

case "datetime" 'Access97支持
if ColumnDefault="" then
sql=sql&ColumnName&" datetime "&ColumnNullable
else
sql=sql&ColumnName&" datetime "&ColumnNullable&" default "&ColumnDefault
end if

case "date"
if ColumnDefault="" then
sql=sql&ColumnName&" date "&ColumnNullable
else
sql=sql&ColumnName&" date "&ColumnNullable&" default "&ColumnDefault
end if

case "time"
if ColumnDefault="" then
sql=sql&ColumnName&" time "&ColumnNullable
else
sql=sql&ColumnName&" time "&ColumnNullable&" default "&ColumnDefault
end if
case "yesno"
if ColumnDefault="" then
sql=sql&ColumnName&" yesno "&ColumnNullable
else
sql=sql&ColumnName&" yesno "&ColumnNullable&" default "&ColumnDefault
end if

case "currency"
if ColumnLength="" then
sql=sql&ColumnName&" currency "&ColumnNullable
else
sql=sql&ColumnName&" currency("&ColumnLength&") "&ColumnNullable
end if

if ColumnDefault<>"" then
sql=sql&" default "&ColumnDefault
else
sql=sql
end if

case "hyperlink"
if ColumnDefault="" then
sql=sql&ColumnName&" OleObject "&ColumnNullable
else
sql=sql&ColumnName&" OleObject "&ColumnNullable&" default "&ColumnDefault
end if
case "OleObject"
if ColumnDefault="" then
sql=sql&ColumnName&" OleObject "&ColumnNullable
else
sql=sql&ColumnName&" OleObject "&ColumnNullable&" default "&ColumnDefault
end if
case else
response.write("<script>alert('数据类别"&ColumnType&"不可以识别或者暂时未完善此类别数据类型的建表功能,即将返回');location.href='TableManage.asp';</script>")
response.End()
end select

conn.execute(sql)
conn.close
set Conn=nothing
response.write("<script>alert('数据表"&tablename&"表中字段 "&ColumnName&" 修改常用属性完成 ,系统即将返回');location.href='TableManage.asp';</script>")
Response.end

End Sub
%>
<html>
<head>
<title>数据库管理系统之字段管理</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
</head>

<body>
<%if Session("PathName")="" then%>
<table width="739" border="0" align="center" cellpadding="0" cellspacing="0">
<form action="?act=FindMdbFileList" method="post" >
<tr>
<td height="30" colspan="3"><div align="center"><strong><font color="#FF0000">查询数据库中的表</font></strong></div></td>
</tr>
<tr>
<td width="106" height="25" >根目录路径</td>
<td width="633" colspan="2"><input name="RootPath" type="text" id="RootPath" value="<%=server.MapPath("/")%>">
</td>
</tr>
<tr>
<td height="25" >数据库后缀</td>
<td colspan="2"><input name="FileName" type="text" id="FileName" value=".mdb">
</td>
</tr>
<tr>
<td height="25" >路径属性</td>
<td colspan="2"><input name="TruePath" type="radio" value="0">
相对路径
<input name="TruePath" type="radio" value="1" checked>
绝对路径 </td>
</tr>
<tr>
<td height="25" >查找方式</td>
<td colspan="2"> <input name="FindType" type="radio" value="1" checked>
查根整个站点
<input type="radio" name="FindType" value="2">
查找当前文件夹
<input name="FindType" type="radio" value="3" checked>
查找站点外</td>
</tr>
<tr align="center">
<td colspan="3"><input type="submit" name="Submit" value="查找根目录下数据库文件">
&nbsp;&nbsp; </td>
</tr>
</form>
</table>
<%end if%>
<br>
<%if Session("PathName")<>"" then%>
<table width="739" border="0" align="center" cellpadding="0" cellspacing="0">
<form id="Form1" name="Form1"method="post" action="TableManage.asp?act=AlterTableColumn" >
<tr>
<td height="31" colspan="2"><div align="center"><strong><font color="#FF0000">设置数据库表中字段的常用属性（AODX）</font></strong></div></td>
</tr>

<tr>
<td width="154" height="23"><strong><font color="#FF0000">*</font></strong>数据库路径:</td>
<td > <select name="PathName" id="PathName" onChange="ChangePathName(document.Form1.PathName.options[document.Form1.PathName.selectedIndex].value)">
<%if Session("PathName")="" then%>
<option value="" selected>请查询数据库文件</option>
<%else%>
<option value="<%=Session("PathName")%>" selected><%=Session("PathName")%></option>
<%end if%>
<%
PathNameList=split(Session("MdbFileList"),";",-1,1)
for i=0 to ubound(PathNameList)-1
%>
<option value="<%=PathNameList(i)%>" ><%=PathNameList(i)%></option>
<%
next
%>
</select> <script language="JavaScript">
function ChangePathName(locationPath)
{
var locationPath=locationPath;
location.href='TableManage.asp?act=ChangPathName&PathName='+locationPath;
}
</script> </td>
</tr>
<tr>
<td height="23"><strong><font color="#FF0000">*</font></strong>数据表名称:</td>
<td > <%
PathName=session("PathName")
Call FindTableList(PathName,TableListString)
'查询指定数据库中的所有数据表
Sub FindTableList(PathName,TableListString)
TableListString=""
Session("TableNameTotal")=0
Session("TableNameList")=""
strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & PathName
set objConn=server.createobject("Adodb.connection")
objConn.open strConn
set rsSchema=objConn.openSchema(20)
rsSchema.movefirst
y=0
Do Until rsSchema.EOF
if rsSchema("TABLE_TYPE")="TABLE" then
y=y+1
if y=1 then
if Session("TableName")="" then
Session("TableName")=rsSchema("TABLE_NAME")
end if
end if
TableListString=TableListString&rsSchema("TABLE_NAME")&";"
end if
rsSchema.movenext
Loop
if y>0 then
Session("TableNameTotal")=x
Session("TableNameList")=TableListString
end if
set objConn=nothing
End Sub
%> <select name="TableName" id="TableName" onChange="ChangeTableName(document.Form1.TableName.options[document.Form1.TableName.selectedIndex].value)">
<%if Session("TableName")="" then%>
<option value="" selected>没有任何数据表</option>
<%else%>
<option value="<%=Session("TableName")%>" selected><%=Session("TableName")%></option>
<%end if%>
<%
TableNameList=split(Session("TableNameList"),";",-1,1)
for i=0 to ubound(TableNameList)-1
%>
<option value="<%=TableNameList(i)%>" ><%=TableNameList(i)%></option>
<%
next
%>
</select> <script language="JavaScript">
function ChangeTableName(locationTable)
{
var locationTable=locationTable;
location.href='TableManage.asp?act=ChangTableName&TableName='+locationTable;
}
</script>
&nbsp;&nbsp; </td>
</tr>
<tr align="center">
<td height="25" align="left" ><strong><font color="#FF0000">*</font></strong>字段名称:</td>
<td width="585" height="25" align="left"> <%
PathName=Session("PathName")
TableName=Session("TableName")
Call FindTableColumnList(PathName,TableName,ColumnString)
Sub FindTableColumnList(PathName,TableName,ColumnString)
ColumnString=""
ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & PathName
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.open ConnStr
Const adSchemaTables = 20
adSchemaColumns = 4
Set rstSchema = oConn.OpenSchema(adSchemaColumns)
z=0
Do Until rstSchema.EOF
if rstSchema("Table_name") =tablename then
z=z+1
if z=1 then
if Session("ColumnName")="" then
Session("ColumnName")=rstschema("column_Name")
end if
end if
ColumnString=ColumnString&rstschema("column_Name")&";"

end if
rstSchema.MoveNext
Loop
if z>0 then
Session("ColumnNameList")=ColumnString
Session("ColumnNameTotal")=z
end if
end sub
%> <select name="ColumnName" id="ColumnName" onChange="ChangeColumnName(document.Form1.ColumnName.options[document.Form1.ColumnName.selectedIndex].value)">
<%if Session("ColumnName")="" then%>
<option value="" selected>没有任何字段</option>
<%else%>
<option value="<%=Session("ColumnName")%>" selected><%=Session("ColumnName")%></option>
<%end if%>
<%
ColumnNameList=split(Session("ColumnNameList"),";",-1,1)
for i=0 to ubound(ColumnNameList)-1
%>
<option value="<%=ColumnNameList(i)%>" ><%=ColumnNameList(i)%></option>
<%
next
%>
</select>
<script language="JavaScript">
function ChangeColumnName(locationColumn)
{
var locationColumn=locationColumn;
location.href='TableManage.asp?act=ChangColumnName&ColumnName='+locationColumn;
}
</script> </td>
</tr>
<%
PathName=Session("PathName")
TableName=Session("TableName")
ColumnName=Session("ColumnName")
Call FindColumnProperty(PathName,TableName,ColumnName,ColumnType,ColumnLength,ColumnDefault,ColumnDescription,ColumnNullable,ColumnValidRule,ColumnValidText,ColumnZeroLength,ColumnUnicode)

Sub FindColumnProperty(PathName,TableName,ColumnName,ColumnType,ColumnLength,ColumnDefault,ColumnDescription,ColumnNullable,ColumnValidRule,ColumnValidText,ColumnZeroLength,ColumnUnicode)
On Error resume next

strConn="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &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
For Each MyField In MyTable.Columns

if MyField.Name=ColumnName Then
Res=1
ColumnType1=MyField.Type
ColumnLength=MyField.DefinedSize
ColumnDefault=MyField.Properties("Default").Value
ColumnDescription=MyField.Properties("Description").Value
ColumnNullable=MyField.Properties("Nullable").Value
ColumnValidRule=MyField.Properties("Jet OLEDB:Column Validation Rule").Value
ColumnValidText=MyField.Properties("Jet OLEDB:Column Validation Text").Value
ColumnZeroLength =MyField.Properties("Jet OLEDB:Allow Zero Length").Value
ColumnUnicode=MyField.Properties("Jet OLEDB:Compressed UNICODE Strings").Value
Select Case ColumnType1
'/**字段的Type属性集*******
'/*Type = 2 整形
'/*Type = 3 长整形
'/*Type = 4 单精度形
'/*Type = 5 双精度形
'/*Type = 6 货币形
'/*Type = 7 日期时间
'/*Type = 11 逻辑形
'/*Type = 17 字节形
'/*Type = 202 文本型
'/*Type = 203 备注型
'/*Type = 205 OLE对象
'/*<option value="AutoIncrement" >自动编号</option>
'/*<option value="numeric">小数整数</option>

case 2
ColumnType="short"'整形
case 3
ColumnType="long"'长整形
case 4
ColumnType="real"'单精度
case 5
ColumnType="double"'双精度
case 6
ColumnType="currency"'货币
case 7
ColumnType="datetime"'日期/时间
case 11
ColumnType="yesno"'布尔
case 17
ColumnType="byte"'字节型
case 128
ColumnType="hyperlink"'二进制
case 133
ColumnType="date"'日期
case 134
ColumnType="time"'时间
case 135
ColumnType="datetime"'日期时间
case 202
ColumnType="varchar"'文本
case 203
ColumnType="memo"'备注
case 204
ColumnType="OleObject"'二进制
case 205
ColumnType="OleObject"'OLE对象
case else
ColumnType=ColumnType1
end Select
if MyField.Properties("AutoIncrement").Value=true then
ColumnType="AutoIncrement"
end if
exit for
end if
Next
if Res=1 then exit for
end if
if Res=1 then exit for
Next
'response.Write("<br>修改后的属性")
'response.write "<br>ColumnType:"&ColumnType
'response.write "<br>ColumnLength:"&ColumnLength
'response.write "<br>ColumnDefault:"&ColumnDefault
'response.write "<br>ColumnDescription:"&ColumnDescription
'response.write "<br>ColumnNullable:"&ColumnNullable
'response.write "<br>ColumnValidRule:"&ColumnValidRule
'response.write "<br>ColumnValidText:"&ColumnValidText
'response.write "<br>ColumnZeroLength:"&ColumnZeroLength
'response.write "<br>ColumnUnicode:"&ColumnUnicode
conn.close
set Conn=nothing
End Sub
%>
<tr align="center">
<td height="25" align="left" >字段类别：</td>
<td height="25" align="left"> <select name="RsType" size="1" id="RsType">
<option value="AutoIncrement" <%if ColumnType="AutoIncrement" then response.Write("selected")%>>自动编号</option>
<option value="varchar" <%if ColumnType="varchar" then response.Write("selected")%>>文本</option>
<option value="memo" <%if ColumnType="memo" then response.Write("selected")%>>备注</option>
<option value="short" <%if ColumnType="short" then response.Write("selected")%>>整型整数</option>
<option value="long" <%if ColumnType="long" then response.Write("selected")%>>长整型整数</option>
<option value="double" <%if ColumnType="double" then response.Write("selected")%>>双精度型整数</option>
<option value="real" <%if ColumnType="real" then response.Write("selected")%>>单精度型整数</option>
<option value="byte" <%if ColumnType="byte" then response.Write("selected")%>>字节型整数</option>
<option value="numeric" <%if ColumnType="numeric" then response.Write("selected")%>>小数整数</option>
<option value="datetime" <%if ColumnType="datetime" then response.Write("selected")%>>日期/时间</option>
<option value="date" <%if ColumnType="date" then response.Write("selected")%>>日期</option>
<option value="time" <%if ColumnType="time" then response.Write("selected")%>>时间</option>
<option value="currency" <%if ColumnType="currency" then response.Write("selected")%>>货币</option>
<option value="yesno" <%if ColumnType="yesno" then response.Write("selected")%>>是/否</option>
<option value="hyperlink" <%if ColumnType="hyperlink" then response.Write("selected")%>>超链接</option>
<option value="OleObject" <%if ColumnType="OleObject" then response.Write("selected")%>>OLE对象</option>
</select> &nbsp; </td>
</tr>
<tr align="center">
<td height="25" align="left" >字段长度：</td>
<td height="25" align="left"><input name="RsLength" type="text" id="RsLength" value="<%=ColumnLength%>">
小数位数取值<strong><font color="#FF0000">0-15位</font></strong></td>
</tr>
<tr align="center">
<td height="25" align="left" ><strong><font color="#FF0000">*</font></strong>默认值:</td>
<td height="25" align="left"><input name="RsDefault" type="text" id="RsDefault" value="<%=ColumnDefault%>">
&nbsp;</td>
</tr>
<tr align="center">
<td height="25" align="left" ><strong><font color="#FF0000">*</font></strong>字段描述内容：</td>
<td height="25" align="left"><input name="RsDescription" type="text" id="RsDescription" value="<%=ColumnDescription%>"></td>
</tr>
<tr align="center">
<td height="25" align="left" ><strong><font color="#FF0000">*</font></strong>有效性规则：</td>
<td height="25" align="left"><input name="RsValidRule" type="text" id="RsValidRule" value="<%=ColumnValidRule%>">
Jet OLEDB:Column Validation Rule</td>
</tr>
<tr align="center">
<td height="25" align="left" ><strong><font color="#FF0000">*</font></strong>有效性文本：</td>
<td height="25" align="left"><input name="RsValidText" type="text" id="RsValidText" value="<%=ColumnValidText%>">
Jet OLEDB:Column Validation Text</td>
</tr>
<tr align="center">
<td height="25" align="left" ><strong><font color="#FF0000">*</font></strong>必填字段：</td>
<td height="25" align="left"> <input name="RsNullable" type="radio" id="RsNullable" value=False <%if ColumnNullable=False then response.Write("checked")%>>
默认是
<input name="RsNullable" id="RsNullable" type="radio" value=True <%if ColumnNullable=True then response.Write("checked")%>>
默认否 </td>
</tr>
<tr align="center">
<td height="25" align="left" ><strong><font color="#FF0000">*</font></strong>允许空字符串：</td>
<td height="25" align="left"> <input name="RsZeroLength" id="RsZeroLength" type="radio" value=True <%if ColumnZeroLength=true then response.Write("checked")%> >
默认是
<input name="RsZeroLength" id="RsZeroLength" type="radio" value=False <%if ColumnZeroLength=False then response.Write("checked")%>>
默认否 Jet OLEDB:Allow Zero Length</td>
</tr>
<tr align="center">
<td height="25" align="left" >Unicode压缩：</td>
<td height="25" align="left"><input name="RsUnicode" id="RsUnicode" type="radio" value=True <%if ColumnUnicode=True then response.Write("checked")%>>
默认是
<input name="RsUnicode" id="RsUnicode" type="radio" value=False <%if ColumnUnicode=False then response.Write("checked")%>>
默认否 Jet OLEDB:Compressed UNICODE Strings</td>
</tr>
<tr align="left">
<td colspan="2">&nbsp;</td>
</tr>
<tr align="center">
<td colspan="2"><input type="submit" name="Submit" value="设置数据库表中字段常用属性">
&nbsp;&nbsp; </td>
</tr>
</form>
</table>
<%end if%>
<br>
<table width="739" border="0" align="center" cellpadding="0" cellspacing="0">
<form id="Form2" name="Form2"method="post" action="TableManage.asp?act=CopyTableConstruct" >
<tr>
<td height="31" colspan="2"><div align="center"><strong><font color="#FF0000">复制数据库结构</font></strong></div></td>
</tr>
<tr>
<td height="23" colspan="2">&nbsp;</td>
</tr>
<tr>
<td width="154" height="23"><strong><font color="#FF0000">*</font></strong>数据库路径:</td>
<td width="585" ><input name="PathName" type="text" id="PathName" maxlength="255" value="<%=Session("PathName")%>">
</td>
</tr>
<tr>
<td height="23"><strong><font color="#FF0000">*</font></strong>新数据库路径:</td>
<td ><input name="PathName1" type="text" id="PathName1" value="<%=Session("PathName1")%>"></td>
</tr>
<tr>
<td height="23"><strong><font color="#FF0000">*</font></strong><font color="#FF0000">原数据表</font>名称:</td>
<td > <input name="TableName" type="text" id="TableName" maxlength="255" value="<%=Session("TableName")%>">
&nbsp;&nbsp; </td>
</tr>
<tr align="center">
<td height="25" align="left" ><strong><font color="#FF0000">*</font></strong><font color="#FF0000">新数据表</font>名称：</td>
<td height="25" align="left"> <input name="TableName1" type="text" id="TableName1" maxlength="255"></td>
</tr>
<tr align="left">
<td colspan="2">&nbsp;</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 id="Form3" name="Form3"method="post" action="TableManage.asp?act=CopyTableName" >
<tr>
<td height="31" colspan="2"><div align="center"><strong><font color="#FF0000">复制数据库结构和内容</font></strong></div></td>
</tr>
<tr>
<td width="154" height="23"><strong><font color="#FF0000">*</font></strong>数据库路径:</td>
<td width="585" ><input name="PathName" type="text" id="PathName" maxlength="255" value="<%=Session("PathName")%>">
</td>
</tr>
<tr>
<td height="23"><strong><font color="#FF0000">*</font></strong>新数据库路径:</td>
<td ><input name="PathName1" type="text" id="PathName1" value="<%=Session("PathName1")%>"></td>
</tr>
<tr>
<td height="23"><strong><font color="#FF0000">*</font></strong><font color="#FF0000">原数据表</font>名称:</td>
<td > <input name="TableName" type="text" id="TableName" maxlength="255" value="<%=Session("TableName")%>">
&nbsp;&nbsp; </td>
</tr>
<tr align="center">
<td height="25" align="left" ><strong><font color="#FF0000">*</font></strong><font color="#FF0000">新数据表</font>名称：</td>
<td height="25" align="left"> <input name="TableName1" type="text" id="TableName1" maxlength="255"></td>
</tr>
<tr align="left">
<td colspan="2">&nbsp;</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 id="Form4" name="Form4"method="post" action="TableManage.asp?act=DropTableContent" >
<tr>
<td height="31" colspan="2"><div align="center"><strong><font color="#FF0000">删除数据表所有数据</font></strong></div></td>
</tr>
<tr>
<td width="154" height="23"><strong><font color="#FF0000">*</font></strong>数据库路径:</td>
<td width="585" ><input name="PathName" type="text" id="PathName" maxlength="255" value="<%=Session("PathName")%>"> </td>
</tr>
<tr>
<td height="23"><strong><font color="#FF0000">*</font></strong><font color="#FF0000">原数据表</font>名称:</td>
<td >
<input name="TableName" type="text" id="TableName" maxlength="255" value="<%=Session("TableName")%>">&nbsp;&nbsp; </td>
</tr>
<tr align="left">
<td colspan="2">&nbsp;</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 id="Form5" name="Form5"method="post" action="TableManage.asp?act=DropTableName" >
<tr>
<td height="31" colspan="2"><div align="center"><strong><font color="#FF0000">删除整个数据表结构和内容</font></strong></div></td>
</tr>
<tr>
<td width="154" height="23"><strong><font color="#FF0000">*</font></strong>数据库路径:</td>
<td width="585" ><input name="PathName" type="text" id="PathName" maxlength="255" value="<%=Session("PathName")%>"> </td>
</tr>
<tr>
<td height="23"><strong><font color="#FF0000">*</font></strong><font color="#FF0000">原数据表</font>名称:</td>
<td >
<input name="TableName" type="text" id="TableName" maxlength="255" value="<%=Session("TableName")%>">&nbsp;&nbsp; </td>
</tr>
<tr align="left">
<td colspan="2">&nbsp;</td>
</tr>
<tr align="center">
<td colspan="2"><input type="submit" name="Submit" value="删除整个数据表结构和内容">
&nbsp;&nbsp; </td>
</tr>
</form>
</table>
<br>
</body>
</html>