Option Compare Database
Dim tS As Long
Dim sj_ZhuKuLuJing As String, sj_FuKuLuJing As String
'聯接帶密碼的處部數據庫（源）
'處理兩個外部數據庫文件的過程
'
'以下共有六個過程，分別處理不同的任務
'第一步：主過程，設置需要聯接表的對象
'第二步：設置完成後根據設置需要一一調用執行相關過程
'選擇目標數據庫路徑及文件
'關閉所有打開的窗體和報表
'刪除已建立的聯接表 共64個聯接表
'建立64個表的聯接表
'壓縮前台數據庫
'
Function LinkAllDataTable()
On Error GoTo LinkData_Err
tS = MsgBox("只聯接主數據庫嗎？", 36, "聯接")
If tS = 6 Then
sj_ZhuKuLuJing = dlgGetFile(CurrentProject.Path & "\", "請選擇(*.MDB文件)" & vbNullChar & "*.MDB", , , , "主數據源")
Else
tS = MsgBox("只聯接輔助數據庫嗎？", 36, "聯接")
If tS = 6 Then
sj_FuKuLuJing = dlgGetFile(CurrentProject.Path & "\", "請選擇(*.MDB文件)" & vbNullChar & "*.MDB", , , , "輔助數據源")
Else
tS = MsgBox("聯接所有的數據庫資源嗎？", 36, "聯接")
If tS = 6 Then
sj_ZhuKuLuJing = dlgGetFile(CurrentProject.Path & "\", "請選擇(*.MDB文件)" & vbNullChar & "*.MDB", , , , "主數據源")
sj_FuKuLuJing = dlgGetFile(CurrentProject.Path & "\", "請選擇(*.MDB文件)" & vbNullChar & "*.MDB", , , , "輔助數據源")
Else
End
End If
End If
End If
Call CloseAllForms '關閉所有打開的窗體但不含帳號與密碼窗口
Call CloseAllReports '關閉所有打開的報表
Call DelAllLinkTable '調用刪除所有聯接表
Call AddAllLinkTable '重新建立所有需要聯接的數據庫資源
'最後詢問用是否需要壓縮數據庫
tS = MsgBox("全部數據源已重新更新，是否對系統進行壓縮後再運行？", 36, "要求壓縮")
If tS = 6 Then
CommandBars("menu bar").Controls("工具(&T)").Controls("數據庫實用工具(&D)").Controls("壓縮和修復數據庫(&C)...").accDoDefaultAction
End If
LinkData_Exit:
Exit Function
LinkData_Err:
MsgBox Error$
Resume LinkData_Exit
End Function
Public Sub CloseAllForms() '用代碼關閉所有窗體
Dim intx As Integer
Dim intCount As Integer
intCount = Forms.Count - 1
For intx = intCount To 0 Step -1
If Forms(intx).Name <> "帳號與密碼" Then
DoCmd.Close acForm, Forms(intx).Name
End If
Next intx
End Sub
Public Sub CloseAllReports() '用代碼關閉所有報表
Dim intx As Integer
Dim intCount As Integer
intCount = Reports.Count - 1
For intx = intCount To 0 Step -1
DoCmd.Close acReport, Reports(intx).Name
Next intx
End Sub
Public Sub AddAllLinkTable() '建立所有需要聯接的數據庫資源
Dim dbs As Object, dbs2 As Object
If sj_FuKuLuJing <> "" Then
Set dbs = DBEngine.Workspaces(0).OpenDatabase(sj_FuKuLuJing, False, False, ";pwd=源數據庫的密碼")
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_FuKuLuJing, acTable, "操作參數存儲器", "操作參數存儲器", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_FuKuLuJing, acTable, "UsysPasswordSj", "UsysPassWordSj", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_FuKuLuJing, acTable, "工資字段名設置", "工資字段名設置", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_FuKuLuJing, acTable, "注冊用戶資料", "注冊用戶資料", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_FuKuLuJing, acTable, "系統參數", "系統參數", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_FuKuLuJing, acTable, "計劃任務", "計劃任務", False
End If
If sj_ZhuKuLuJing <> "" Then
Set dbs2 = DBEngine.Workspaces(0).OpenDatabase(sj_ZhuKuLuJing, False, False, ";pwd=源數據庫的密碼")
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "USYSTEMRIZHI", "USYSTEMRIZHI", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "報表說明文字", "報表說明文字", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "報關類型", "報關類型", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "報料單", "報料單", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "報料單明細", "報料單明細", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "報料單明細_報關", "報料單明細_報關", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "報料單明細_派車單", "報料單明細_派車單", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "報料單明細_轉關", "報料單明細_轉關", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "超級對象管理器", "超級對象管理器", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "超重貨物車輛燃油配置", "超重貨物車輛燃油配置", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "車檢場入場費收費標准", "車檢場入場費收費標准", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "車輛海關備案", "車輛海關備案", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "車輛禁令", "車輛禁令", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "車輛派遣", "車輛派遣", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "車輛資料", "車輛資料", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "車輛綜合帳存根", "車輛綜合帳存根", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "車輛作業時間", "車輛作業時間", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "船公司存櫃堆場表", "船公司存櫃堆場表", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "船期表", "船期表", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "船期時刻表", "船期時刻表", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "發票表", "發票表", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "發票明細表", "發票明細表", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "費用結算表", "費用結算表", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "費用結算明細帳表", "費用結算明細帳表", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "工廠默認值設置", "工廠默認值設置", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "工廠資料", "工廠資料", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "工資資料", "工資資料", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "公裡數判斷數據庫", "公裡數判斷數據庫", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "股票資料", "股票資料", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "櫃型車型", "櫃型車型", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "貨幣種類", "貨幣種類", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "記錄打印登記表", "記錄打印登記表", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "加油資料表", "加油資料表", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "接收單位資料", "接收單位資料", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "客戶報表設置", "客戶報表設置", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "客戶費用欄目名稱設置", "客戶費用欄目名稱設置", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "來往單位綜合資料", "來往單位綜合資料", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "聯系人資料", "聯系人資料", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "收入明細帳表", "收入明細帳表", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "收入帳務表", "收入帳務表", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "司機禁令", "司機禁令", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "特殊裡程參考表", "特殊裡程參考表", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "托運單打印選項設置", "托運單打印選項設置", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "拖架編碼表", "拖架編碼表", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "異常費用申請", "異常費用申請", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "銀行帳號", "銀行帳號", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "營運狀態", "營運狀態", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "營運資料", "營運資料", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "油價表", "油價表", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "員工司機資料", "員工司機資料", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "運費收取條款", "運費收取條款", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "運輸公司名稱資料", "運輸公司名稱資料", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "支出明細帳表", "支出明細帳表", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "支出帳務表", "支出帳務表", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "支付運費條款", "支付運費條款", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "裝載貨物資料", "裝載貨物資料", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "資料與檔案", "資料與檔案", False
DoCmd.TransferDatabase acLink, "Microsoft Access", sj_ZhuKuLuJing, acTable, "自定義客戶報表公式方案", "自定義客戶報表公式方案", False
End If
End Sub
Public Sub DelAllLinkTable() '刪除所有聯接的表
If sj_FuKuLuJing <> "" Then
If fExistTable("操作參數存儲器") = True Then DoCmd.DeleteObject acTable, "操作參數存儲器"
If fExistTable("UsysPasswordSj") = True Then DoCmd.DeleteObject acTable, "UsysPassWordSj"
If fExistTable("工資字段名設置") = True Then DoCmd.DeleteObject acTable, "工資字段名設置"
If fExistTable("注冊用戶資料") = True Then DoCmd.DeleteObject acTable, "注冊用戶資料"
If fExistTable("系統參數") = True Then DoCmd.DeleteObject acTable, "系統參數"
If fExistTable("計劃任務") = True Then DoCmd.DeleteObject acTable, "計劃任務"
End If
If sj_ZhuKuLuJing <> "" Then
If fExistTable("USYSTEMRIZHI") = True Then DoCmd.DeleteObject acTable, "USYSTEMRIZHI"
If fExistTable("報表說明文字") = True Then DoCmd.DeleteObject acTable, "報表說明文字"
If fExistTable("報關類型") = True Then DoCmd.DeleteObject acTable, "報關類型"
If fExistTable("報料單") = True Then DoCmd.DeleteObject acTable, "報料單"
If fExistTable("報料單明細") = True Then DoCmd.DeleteObject acTable, "報料單明細"
If fExistTable("報料單明細_報關") = True Then DoCmd.DeleteObject acTable, "報料單明細_報關"
If fExistTable("報料單明細_派車單") = True Then DoCmd.DeleteObject acTable, "報料單明細_派車單"
If fExistTable("報料單明細_轉關") = True Then DoCmd.DeleteObject acTable, "報料單明細_轉關"
If fExistTable("超級對象管理器") = True Then DoCmd.DeleteObject acTable, "超級對象管理器"
If fExistTable("超重貨物車輛燃油配置") = True Then DoCmd.DeleteObject acTable, "超重貨物車輛燃油配置"
If fExistTable("車檢場入場費收費標准") = True Then DoCmd.DeleteObject acTable, "車檢場入場費收費標准"
If fExistTable("車輛海關備案") = True Then DoCmd.DeleteObject acTable, "車輛海關備案"
If fExistTable("車輛禁令") = True Then DoCmd.DeleteObject acTable, "車輛禁令"
If fExistTable("車輛派遣") = True Then DoCmd.DeleteObject acTable, "車輛派遣"
If fExistTable("車輛資料") = True Then DoCmd.DeleteObject acTable, "車輛資料"
If fExistTable("車輛綜合帳存根") = True Then DoCmd.DeleteObject acTable, "車輛綜合帳存根"
If fExistTable("車輛作業時間") = True Then DoCmd.DeleteObject acTable, "車輛作業時間"
If fExistTable("船公司存櫃堆場表") = True Then DoCmd.DeleteObject acTable, "船公司存櫃堆場表"
If fExistTable("船期表") = True Then DoCmd.DeleteObject acTable, "船期表"
If fExistTable("船期時刻表") = True Then DoCmd.DeleteObject acTable, "船期時刻表"
If fExistTable("發票表") = True Then DoCmd.DeleteObject acTable, "發票表"
If fExistTable("發票明細表") = True Then DoCmd.DeleteObject acTable, "發票明細表"
If fExistTable("費用結算表") = True Then DoCmd.DeleteObject acTable, "費用結算表"
If fExistTable("費用結算明細帳表") = True Then DoCmd.DeleteObject acTable, "費用結算明細帳表"
If fExistTable("工廠默認值設置") = True Then DoCmd.DeleteObject acTable, "工廠默認值設置"
If fExistTable("工廠資料") = True Then DoCmd.DeleteObject acTable, "工廠資料"
If fExistTable("工資資料") = True Then DoCmd.DeleteObject acTable, "工資資料"
If fExistTable("公裡數判斷數據庫") = True Then DoCmd.DeleteObject acTable, "公裡數判斷數據庫"
If fExistTable("股票資料") = True Then DoCmd.DeleteObject acTable, "股票資料"
If fExistTable("櫃型車型") = True Then DoCmd.DeleteObject acTable, "櫃型車型"
If fExistTable("貨幣種類") = True Then DoCmd.DeleteObject acTable, "貨幣種類"
If fExistTable("記錄打印登記表") = True Then DoCmd.DeleteObject acTable, "記錄打印登記表"
If fExistTable("加油資料表") = True Then DoCmd.DeleteObject acTable, "加油資料表"
If fExistTable("接收單位資料") = True Then DoCmd.DeleteObject acTable, "接收單位資料"
If fExistTable("客戶報表設置") = True Then DoCmd.DeleteObject acTable, "客戶報表設置"
If fExistTable("客戶費用欄目名稱設置") = True Then DoCmd.DeleteObject acTable, "客戶費用欄目名稱設置"
If fExistTable("來往單位綜合資料") = True Then DoCmd.DeleteObject acTable, "來往單位綜合資料"
If fExistTable("收入明細帳表") = True Then DoCmd.DeleteObject acTable, "收入明細帳表"
If fExistTable("收入帳務表") = True Then DoCmd.DeleteObject acTable, "收入帳務表"
If fExistTable("聯系人資料") = True Then DoCmd.DeleteObject acTable, "聯系人資料"
If fExistTable("司機禁令") = True Then DoCmd.DeleteObject acTable, "司機禁令"
If fExistTable("特殊裡程參考表") = True Then DoCmd.DeleteObject acTable, "特殊裡程參考表"
If fExistTable("托運單打印選項設置") = True Then DoCmd.DeleteObject acTable, "托運單打印選項設置"
If fExistTable("拖架編碼表") = True Then DoCmd.DeleteObject acTable, "拖架編碼表"
If fExistTable("異常費用申請") = True Then DoCmd.DeleteObject acTable, "異常費用申請"
If fExistTable("銀行帳號") = True Then DoCmd.DeleteObject acTable, "銀行帳號"
If fExistTable("營運狀態") = True Then DoCmd.DeleteObject acTable, "營運狀態"
If fExistTable("營運資料") = True Then DoCmd.DeleteObject acTable, "營運資料"
If fExistTable("油價表") = True Then DoCmd.DeleteObject acTable, "油價表"
If fExistTable("員工司機資料") = True Then DoCmd.DeleteObject acTable, "員工司機資料"
If fExistTable("運費收取條款") = True Then DoCmd.DeleteObject acTable, "運費收取條款"
If fExistTable("運輸公司名稱資料") = True Then DoCmd.DeleteObject acTable, "運輸公司名稱資料"
If fExistTable("支出明細帳表") = True Then DoCmd.DeleteObject acTable, "支出明細帳表"
If fExistTable("支出帳務表") = True Then DoCmd.DeleteObject acTable, "支出帳務表"
If fExistTable("支付運費條款") = True Then DoCmd.DeleteObject acTable, "支付運費條款"
If fExistTable("裝載貨物資料") = True Then DoCmd.DeleteObject acTable, "裝載貨物資料"
If fExistTable("資料與檔案") = True Then DoCmd.DeleteObject acTable, "資料與檔案"
If fExistTable("自定義客戶報表公式方案") = True Then DoCmd.DeleteObject acTable, "自定義客戶報表公式方案"
End If
End Sub
'判斷表是否存在的函數
Function fExistTable(strTableName As String) As Integer
Dim db As Database
Dim i As Integer
Set db = DBEngine.Workspaces(0).Databases(0)
fExistTable = False
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
If strTableName = db.TableDefs(i).Name Then
'Table Exists
fExistTable = True
Exit For
End If
Next i
Set db = Nothing
End Function
