?? 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 + -