设置链接数据库(MS Access)路径而不访问链接数据库 [英] Setting Linked Database (MS Access) path without access to linked database

问题描述

我有一个包含两个部分的Access系统:一个包含表单，报表和宏的前端" .mdb文件，以及一个包含数据的后端.mdb文件.前端MDB文件的副本存储在每台计算机上，后端文件位于\\server\share\backend.mdb.前端MDB文件使用Access的链接表功能连接到后端MDB.

I have an Access system that comprises two parts: a "frontend" .mdb file that contains forms, reports and macros, and a backend .mdb file that contains the data. Copies of the frontend MDB files are stored on each computer and the backend file is located at \\server\share\backend.mdb. The frontend MDB files use the Linked Table feature of Access to connect to the backend MDB.

最近，我对家庭网络上的MDB进行了一些更改，我将两个文件都复制到了我的计算机上，并且能够更改链接表路径，因为我的本地计算机上有后端文件.但是，现在我需要将更新的前端MDB重新放到(远程)客户端的网络上，但是我无法远程进入将链接表路径重新更改为\\server\shares\backend.mdb的操作.

I recently made some changes to the MDB on my home network, I copied both files to my machine and was able to change the Linked Table path because I had the backend file on my local computer. However now I need to put the updated frontend MDB back on the (remote) client's network, however there is no way for me to remote-in to change the Linked Table path back to \\server\shares\backend.mdb.

是否可以将链接表"路径(在我的本地计算机上)设置为不存在的路径? Access GUI仅允许我通过文件打开"对话框进行设置，因此不允许我手动进行设置.

Is there any way to set the Linked Table path (on my local computer) to a path that doesn't exist? The Access GUI only lets me set it through a File Open dialog and so doesn't let me manually set it.

我正在使用Access 2010，尽管客户端使用Access 2003和Access 2013.

I'm using Access 2010, though the client uses Access 2003 and Access 2013.

推荐答案

我使用此代码链接到另一个后端文件(例如，如果我交付，并且后端不在同一个位置，就像很少那样)

I use this code to link to another backend file (say if I deliver and the backend is not in the same place, like it rarely is)

Public Function AttachToAnotherDataFile() As Boolean
    On Error GoTo 0
    Dim ofd As FileDialog
    Dim result As VbMsgBoxResult
    Set ofd = FileDialog(msoFileDialogFilePicker)
    ofd.show
    If ofd.SelectedItems.Count = 1 Then

        result = RelinkLinedTablesToBackend(ofd.SelectedItems(1))
        If result = vbCancel Then
            AttachToAnotherDataFile = False
        End If
        AttachToAnotherDataFile = True
    Else
        AttachToAnotherDataFile = False
    End If
End Function

Function RelinkLinedTablesToBackend(backendPath As String) As VbMsgBoxResult

    Dim tdf As TableDef
    Dim db As Database
    Dim tdfRefresh As TableDef
    Set db = CurrentDb

        For Each tdf In CurrentDb.TableDefs
            If tdf.Connect <> vbNullString Then
                On Error Resume Next
                db.TableDefs(tdf.Name).Connect = ";DATABASE=" & backendPath
                db.TableDefs(tdf.Name).RefreshLink
                If Err.Number <> 0 Then
                    RelinkLinedTablesToBackend = MsgBox(Err.Description, vbCritical + vbRetryCancel, "Error #:" & Err.Number)
                    Exit Function
                End If

                On Error GoTo 0
            End If
        Next

    Set tdf = Nothing
    Set db = Nothing
End Function

然后，当数据库打开时打开默认表单时，我尝试连接到后端

Then when I open my default form when the DB opens I try to connect to the backend

On Error Resume Next
    Dim rs As DAO.Recordset: Set rs = CurrentDb.OpenRecordset("Select Username, Password, UserGroup FROM Users")
    If Err.Number = 3024 Or Err.Number = 3044 Then
        MsgBox Err.Description & vbNewLine & "You will be prompted next to locate the data file. Without this file the database cannot open." _
            , vbCritical + vbOKOnly, "Backend Data File Not Found"
        GoTo FindBackEndFile
    End If

这篇关于设置链接数据库(MS Access)路径而不访问链接数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！