Asp实现Access自动创建数据库(代码调试通过)

最新推荐文章于 2024-09-08 13:50:54 发布

ITFLY8

最新推荐文章于 2024-09-08 13:50:54 发布

阅读量1.4k

点赞数

文章标签：数据库 access asp sql input null

本文链接：https://blog.csdn.net/ITFLY8/article/details/660397

版权

此代码实现
创建数据库创建表添加字段修改字段删除字段获取字段信息复制表结构整表复制功能
在此基础上可以实现更强大的数据库安装程序或应用于其他功能
此代码修改后可以应用于Sql数据库
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>Access自动创建数据库</title>
</head>
<%
DBName = "c:/AccessDB.mdb"
if Request.Form("SubmitCreate")<>"" then
'创建数据库
Set mycat = Server.CreateObject( "ADOX.Catalog" )
mycat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBName
if err.number=0 then
Response.Write("<script>alert('数据库创建成功')</script>")
end if
end if
%>
<%
if Request.Form("SubmitCreateTable")<>"" then
'创建数据表
Set conn = Server.CreateObject( "ADODB.Connection" )
conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & DBName
Sql="CREATE TABLE TestTable("&_
"id int IDENTITY (1, 1) NOT NULL ,"&_
"name varchar(255) NOT NULL ,"&_
"pass varchar(255) NULL ,"&_
"logintimes int Default 0 NOT NULL ,"&_
"createtime datetime Default now() NOT NULL "&_
")"
conn.execute(sql)
if err.number=0 then
Response.Write("<script>alert('数据表创建成功')</script>")
end if
end if
%>
<%
if Request.Form("AlterTable")<>"" then
'修改数据表
Set conn = Server.CreateObject( "ADODB.Connection" )
conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & DBName
Sql="ALTER TABLE TestTable("&_
"id int IDENTITY (1, 1) NOT NULL ,"&_
"name varchar(255) NOT NULL ,"&_
"pass varchar(255) NULL ,"&_
"logintimes int Default 0 NOT NULL ,"&_
"createtime datetime Default now() NOT NULL "&_
"flag int Default 0"&_
")"
conn.execute(sql)
if err.number=0 then
Response.Write("<script>alert('数据表字段修改成功')</script>")
end if
end if
%>
<%
if Request.Form("AddColumn")<>"" then
'增加数据表字段
Set conn = Server.CreateObject( "ADODB.Connection" )
conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & DBName
sql="alter table [TestTable] add lasttime datetime Default now()"
conn.execute(sql)
if err.number=0 then
Response.Write("<script>alert('数据表字段添加成功')</script>")
end if
end if
%>
<%
if Request.Form("AlterColumn")<>"" then
'修改数据表字段
Set conn = Server.CreateObject( "ADODB.Connection" )
conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & DBName
sql="alter table [TestTable] alter lasttime int Default 0"
conn.execute(sql)
if err.number=0 then
Response.Write("<script>alert('数据表字段修改成功')</script>")
end if
end if
%>
<%
if Request.Form("DropColumn")<>"" then
'修改数据表字段
Set conn = Server.CreateObject( "ADODB.Connection" )
conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & DBName
sql="alter table [TestTable] Drop Column lasttime"
conn.execute(sql)
if err.number=0 then
Response.Write("<script>alert('数据表字段删除成功')</script>")
end if
end if
%>
<%
if Request.Form("DropTable")<>"" then
'删除数据表
Set conn = Server.CreateObject( "ADODB.Connection" )
conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & DBName
sql="Drop table [TestTable]"
conn.execute(sql)
if err.number=0 then
Response.Write("<script>alert('数据表删除成功')</script>")
end if
end if
%>
<%
if Request.Form("TableColumn")<>"" then
'修改数据表字段
Set conn = Server.CreateObject( "ADODB.Connection" )
conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & DBName
sql="select * from TestTable where id=null"
set rs=server.CreateObject("adodb.recordset")
rs.open sql,conn,1,1
for each id in rs.fields
response.write id.name
response.write "<br/>"
next
end if
%>
<%
if Request.Form("CopyTableColumn")<>"" then
'复制表结构
Set conn = Server.CreateObject( "ADODB.Connection" )
conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & DBName
sql="select * into TestTable2 from TestTable where id=null"
conn.execute(sql)
if err.number=0 then
Response.Write("<script>alert('表结构复制成功')</script>")
end if
end if
%>
<%
if Request.Form("CopyTable")<>"" then
'整表复制
Set conn = Server.CreateObject( "ADODB.Connection" )
conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & DBName
sql="select * into TestTable2 from TestTable"
conn.execute(sql)
if err.number=0 then
Response.Write("<script>alert('整表复制成功')</script>")
end if
end if
%>
<body>
<form id="addForm" name="addForm" method="post" action="">
<table width="100%" border="1" align="center">
    <tr>
      <td> </td>
      <td><label>Access自动创建数据库</label></td>
    </tr>
    <tr>
      <td> </td>
      <td><label>
        <input name="SubmitCreate" type="submit" id="SubmitCreate" value="创建数据库" />
        <input name="SubmitCreateTable" type="submit" id="SubmitCreateTable" value="创建数据表" />
        <input name="AddColumn" type="submit" id="AddColumn" value="添加字段" />
        <input name="AlterColumn" type="submit" id="AlterColumn" value="修改字段" />
        <input name="DropColumn" type="submit" id="DropColumn" value="删除字段" />
        <input name="DropTable" type="submit" id="DropTable" value="删除数据表" />
        <input name="TableColumn" type="submit" id="TableColumn" value="获得字段信息" />
        <input name="CopyTableColumn" type="submit" id="CopyTableColumn" value="复制表结构" />
        <input name="CopyTable" type="submit" id="CopyTable" value="整表复制" />
      </label></td>
    </tr>
</table>
</form>
</body>
</html>