CODEWITHSUNDEEP
performancems-accessvbams-access-2010
AardVark71
AardVark71

Reputation: 4126

Slow relinking database in multi-user situation

We have an access application (split into frontend and backend) that uses the following code to relink tables:

Private Function ReconnectTables() As Boolean
On Error Resume Next

    Dim tdf As DAO.TableDef
    Dim dbs As DAO.Database
    Dim strPath As String
    Dim strConnect As String

    Set dbs = CurrentDb
    strConnect = "\\dfs\prd\departmentX\DepartmentalApplicationX_be.accdb"
    For Each tdf In dbs.TableDefs
        If tdf.Connect <> "" Then
            tdf.Connect = ";DATABASE=" & strConnect
            tdf.RefreshLink
        End If
    Next

    Set dbs = Nothing
    If Err.Number = 0 Then ReconnectTables = True

End Function

This works fine when used by a single user, but hangs (takes approx. 5 minutes ) as soon as a second user tries to access this.

Is there any way I can avoid this? It looks as if I have two options:

  1. Improve performance of relinking, if possible. How would this be done?
  2. Completely avoid relinking tables as soon as the application is release to production. I am not sure if that’s advisable at all ? If I stop relinking tables each time, would it still be possible to detect the need to relink somehow?

Thanks for sharing your wisdom !

Upvotes: 2

Views: 562

Answers (1)

Doc Brown
Doc Brown

Reputation: 20044

Try this: 

Set dbs = CurrentDb
strConnect = ";DATABASE=\\dfs\prd\departmentX\DepartmentalApplicationX_be.accdb"
For Each tdf In dbs.TableDefs
    If tdf.Connect <> "" and tdf.Connect<> strConnect Then
        tdf.Connect = strConnect
        tdf.RefreshLink
    End If
Next

So the reconnect should only happen if necessary.

Upvotes: 2

Related Questions