面向:初學(xué)者
目的:如果一年的數(shù)據(jù)較多,希望在分年的數(shù)據(jù)庫(kù)中保存數(shù)據(jù)
知識(shí)點(diǎn):1.數(shù)據(jù)庫(kù)拆分
2.文件查找技術(shù)
3.文件復(fù)制
4.鏈接表的刷新
步驟: 1.將一些每年都要使用(修改,添加等)的表的名稱前兩個(gè)字母改為共同的(如:or_業(yè)務(wù)人員名單,or_收貨人名單等),注意不要是"ms","sw","us"等系統(tǒng)要使用的字母
2.將數(shù)據(jù)庫(kù)拆分(假如前端名稱為:出口業(yè)務(wù)記錄.mdb,后端名稱為:出口業(yè)務(wù)記錄_dataorigin.mdb
3.在啟動(dòng)窗體(假如名稱為:窗體1)中建立一文本框(假如名稱為:所屬年份)
4.在窗體1的open事件和所屬年份的afterupdate事件中調(diào)用下面的"查找文件"過(guò)程.
public sub 鏈接()
on error goto lj_error
dim tabname as string
dim tab1 as tabledef
dim mypath as string
mypath = application.currentproject.path
currentdb.tabledefs.refresh '刷新當(dāng)前數(shù)據(jù)庫(kù)中的表對(duì)象
if currentdb.tabledefs(15).connect = ";database=" & mypath & "/出口業(yè)務(wù)記錄_data" & forms!窗體1!所屬年份 & ".mdb" then
exit sub
else
for each tab1 in currentdb.tabledefs
tabname = tab1.name
if left(tabname, 2) <> "ms" and left(tabname, 2) <> "sw" and left(tabname, 2) <> "us" then
if left(tabname, 2) = "or" then
tab1.connect = ";database=" & mypath & "/出口業(yè)務(wù)記錄_dataorigin.mdb"
else
tab1.connect = ";database=" & mypath & "/出口業(yè)務(wù)記錄_data" & forms!窗體1!所屬年份 & ".mdb"
end if
tab1.refreshlink
end if
next tab1
msgbox forms!窗體1!所屬年份 & "年的基礎(chǔ)數(shù)據(jù)庫(kù)連接成功!"
end if
exit_lj_error:
exit sub
lj_error:
msgbox forms!窗體1!所屬年份 & "年的后端數(shù)據(jù)庫(kù)文件不存在!"
resume exit_lj_error
end sub
public sub 查找文件()
dim mypath as string
dim fs as variant
dim tabname as string
dim tab1 as tabledef
mypath = application.currentproject.path
set fs = application.filesearch
with fs
.lookin = mypath
.searchsubfolders = true
.filename = "出口業(yè)務(wù)記錄_data" & forms!窗體1!所屬年份 & ".mdb"
if .execute() <= 0 then
if msgbox("沒(méi)有" & forms!窗體1!所屬年份 & "年的數(shù)據(jù)庫(kù),是否要?jiǎng)?chuàng)建一個(gè)?", vbyesno) = vbyes then
forms!窗體1.form!版本.form.recordsource = ""
filecopy mypath & "/出口業(yè)務(wù)記錄_dataorigin.mdb", mypath & "/出口業(yè)務(wù)記錄_data" & forms!窗體1!所屬年份 & ".mdb"
else
forms!窗體1!所屬年份 = year(now())
msgbox "沒(méi)有" & forms!窗體1!所屬年份 & "年的數(shù)據(jù)庫(kù)!"
exit sub
end if
end if
end with
鏈接
end sub
4.在窗體1的close事件中寫:
private sub form_close()
dim tabname as string
dim tab1 as tabledef
dim mypath as string
mypath = application.currentproject.path
currentdb.tabledefs.refresh '刷新當(dāng)前數(shù)據(jù)庫(kù)中的表對(duì)象
if currentdb.tabledefs(15).connect = ";database=" & mypath & "/出口業(yè)務(wù)記錄_data" & year(now()) & ".mdb" then
exit sub
else
for each tab1 in currentdb.tabledefs
tabname = tab1.name
if left(tabname, 2) <> "ms" and left(tabname, 2) <> "sw" and left(tabname, 2) <> "us" then
if left(tabname, 2) = "or" then
tab1.connect = ";database=" & mypath & "/出口業(yè)務(wù)記錄_dataorigin.mdb"
else
tab1.connect = ";database=" & mypath & "/出口業(yè)務(wù)記錄_data" & year(now()) & ".mdb"
end if
tab1.refreshlink
end if
next tab1
end if
end sub
注意窗體一最好是沒(méi)有任何綁定控件的切換面板,如果有這樣的控件,在更改鏈接表connect屬性和filecopy之前要設(shè)置這些控件的所有***source(如rcordsource,rowsource等)="",完成相關(guān)語(yǔ)句后再設(shè)置成原來(lái)的值.
希望指正!!!
