刷新访问中的链接表 [英] refreshing linked tables in access

问题描述

嘿.我的主要访问数据库位于网络外的独立 PC 上，我有一个访问数据库，其中包含链接回独立 PC 的网络上的链接表.我通过创建一个网络共享到独立 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

这里还有一些注意事项:

Here are some more notes:

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屋！