在获得清爽的链接表 [英] refreshing linked tables in access

问题描述

嘿。 我有位于一个独立的PC机关闭网络上的主数据库的访问和我有联退给独立的计算机网络上链接表的Access数据库。我已经通过创建一个网络共享的独立PC和连接它们虽然路径链接表。我可以设置它，以便当打开数据库，它会自动更新链接表。 本

Hey. I have the main access database located on a stand alone PC off the network and i have a access database with linked tables on the network linked back to the stand alone PC. I have linked the tables by creating a network share to the stand alone PC and linking them though a path. Can i set it up so that when the database is opened it automatically updates the linked tables. Ben

推荐答案

可以。我常常觉得方便使用在启动（通过启动选项设置）运行，并检查各种各样的事情，包括连接表的小额支票的形式。为此，我还抱在本地机器上的链接表的表，尽管可以通过TableDefs集合迭代获得链接表的列表，我认为这是稍微更安全，以保持一个列表。

You can. I often find it convenient to use a small check form that runs on start-up (set through start-up options) and checks a variety of things, including linked tables. To this end, I also hold a table of linked tables on the local machine, although a list of linked tables can be obtained by iterating through the TableDefs collection, I think it is slightly safer to keep a list.

支票形式可以检查各个环节，如果一个链接损坏或丢失，或者要求用户提供一个新的位置，或使用一个固定的位置。如果没有发现问题，该表可自行关闭，并打开一个菜单或其他形式。

The check form can check all links and if a link is broken or missing, either ask the user for a new location or use a fixed location. If no problems are found, the form can close itself and open a menu or other form.

在链接到一个链接表的情况下，有可能得到从要使用的连接

In the case of linking to a linked table, it is possible to get the connection to use from:

 CurrentDB.TableDefs("TableName").Connection

下面是一些注意事项：

Sub RelinkTables(Optional strConnect As String = "")
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL
Dim tdf As DAO.TableDef

On Error GoTo TrapError

    Set db = CurrentDb

    If strConnect = "" Then
        ''Where Me.txtNewDataDirectory is a control on the check form
        strConnect = "MS Access;PWD=databasepassword;DATABASE=" & Me.txtNewDataDirectory
    End If

    ''Table of tables to be linked with two fields TableName, TableType
    Set rs = CurrentDb.OpenRecordset("Select TableName From sysTables " _
           & "WHERE TableType = 'LINK'")

    Do While Not RS.EOF
        ''Check if the table is missing
        If IsNull(DLookup("[Name]", "MSysObjects", "[Name]='" & rs!TableName & "'")) Then
            Set tdf = db.CreateTableDef(RS!TableName, dbAttachSavePWD, _
                rs!TableName, strConnect)
            ''If the table is missing, append it
            db.TableDefs.Append tdf
        Else
            ''If it exists, update the connection
            db.TableDefs(rs!TableName).Connect = strConnect
        End If
        db.TableDefs(rs!TableName).RefreshLink
        RS.MoveNext
    Loop

    Set db = Nothing
    RS.Close
    Set RS = Nothing


Exit_Sub:
    Exit Sub

TrapError:
    HandleErr Err.Number, Err.Description, "Relink Tables"

End Sub

这篇关于在获得清爽的链接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！