Multiusers "hang"

[spikepl]

A2007, WinXp.

UserA: Local frontend, Backend in local folder that is shared

User B: Local frontend, linked to the shared folder on User A's machine

Problem:

• User A starts up FE first. When User B starts up, Access hangs, but opens if User A closes FE. (I do not know specifically where it hangs - all this is by phone)
• If User B starts up FE first, User A can startup his FE without problem.

Shared access is ticked in both FE's and the BE.

On startup, each FE reads the location of the BE from a local .INI file and runs this code:

Public Function LinkTableDefs()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    If gcfHandleErrors Then On Error GoTo LinkTableDefs_Error

    LinkTableDefs = False

    If Dir(TempVars("BE_Location").Value) <> "" Then

        Set dbs = CurrentDb()

        ' Loop through TableDefs collection, only processing ' the table if it already has a Connection property. ' (all other tables are local ... not linked)

        For Each tdf In dbs.TableDefs
            If tdf.Connect <> "" Then
                If tdf.Connect <> ";DATABASE=" & LinkTableDefs Then
                    tdf.Connect = ";DATABASE=" & TempVars("BE_Location").Value
                    tdf.RefreshLink
                End If
            End If
        Next
        
        LinkTableDefs = True

    Else
    
        MsgBox "Could not connect to " & TempVars("BE_Location").Value & vbCrLf & _
        "Check location and content of .INI file and the network", vbInformation

    End If
    
LinkTableDefs_Exit:

    On Error Resume Next


    Exit Function

LinkTableDefs_Error:

    Select Case Err.Number

        Case Else

            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure LinkTableDefs of Module Relinker"

            Resume LinkTableDefs_Exit

    End Select
End Function

 

[VilaRestal]

Shouldn't it be

If tdf.Connect <> ";DATABASE=" & TempVars("BE_Location").Value Then

rather than

If tdf.Connect <> ";DATABASE=" & LinkTableDefs Then

which would be a recurssive call to the LinkTableDefs function from within the LinkTableDefs function

 

[spikepl]

Hehe. Of course this is a blunder, thanks for that. But I guess this doesn't change anything in that the clause is always true, and the app runs as it should, apart from the "hanging" bit.

On reflection: have to have a look into what goes on here. You are probably right. But I fail to see how it explains the "hanging".

Next week I can debug the app remotely to find out where it hangs, but I am just fishing for some ideas until then.

The sequence of startup having such different outcomes confuses me. Could it have to do with some read/write/modify permissions for UserB on UserA's machine messing with the lock file?

 

[VilaRestal]

It hangs surely because it's recursive, it's an infinite loop.

It's not using LinkTableDefs as a variable with an assigned value it's using the function.

I just don't get why it doesn't hang for every user in every situation :s

 

[spikepl]

If User B who has to link to BE on the other machine starts up first then it did run in the sense it managed to relink with the correct BE (that has been verified). A miracle

Will have a look next week.

 

[spikepl]

Writing the code correctly solved the hanging issue!

 

[LucentPeregrine]

Hiya spikepl,

I was wondering if you could post the final working code for this as it sounds exactly what I need to do.
Found this post while googling about looking for suggestions, I'm sure it would be really useful for many many people.
Looks like a pretty big task to make from scratch.

Cheers

Lucent

 

[spikepl]

Public Function LinkTableDefs()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    If gcfHandleErrors Then On Error GoTo LinkTableDefs_Error

    LinkTableDefs = False

    If Dir(TempVars("BE_Location").Value) <> "" Then

        Set dbs = CurrentDb()

        ' Loop through TableDefs collection, only processing ' the table if it already has a Connection property. ' (all other tables are local ... not linked)

        For Each tdf In dbs.TableDefs
            If tdf.Connect <> "" Then
                If tdf.Connect <> ";DATABASE=" & TempVars("BE_Location").Value Then
                    tdf.Connect = ";DATABASE=" & TempVars("BE_Location").Value
                    tdf.RefreshLink
                End If
            End If
        Next
        
        LinkTableDefs = True

    Else
    
        MsgBox "Could not connect to " & TempVars("BE_Location").Value & vbCrLf & _
        "Check location and content of .INI file and the network", vbInformation

    End If
    
LinkTableDefs_Exit:

    On Error Resume Next


    Exit Function

LinkTableDefs_Error:

    Select Case Err.Number

        Case Else

            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure LinkTableDefs of Module Relinker"

            Resume LinkTableDefs_Exit

    End Select
End Function

 

[Angel69]

I'd like to implement this on my front end. I work on the front end on my desktop and change the link to my desk top back end copy in order to speed things up as it's really slow when I am linked to the shared drive. I forget to relink it to the back end on the shared drive and users are calling me.

Where do I put this code? On the On Open of the dbase? Also, does this code tell the user there is an error or does it relink to the correct drive and only give an error if the drive is not available? TIA