联接两个以上外部带密码数据库文件的处理

时　间:2009-03-17 18:02:21
作　者:沈军   ID：2469  城市：东莞
摘　要:设计一个大型的数据库应用程序一般都分为前台程序文件和后台数据源文件，而后台文件一般都会设置密码以求保证数据库的安全。这里我介绍一下我的应用管理系统是否如何处理外部数据库文件的。
正　文:

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