📄 access to sql 脚本编写器.htm
字号:
<BR><B>表,视图,索引,约束,</B>包括<B> 默认值,主键,自动编号,外键(表关系)
</B><BR>编写完自动保存为原数据库名+相应扩展的文件
<BR>Asp模式可直接生成带表单输入的可执行的Asp文件,用生成的Asp文件即可生成新的数据库
<BR>Sql模式可直接生成纯Sql语句文本</P>
<P><B>实现思路:</B><BR>用Ado 模式查询 + Adox对象模型
双管齐下<BR>通过编写模式查询的List文件(该代码需要可另外提供),分析表结构元素<BR>在加上Adox细节获取必要属性,比如自动编号的种子值,增长量<BR>索引集合的列的排序属性<BR>就基本实现了Access
Sql的脚本编写<BR>可能更实用的是编写成可执行的 Asp文件,<BR>因为 Access2000的查询设计运行
Sql语句实在弱智,还乱纠错</P>
<P><B>第一版代码:</B> 按海娃的提议<BR>1.修改了一下，备注类型用回Access标准字 text char / 我一直习惯
LongText varchar</P>
<P>2.按字段的在表中的顺序来编写，这样重建脚本时就保证一致了<BR>测试比较： 将原始数据库编写的sql
与该sql脚本重建的数据库再次编写的 sql比较 一致</P>
<P>代码拷贝框<BR><TEXTAREA class=codeTextarea><% @ LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<%Option Explicit
response.buffer=true
Response.Expires = -1
Response.AddHeader "Pragma","no-cache"
Response.AddHeader "cache-ctrol","no-cache"
%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>MiniAcces Editor1.0part2 Access SQL脚本编写器(V37 PaintBlue.Net 2004 Acp Code)</TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="V37">
<META NAME="Keywords" CONTENT="PaintBlue.Net,PaintBlue">
<META NAME="Description" CONTENT="PaintBlue.Net">
<style>
table{ color: #000000;
font-size: 9pt;
FONT-FAMILY: "Tahoma","MS Shell Dlg";
}
td { color: #000000;
font-size: 9pt;
}table{ color: #000000;
font-size: 9pt;
FONT-FAMILY: "Tahoma","MS Shell Dlg";
}
body { color: #000000;
font-size: 9pt;
}
</style>
</HEAD>
<body bgCOLOR=eeeeee text="#000000" leftmargin="0" marginwidth="100%" topmargin="0" bottommargin="20">
<%
dim enMode
dim DB_Name,ExtName,FileName
dim rs,CONN,CONNstr
DB_Name="/coosel2.0/data/mydb.mdb"
DB_Name=questStr("DB_Name")
FileName=questStr("DB_Name")
enMode=questStr("enMode")
if not isnumeric(enMode) then enMode=0
if DB_Name<>"" then
enMode=clng(enMode)
if enMode=0 then
ExtName=".Sql"
else
ExtName=".Asp"
end if
Call openDB(DB_Name)
Call CreateSQL(DB_Name,enMode)
else
if DB_Name="" then DB_Name="data/mydb.mdb"
Call Main()
end if
Sub MAIN()
%>
<style>
.titlebar {
FONT-WEIGHT: bold; FONT-SIZE: 12pt; FILTER: dropshadow(color=#333333, offx=1, offy=2); WIDTH: 100%; COLOR: #ffffff; FONT-FAMILY: Tahoma,Verdana, Arial, sans-serif; POSITION: relative; TOP: 1px
}
</style>
<FORM METHOD=POST ACTION="?action=1" Name=DBform>
<TABLE width="100%" cellspacing=0 border=0>
<TR bgcolor=#D4D0C8>
<TD align=center height=32><a href=http://www.paintblue.net/ target=_blank><img src=http://www.paintblue.net/bbs/images/TitleLogo.gif border=0></a></td><td><span class=titlebar><font color=#ffffff><b>MiniAccess Editor V1.0 P2 (Access 脚本编写器)</b></font></span></TD>
<td></td></TR>
<TABLE align=center width="100%" cellspacing=1 cellpadding=3 border=0>
</TABLE>
<TABLE align=center width="100%" cellspacing=1 cellpadding=3 border=0>
<TR bgcolor=#a4c0d8><TD align=right height=10></TD><TD></TD></TR>
<TR bgcolor=#D4D0C8>
<TD align=right><span id=a>编写模式</span></TD>
<TD>
<INPUT TYPE="radio" NAME="enMode" value="0" <%if enMode=0 then response.write "checked" end if%> >Sql文本
<INPUT TYPE="radio" NAME="enMode" value="1" <%if enMode=1 then response.write "checked" end if%> >Asp代码
</TD>
</TR>
<TR bgcolor=#D4D0C8>
<TD align=right width=250>数据库路径</TD>
<TD><INPUT TYPE="text" NAME="DB_Name" value="<%=DB_Name%>" style="width:70%;"> </TD>
</TR>
<TR bgcolor=#a4c0d8><TD align=right height=10></TD><TD></TD></TR>
<TR >
<TD height=38></TD>
<TD bgcolor=#D4D0C8>&nbsp;&nbsp;<INPUT TYPE="submit" value=" 确 定 " style="width:80;"></TD>
</TR>
<TR >
<TD height=38></TD>
<TD bgcolor=#D4D0C8>&nbsp;&nbsp;
<li><<简介>>
<li>功能:可编写Access数据库的常用的主要对象,包括 <br>&nbsp;&nbsp;&nbsp;&nbsp;<b>表,视图,索引,约束,包括 默认值,主键,自动编号,外键</b>(表关系)
<li>编写完自动保存为原数据库名+相应扩展的文件
<li>Asp模式可直接生成带表单输入的可执行的Asp文件,用生成的Asp文件即可生成新的数据库
<li>Sql模式可直接生成纯Sql语句文本</li><br><br></TD>
</TR>
</Table>
</FORM>
<%
End SUB
'====MiniAcces Editor1.0part2 Access SQL脚本编写器(V37 PaintBlue.Net 2004 Acp Code)=========
SUB openDB(DB_Name)
if inStr(DB_Name,":/")=0 and inStr(DB_Name,":\")=0 then
DB_Name=server.mappath(DB_Name)
end if
Set CONN = Server.CreateObject("ADODB.CONNection")
on error resume next
CONN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&DB_Name
if err.number<>0 then
rw "数据库打开失败,错误为:"&err.description,0
err.clear
else
Set rs=Server.CreateObject("adodb.recordSet")
end if
End SUB
SUB CreateSQL(DB_Name,exec)
'创建模式
'exec = 0 : 生成SQL语句
'exec = 1 : 生成Asp程序
dim tbls,tabsArr,ub,I,TtempStr,TtempStrHead
dim TableStr
if exec=1 then
TtempStrHead="<"&"% @ LANGUAGE=""VBSCRIPT""%"&">"&vbcrlf
TtempStrHead=TtempStrHead&"<"&"%Option Explicit"&vbcrlf
TtempStrHead=TtempStrHead&"response.buffer=true"&vbcrlf&vbcrlf
TtempStrHead=TtempStrHead&"'========================================================================="&vbcrlf&"'Access 数据库 SQL 脚本生成 by MiniAccess Edit V1.0 P2(V37 PaintBlue.Net 2004 Asp Code)"&vbcrlf&"'========================================================================="&vbcrlf&vbcrlf
end if
if instr(DB_Name,":\")=0 and instr(DB_Name,":/")=0 then
DB_Name=Server.MapPath(DB_Name)
end if
CONNstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DB_Name
Set CONN = Server.CreateObject("ADODB.Connection")
CONN.Open CONNstr
'编写CONN对象
if exec=1 then
TtempStr="SUB CreateDB(DB_Name)"&vbcrlf
TtempStr=TtempStr&"DIM CONN"&vbcrlf
TtempStr=TtempStr&"Set CONN=Server.CreateObject(""ADODB.Connection"")"&vbcrlf
TtempStr=TtempStr&"CONN.open ""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""&DB_Name"&vbcrlf&vbcrlf
end if
'编写表/索引对象
Set tbls=CONN.openSchema(20) 'adSchemaPrimaryKeys
tbls.Filter =" TABLE_TYPE='TABLE' " '筛选出有默认值，但允许null的列
while Not tbls.eof
TableStr=TableStr&"|"&tbls("TABLE_Name")
tbls.movenext
wend
tbls.filter=0
tbls.close
set tbls=nothing
TableStr=mid(TableStr,2)
if TableStr<>"" then
tabsArr=split(TableStr,"|")
ub=ubound(tabsArr)
for I=0 to ub
if exec=1 then TtempStr=TtempStr&"'["&tabsArr(I)&"]:"& vbcrlf
TtempStr=TtempStr&CreatTableSql(tabsArr(I),exec)&vbcrlf&vbcrlf
next
end if
'编写表关系
if TableStr<>"" then TtempStr=TtempStr&CreatForeignSql(exec)
'编写视图
TtempStr=TtempStr&CreatViewSql(exec)
if exec=1 then
TtempStr=replace(TtempStr,">","""&chr(62)&""")
TtempStr=replace(TtempStr,"<","""&chr(60)&""")
TtempStr=TtempStr&"End SUB"& vbcrlf&vbcrlf
TtempStr=TtempStr&" call CreateMDB()"&vbcrlf
TtempStr=TtempStr&"SUB Main()"&vbcrlf
TtempStr=TtempStr&" Response.write(""<center><FORM METHOD=POST><input name=DB_Name Value="""""&Server.Htmlencode(DB_Name)&""""" style=""""width:70%;""""><br><INPUT TYPE=submit name=CreateDB Value=创建数据库></FORM></center>"")"&vbcrlf
TtempStr=TtempStr&"End SUB" & vbCrlf& vbCrlf
TtempStr=TtempStr& "SUB CreateMDB()" & vbCrlf
TtempStr=TtempStr& " dim cat,NewDB_Name" & vbCrlf
TtempStr=TtempStr& " NewDB_Name=request(""DB_Name"")" & vbCrlf
TtempStr=TtempStr& " if NewDB_Name<>"""" then" & vbCrlf
TtempStr=TtempStr& " if instr(NewDB_Name,"":\"")=0 and instr(NewDB_Name,"":/"")=0 then" & vbCrlf
TtempStr=TtempStr& " NewDB_Name=Server.MapPath(NewDB_Name)" & vbCrlf
TtempStr=TtempStr& " end if " & vbCrlf
TtempStr=TtempStr& " set cat=Server.CreateObject(""ADOX.Catalog"") " & vbCrlf
TtempStr=TtempStr& " cat.Create ""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""&NewDB_Name" & vbCrlf
TtempStr=TtempStr& " set cat=nothing " & vbCrlf
TtempStr=TtempStr& " CreateDB(NewDB_Name)" & vbCrlf
TtempStr=TtempStr& " response.write vbcrlf&""OK""" & vbCrlf
TtempStr=TtempStr& " else" & vbCrlf
TtempStr=TtempStr& " set cat=nothing " & vbCrlf
TtempStr=TtempStr& " call main()" & vbCrlf
TtempStr=TtempStr& " end if" & vbCrlf
TtempStr=TtempStr& "End SUB"
TtempStr=TtempStrHead&TtempStr&vbcrlf&"%"&">"
end if
call Ados_Write(TtempStr,DB_Name&ExtName,"gb2312")
rw "<br><img width=100 height=0>"&DB_Name&"的SQL脚本编写完成",1
rw "<img width=100 height=0>已经保存文件为<b><font color=blue>"&DB_Name&ExtName&"</font></b>[<a href=?>返回</a>]:",1
rw "<center><textarea style=""width:70%;height:500px;"" wrap=""off"">"&server.Htmlencode(TtempStr)&"</textarea></center>",1
End SUB
function CreatViewSql(exec)
dim cols
dim FKtable,PK_cols,FK_cols,tmpStr,tmpStr1,VIEW_DEFINITION
Set cols=CONN.openSchema(23)
cols.filter=0
while not cols.eof
tmpStr1=""
VIEW_DEFINITION=replace(cols("VIEW_DEFINITION"),chr(13),"")
VIEW_DEFINITION=replace(VIEW_DEFINITION,chr(10)," ")
tmpStr1="Create view ["&cols("TABLE_NAME")&"] As "&VIEW_DEFINITION&""
if exec=1 then tmpStr1="CONN.execute("""&tmpStr1&""")"
tmpStr=tmpStr&vbcrlf&tmpStr1
cols.movenext
wend
cols.close
set cols=nothing
CreatViewSql=tmpStr
End Function
function CreatTableSql(byval tableName,exec)
dim cols
dim TmpStr
Set cols=CONN.openSchema(4)
dim splitchar,splitchar1
if exec=1 then
splitchar=""""
splitchar1="""&_"
else
splitchar=""
splitchar1=""
end if
cols.filter="Table_name='"&tableName&"'"
if cols.eof then
exit function
end if
dim cat,autoclumn,n,chkPrimaryKey
n=0
' 编写表脚本
autoclumn=GetAutoincrementCoulmnT(tableName)
if exec=1 then
TmpStr="CONN.execute(""CREATE TABLE ["&tableName&"] (""&_"& vbcrlf
else
TmpStr="CREATE TABLE ["&tableName&"] ("& vbcrlf
end if
dim autoclumnStr,columnStr
if autoclumn<>"" then
autoclumnStr= " "&splitchar&"["& autoclumn &"] integer IDENTITY (1,"&GetIncrement(tableName,autoclumn)&") not null"
end if
n=0
do
n=n+1
cols.filter="Table_name='"&tableName&"' and ORDINAL_POSITION="&n
if cols.eof then exit do
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -