Access编程-操作（宏）[2]
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
(责任编辑：admin)
