1

In the following procedure, conditions are such that everything above the numbered lines is not executed; however, I suspect that if conditions were such that the code entered into one of those If or ElseIf blocks, the same line(s) would produce the same error.

Public Function Reconnect_Linked_Tables()
'CHECK TABLE LINKS AND RESTORE LINKS IF NECESSARY IN PRIORITY OF NETWORK BACK-END, LOCAL BACK-END, BACK-END IN THE SAME FOLDER AS THE OPEN FRONT-END, & FINALLY THE CURRENTLY LINKED
'BACK-END (IF NEITHER NETWORK NOR LOCAL NOR SAME FOLDER)
'IF AN ERROR OCCURS, MOVE TO & PROCESS THE 'ErrorHandler:' PROCESS
On Error GoTo ErrorHandler
    'PRINT PROCEDURE START MESSAGE TO IMMEDIATE WINDOW
    Debug.Print "Reconnect_Linked_Tables (modUtilities) - START:"
    Debug.Print ""
    'DECLARE AND/OR SET VARIABLES NEEDED FOR THE PROCEDURE
    Set db = CurrentDb
    intTablesLinkedTo = 0
    intRelinkedTableCount = 0
    intTableLinkStatus = 0
    'IF THE NETWORK FILE PATH IN FIELD 'Network_Drive_BackEnd_Path' IN TABLE 'tblAdminOptions' DOES EXIST, SET 'blnNetworkDriveBackEndExists' TO 'TRUE' & RESTORE NETWORK TABLE LINKS (PRIORITY ONE)
    If Check_Network_Drive_For_BackEnd = True Then
        'PRINT TO IMMEDIATE WINDOW
        Debug.Print "Check_Network_Drive_For_BackEnd = True"
        Debug.Print ""
        'SET 'blnNetworkDriveBackEndExists' TO 'TRUE'
        blnNetworkDriveBackEndExists = True
        'PRINT TO IMMEDIATE WINDOW
        Debug.Print "Network Back-end File Exists...Connecting"
        Debug.Print ""
        'COUNT THE NUMBER OF RELINKED TABLES IN THE DATABASE, SAVE THE RESULT TO 'intReLinkedTableCount', SAVE THE LINKED TABLE NAME TO 'strTableName', SET 'intTableLinkStatus' to 1,
        '& SAVE THE CURRENT LINKED TABLE PATH TO 'strCurrentTableLinkBackEndPath'
        For i = 0 To db.TableDefs.Count - 1
            If db.TableDefs(i).Connect <> "" Then
                strCurrentTableLinkBackEndPath = Mid(db.TableDefs(i).Connect, 11)
                'IF THE CURRENT LINKED TABLE PATH DOES NOT MATCH THE NETWORK FILE PATH & SINCE THE NETWORK FILE PATH EXISTS, RELINK TABLES TO THE NETWORK FILE PATH
                If strCurrentTableLinkBackEndPath <> strNetworkDriveBackEndPath Then
                    db.TableDefs(i).Connect = ";Database=" + strNetworkDriveBackEndPath
                    db.TableDefs(i).RefreshLink
                    strTableName = db.TableDefs(i).SourceTableName
                    intRelinkedTableCount = intRelinkedTableCount + 1
                    ''intTableLinkStatus' POSSIBLE VALUES:
                        '1 = NETWORK
                        '2 = LOCAL
                        '3 = SAME FOLDER
                        '4 = CURRENT
                        '5 = ERROR
                    intTableLinkStatus = 1
                    'PRINT TO IMMEDIATE WINDOW
                    Debug.Print "[" & intRelinkedTableCount & "] Table " & strTableName & " connected to back-end file '" & strNetworkDriveBackEndPath & "'"
                'IF THE CURRENT LINKED TABLE PATH MATCHES THE NETWORK FILE PATH, THERE IS NOTHING FURTHER TO DO HERE BUT INCREMENT 'intRelinkedTableCount' BY ZERO (0)
                '& SET 'intTableLinkStatus' TO ZERO (0)
                ElseIf strCurrentTableLinkBackEndPath = strNetworkDriveBackEndPath Then
                    intRelinkedTableCount = intRelinkedTableCount + 0
                    ''intTableLinkStatus' POSSIBLE VALUES:
                        '1 = NETWORK
                        '2 = LOCAL
                        '3 = SAME FOLDER
                        '4 = CURRENT
                        '5 = ERROR
                    intTableLinkStatus = 0
                    Exit For
                End If
            End If
        Next
        'PRINT OLD & NEW LINKED TABLE PATHS TO IMMEDIATE WINDOW
        Debug.Print "Old Linked Table Path: " & strCurrentTableLinkBackEndPath
        Debug.Print "New Linked Table Path: " & strNetworkDriveBackEndPath
        Debug.Print ""
        'SET VALUES FOR 'strTablesLinkedTo' & 'intTablesLinkedTo'
        ''intTablesLinkedTo' POSSIBLE VALUES:
            '1 = NETWORK
            '2 = LOCAL
            '3 = SAME FOLDER
            '4 = CURRENT
            '5 = ERROR
        strTablesLinkedTo = "Network Table Links (Priority One)"
        intTablesLinkedTo = 1
        'EXIT THE PROCEDURE, SINCE WE ARE NOW CONNECTED TO THE NETWORK BACK END
        GoTo CleanExit
    'IF THE NETWORK FILE PATH IN FIELD 'Network_Drive_BackEnd_Path' IN TABLE 'tblAdminOptions' DOES NOT EXIST BUT THE LOCAL FILE PATH IN FIELD
    ''Local_Drive_BackEnd_Path' IN TABLE 'tblAdminOptions' DOES EXIST, RESTORE LOCAL TABLE LINKS (PRIORITY TWO)
    ElseIf blnNetworkDriveBackEndExists = False And Check_Local_Drive_For_BackEnd = True Then
        'PRINT TO IMMEDIATE WINDOW
        Debug.Print "blnNetworkDriveBackEndExists = False And Check_Local_Drive_For_BackEnd = True"
        Debug.Print ""
        'SET 'blnLocalDriveBackEndExists' TO 'TRUE'
        blnLocalDriveBackEndExists = True
        'PRINT TO IMMEDIATE WINDOW
        Debug.Print "Network Back-end File Does Not Exist"
        Debug.Print "Local Back-end File Exists...Connecting"
        Debug.Print ""
        'COUNT THE NUMBER OF RELINKED TABLES IN THE DATABASE, SAVE THE RESULT TO 'intReLinkedTableCount', SAVE THE LINKED TABLE NAME TO 'strTableName', SET 'intTableLinkStatus' to 2,
        '& SAVE THE CURRENT LINKED TABLE PATH TO 'strCurrentTableLinkBackEndPath'
        For i = 0 To db.TableDefs.Count - 1
            If db.TableDefs(i).Connect <> "" Then
                strCurrentTableLinkBackEndPath = Mid(db.TableDefs(i).Connect, 11)
                'IF THE CURRENT LINKED TABLE PATH DOES NOT MATCH THE LOCAL FILE PATH & SINCE THE NETWORK FILE PATH DOES NOT EXIST & THE LOCAL FILE PATH EXISTS,
                'RELINK TABLES TO THE LOCAL FILE PATH
                If strCurrentTableLinkBackEndPath <> strLocalDriveBackEndPath Then
                    db.TableDefs(i).Connect = ";Database=" + strLocalDriveBackEndPath
                    db.TableDefs(i).RefreshLink
                    strTableName = db.TableDefs(i).SourceTableName
                    intRelinkedTableCount = intRelinkedTableCount + 1
                    ''intTableLinkStatus' POSSIBLE VALUES:
                        '1 = NETWORK
                        '2 = LOCAL
                        '3 = SAME FOLDER
                        '4 = CURRENT
                        '5 = ERROR
                    intTableLinkStatus = 2
                    'PRINT TO IMMEDIATE WINDOW
                    Debug.Print "[" & intRelinkedTableCount & "] Table " & strTableName & " connected to back-end file '" & strLocalDriveBackEndPath & "'"
                    Debug.Print ""
                'IF THE CURRENT LINKED TABLE PATH MATCHES THE LOCAL FILE PATH, THERE IS NOTHING FURTHER TO DO HERE BUT INCREMENT 'intRelinkedTableCount' BY ZERO (0)
                '& SET 'intTableLinkStatus' TO ZERO (0)
                ElseIf strCurrentTableLinkBackEndPath = strLocalDriveBackEndPath Then
                    intRelinkedTableCount = intRelinkedTableCount + 0
                    ''intTableLinkStatus' POSSIBLE VALUES:
                        '1 = NETWORK
                        '2 = LOCAL
                        '3 = SAME FOLDER
                        '4 = CURRENT
                        '5 = ERROR
                    intTableLinkStatus = 0
                    Exit For
                End If
            End If
        Next
        'PRINT OLD & NEW LINKED TABLE PATHS TO IMMEDIATE WINDOW
        Debug.Print "Old Linked Table Path: " & strCurrentTableLinkBackEndPath
        Debug.Print "New Linked Table Path: " & strLocalDriveBackEndPath
        Debug.Print ""
        'SET VALUES FOR 'strTablesLinkedTo' & 'intTablesLinkedTo'
        'intTablesLinkedTo POSSIBLE VALUES:
            '1 = NETWORK
            '2 = LOCAL
            '3 = SAME FOLDER
            '4 = CURRENT
            '5 = ERROR
        strTablesLinkedTo = "Local Table Links (Priority Two)"
        intTablesLinkedTo = 2
        'EXIT THE PROCEDURE, SINCE WE ARE NOW CONNECTED TO THE LOCAL BACK END
        GoTo CleanExit
    'IF THE NETWORK FILE PATH IN FIELD 'Network_Drive_BackEnd_Path' IN TABLE 'tblAdminOptions' DOES NOT EXIST AND THE LOCAL FILE PATH IN FIELD 'Local_Drive_BackEnd_Path'
    'IN TABLE 'tblAdminOptions' DOES NOT EXIST, CHECK FOR A BACK-END FILE IN THE SAME FOLDER AS THE OPEN FRONT-END (PRIORITY THREE)
    ElseIf blnNetworkDriveBackEndExists = False And blnLocalDriveBackEndExists = False And Check_Same_Folder_For_BackEnd = True Then
100        'PRINT TO IMMEDIATE WINDOW
105        Debug.Print "blnNetworkDriveBackEndExists = False And blnLocalDriveBackEndExists = False And Check_Same_Folder_For_BackEnd = True"
110        Debug.Print ""
115        'SET 'blnSameFolderBackEndExists' TO 'TRUE'
120        blnSameFolderBackEndExists = True
125        'PRINT TO IMMEDIATE WINDOW
130        Debug.Print "Neither Network Nor Local Back-end Files Exist"
135        Debug.Print "Same Folder Back-end File Exists...Connecting"
140        Debug.Print ""
145        'COUNT THE NUMBER OF RELINKED TABLES IN THE DATABASE, SAVE THE RESULT TO 'intReLinkedTableCount', SAVE THE LINKED TABLE NAME TO 'strTableName', SET 'intTableLinkStatus' to 3,
150        '& SAVE THE CURRENT LINKED TABLE PATH TO 'strCurrentTableLinkBackEndPath'
155        For i = 0 To db.TableDefs.Count - 1
160            If db.TableDefs(i).Connect <> "" Then
165                strCurrentTableLinkBackEndPath = Mid(db.TableDefs(i).Connect, 11)
170                'IF THE CURRENT LINKED TABLE PATH DOES NOT MATCH THE SAME FOLDER FILE PATH & SINCE THE NETWORK & LOCAL FILE PATHS DO NOT EXIST & THE SAME FOLDER FILE PATH EXISTS,
175                'RELINK TABLES TO THE SAME FOLDER FILE PATH
180                If strCurrentTableLinkBackEndPath <> strSameFolderBackEndPath Then
185                    db.TableDefs(i).Connect = ";Database=" + strSameFolderBackEndPath
190                    db.TableDefs(i).RefreshLink
195                    strTableName = db.TableDefs(i).SourceTableName
200                    intRelinkedTableCount = intRelinkedTableCount + 1
205                    ''intTableLinkStatus' POSSIBLE VALUES:
210                        '1 = NETWORK
215                        '2 = LOCAL
220                        '3 = SAME FOLDER
225                        '4 = CURRENT
230                        '5 = ERROR
235                    intTableLinkStatus = 3
240                    'PRINT TO IMMEDIATE WINDOW
245                    Debug.Print "[" & intRelinkedTableCount & "] Table " & strTableName & " connected to back-end file '" & strSameFolderBackEndPath & "'"
250                    Debug.Print ""
255                'IF THE CURRENT LINKED TABLE PATH MATCHES THE SAME FOLDER FILE PATH, THERE IS NOTHING FURTHER TO DO HERE BUT INCREMENT 'intRelinkedTableCount' BY ZERO (0)
260                '& SET 'intTableLinkStatus' TO ZERO (0)
265                ElseIf strCurrentTableLinkBackEndPath = strSameFolderBackEndPath Then
270                    intRelinkedTableCount = intRelinkedTableCount + 0
275                    ''intTableLinkStatus' POSSIBLE VALUES:
280                        '1 = NETWORK
285                        '2 = LOCAL
290                        '3 = SAME FOLDER
295                        '4 = CURRENT
300                        '5 = ERROR
305                    intTableLinkStatus = 0
310                    Exit For
315                End If
320            End If
325        Next
330        'RUN THE 'Check_Network_Drive_For_BackEnd' PROCEDURE AGAIN NOW THAT WE ARE RELINKED TO A BACK-END TO DETERMINE WHETHER THE SAME FOLDER PATH IS THE SAME AS THE NETWORK FILE PATH
335        Call Check_Network_Drive_For_BackEnd
340        'RUN THE 'Check_Local_Drive_For_BackEnd' PROCEDURE AGAIN NOW THAT WE ARE RELINKED TO A BACK-END TO DETERMINE WHETHER THE SAME FOLDER PATH IS THE SAME AS THE LOCAL FILE PATH
345        Call Check_Local_Drive_For_BackEnd
350        'IF THE SAME FOLDER PATH MATCHES THE NETWORK FILE PATH, SET VALUES FOR 'strTablesLinkedTo' & 'intTablesLinkedTo'
355        If strSameFolderBackEndPath = strNetworkDriveBackEndPath Then
360            'PRINT OLD & NEW LINKED TABLE PATHS TO IMMEDIATE WINDOW
365            Debug.Print "Old Linked Table Path: " & strCurrentTableLinkBackEndPath
370            Debug.Print "New Linked Table Path: " & strSameFolderBackEndPath
375            Debug.Print ""
380            'SET VALUES FOR 'strTablesLinkedTo' & 'intTablesLinkedTo'
385            ''intTablesLinkedTo' POSSIBLE VALUES:
390                '1 = NETWORK
395                '2 = LOCAL
400                '3 = SAME FOLDER
405                '4 = CURRENT
410                '5 = ERROR
415            strTablesLinkedTo = "Network Table Links (Priority One)"
420            intTablesLinkedTo = 1
425        'IF THE SAME FOLDER PATH MATCHES THE LOCAL FILE PATH, SET VALUES FOR 'strTablesLinkedTo' & 'intTablesLinkedTo'
430        ElseIf strSameFolderBackEndPath = strLocalDriveBackEndPath Then
435            'PRINT OLD & NEW LINKED TABLE PATHS TO IMMEDIATE WINDOW
440            Debug.Print "Old Linked Table Path: " & strCurrentTableLinkBackEndPath
445            Debug.Print "New Linked Table Path: " & strSameFolderBackEndPath
450            Debug.Print ""
455            'SET VALUES FOR 'strTablesLinkedTo' & 'intTablesLinkedTo'
460            ''intTablesLinkedTo' POSSIBLE VALUES:
465                '1 = NETWORK
470                '2 = LOCAL
475                '3 = SAME FOLDER
480                '4 = CURRENT
485                '5 = ERROR
490            strTablesLinkedTo = "Local Table Links (Priority Two)"
495            intTablesLinkedTo = 2
500        'IF THE SAME FOLDER PATH DOES NOT MATCH THE NETWORK FILE PATH OR THE LOCAL FILE PATH, SET VALUES FOR 'strTablesLinkedTo' & 'intTablesLinkedTo'
505        ElseIf (strSameFolderBackEndPath <> strNetworkDriveBackEndPath) And (strSameFolderBackEndPath <> strLocalDriveBackEndPath) Then
510            'PRINT OLD & NEW LINKED TABLE PATHS TO IMMEDIATE WINDOW
515            Debug.Print "Old Linked Table Path: " & strCurrentTableLinkBackEndPath
520            Debug.Print "New Linked Table Path: " & strSameFolderBackEndPath
525            Debug.Print ""
530            'SET VALUES FOR 'strTablesLinkedTo' & 'intTablesLinkedTo'
535            ''intTablesLinkedTo' POSSIBLE VALUES:
540                '1 = NETWORK
545                '2 = LOCAL
550                '3 = SAME FOLDER
555                '4 = CURRENT
560                '5 = ERROR
565            strTablesLinkedTo = "Same Folder Table Links (Priority Three)"
570            intTablesLinkedTo = 3
575        End If
580        'EXIT THE PROCEDURE, SINCE WE ARE NOW CONNECTED TO THE SAME FOLDER BACK END
585        GoTo CleanExit
    'IF THE NETWORK FILE PATH IN FIELD 'Network_Drive_BackEnd_Path' IN TABLE 'tblAdminOptions' DOES NOT EXIST, THE LOCAL FILE PATH IN FIELD 'Local_Drive_BackEnd_Path'
    'IN TABLE 'tblAdminOptions' DOES NOT EXIST, & A BACK-END FILE IN THE SAME FOLDER AS THE OPEN FRONT-END DOES NOT EXIST, CHECK THE CURRENT TABLE LINK PATH FOR
    'VALIDITY & RETAIN TABLE LINKS (PRIORITY FOUR)
    ElseIf blnNetworkDriveBackEndExists = False And blnLocalDriveBackEndExists = False And blnSameFolderBackEndExists = False And Check_Current_Table_Link_Paths_For_BackEnd = True Then
        'PRINT TO IMMEDIATE WINDOW
        Debug.Print "blnNetworkDriveBackEndExists = False And blnLocalDriveBackEndExists = False And blnSameFolderBackEndExists = False And Check_Current_Table_Link_Paths_For_BackEnd = True"
        Debug.Print ""
        'SET 'blnCurrentlyLinkedBackEndExists' TO 'TRUE'
        blnCurrentlyLinkedBackEndExists = True
        'PRINT TO IMMEDIATE WINDOW
        Debug.Print "Neither Network Nor Local Nor Same Folder Back-end Files Exist"
        Debug.Print "Currently Linked Back-end File Exists...Retaining Links"
        Debug.Print ""
        'SAVE THE CURRENT LINKED TABLE PATH TO 'strCurrentTableLinkBackEndPath' & SET 'intTableLinkStatus' to 4
        For i = 0 To db.TableDefs.Count - 1
            If db.TableDefs(i).Connect <> "" Then
                strCurrentTableLinkBackEndPath = Mid(db.TableDefs(i).Connect, 11)
                ''intTableLinkStatus' POSSIBLE VALUES:
                    '1 = NETWORK
                    '2 = LOCAL
                    '3 = SAME FOLDER
                    '4 = CURRENT
                    '5 = ERROR
                intTableLinkStatus = 4
            End If
        Next
        'RUN THE 'Check_Network_Drive_For_BackEnd' PROCEDURE AGAIN NOW THAT WE ARE RELINKED TO A BACK-END TO DETERMINE WHETHER THE SAME FOLDER PATH IS THE SAME AS THE NETWORK FILE PATH
        Call Check_Network_Drive_For_BackEnd
        'RUN THE 'Check_Local_Drive_For_BackEnd' PROCEDURE AGAIN NOW THAT WE ARE RELINKED TO A BACK-END TO DETERMINE WHETHER THE SAME FOLDER PATH IS THE SAME AS THE LOCAL FILE PATH
        Call Check_Local_Drive_For_BackEnd
        'IF THE SAME FOLDER PATH MATCHES THE NETWORK FILE PATH, SET VALUES FOR 'strTablesLinkedTo' & 'intTablesLinkedTo'
        If strCurrentTableLinkBackEndPath = strNetworkDriveBackEndPath Then
            'PRINT OLD & NEW LINKED TABLE PATHS TO IMMEDIATE WINDOW
            Debug.Print "Old Linked Table Path: " & strCurrentTableLinkBackEndPath
            Debug.Print "New Linked Table Path: " & strSameFolderBackEndPath
            Debug.Print ""
            'SET VALUES FOR 'strTablesLinkedTo' & 'intTablesLinkedTo'
            ''intTablesLinkedTo' POSSIBLE VALUES:
                '1 = NETWORK
                '2 = LOCAL
                '3 = SAME FOLDER
                '4 = CURRENT
                '5 = ERROR
            strTablesLinkedTo = "Network Table Links (Priority One)"
            intTablesLinkedTo = 1
        'IF THE SAME FOLDER PATH MATCHES THE LOCAL FILE PATH, SET VALUES FOR 'strTablesLinkedTo' & 'intTablesLinkedTo'
        ElseIf strCurrentTableLinkBackEndPath = strLocalDriveBackEndPath Then
            'PRINT OLD & NEW LINKED TABLE PATHS TO IMMEDIATE WINDOW
            Debug.Print "Old Linked Table Path: " & strCurrentTableLinkBackEndPath
            Debug.Print "New Linked Table Path: " & strSameFolderBackEndPath
            Debug.Print ""
            'SET VALUES FOR 'strTablesLinkedTo' & 'intTablesLinkedTo'
            ''intTablesLinkedTo' POSSIBLE VALUES:
                '1 = NETWORK
                '2 = LOCAL
                '3 = SAME FOLDER
                '4 = CURRENT
                '5 = ERROR
            strTablesLinkedTo = "Local Table Links (Priority Two)"
            intTablesLinkedTo = 2
        'IF THE SAME FOLDER PATH DOES NOT MATCH THE NETWORK FILE PATH OR THE LOCAL FILE PATH, SET VALUES FOR 'strTablesLinkedTo' & 'intTablesLinkedTo'
        ElseIf (strCurrentTableLinkBackEndPath <> strNetworkDriveBackEndPath) And (strCurrentTableLinkBackEndPath <> strLocalDriveBackEndPath) Then
            'PRINT OLD & NEW LINKED TABLE PATHS TO IMMEDIATE WINDOW
            Debug.Print "Old Linked Table Path: " & strCurrentTableLinkBackEndPath
            Debug.Print "New Linked Table Path: " & strSameFolderBackEndPath
            Debug.Print ""
            'SET VALUES FOR 'strTablesLinkedTo' & 'intTablesLinkedTo'
            ''intTablesLinkedTo' POSSIBLE VALUES:
                '1 = NETWORK
                '2 = LOCAL
                '3 = SAME FOLDER
                '4 = CURRENT
                '5 = ERROR
            strTablesLinkedTo = "Current Table Links (Priority Four)"
            intTablesLinkedTo = 4
        End If
        'EXIT THE PROCEDURE, SINCE WE ARE NOW CONNECTED TO THE CURRENT BACK END
        GoTo CleanExit
    'IF THE NETWORK FILE PATH IN FIELD 'Network_Drive_BackEnd_Path' IN TABLE 'tblAdminOptions' DOES NOT EXIST, THE LOCAL FILE PATH IN FIELD 'Local_Drive_BackEnd_Path'
    'IN TABLE 'tblAdminOptions' DOES NOT EXIST, A BACK-END DOES NOT EXIST IN THE SAME FOLDER AS THE OPEN FRONT-END, & THE CURRENT TABLE LINK PATH DOES NOT EXIST,
    'A DATABASE ERROR HAS OCCURED (PRIORITY FIVE)
    ElseIf blnNetworkDriveBackEndExists = False And blnLocalDriveBackEndExists = False And blnSameFolderBackEndExists = False And blnCurrentlyLinkedBackEndExists = False Then
        'PRINT TO IMMEDIATE WINDOW
        Debug.Print "blnNetworkDriveBackEndExists = False And blnLocalDriveBackEndExists = False And blnSameFolderBackEndExists = False And blnCurrentlyLinkedBackEndExists = False"
        Debug.Print ""
        'PRINT TO IMMEDIATE WINDOW
        Debug.Print "Neither Network Nor Local Nor Same Folder Nor Currently Linked Back-end Files Exist"
        Debug.Print ""
        'PRINT TO IMMEDIATE WINDOW
        Debug.Print "No Back-end Files Exist; a Database Error Has Occurred"
        Debug.Print ""
        'SET VALUES FOR 'strTablesLinkedTo', 'intTablesLinkedTo', & 'intTableLinkStatus'
        ''intTablesLinkedTo' & 'intTableLinkStatus' POSSIBLE VALUES:
            '1 = NETWORK
            '2 = LOCAL
            '3 = SAME FOLDER
            '4 = CURRENT
            '5 = ERROR
        strTablesLinkedTo = "Error (Priority Five) - There are no accessible tables to which to establish links"
        intTablesLinkedTo = 5
        intTableLinkStatus = 5
        'PRINT TO IMMEDIATE WINDOW
        Debug.Print "Tables Linked to: '" & intTablesLinkedTo & "' | '" & strTablesLinkedTo & "'"
        Debug.Print ""
        'EXIT THE PROCEDURE; THERE WERE NO TABLES TO LINK TO, SO A DATABASE ERROR HAS OCCURED
        GoTo CleanExit
    End If
    'EXIT THE PROCEDURE; THIS COMMAND SHOULD NEVER BE REACHED, SINCE EACH OF THE IF/THEN STATEMENTS END IN 'GoTo CleanExit'
    GoTo CleanExit
ErrorHandler:
    'IF ANY ERRORS OCCUR, PRINT DETAILS TO IMMEDIATE WINDOW
    Debug.Print "---------- ERROR ----------"
    Debug.Print "Line:        " & Erl
    Debug.Print "Description: " & Err.Description
    Debug.Print "Number:      " & Err.Number
    Debug.Print "Source:      " & Err.Source
    Debug.Print "---------- ERROR ----------"
    Debug.Print ""
    'REF: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/resume-statement
    'CONTINUE PROCEDURE FROM THE LINE AFTER THE LINE THAT CAUSED THE LAST ERROR
    Resume
CleanExit:
    'CLEAN UP
    i = Empty
    intTablesLinkedTo = Empty
    intRelinkedTableCount = Empty
    intTableLinkStatus = Empty
    db.Close
    Set db = Nothing
    blnNetworkDriveBackEndExists = Empty
    strNetworkDriveBackEndPath = Empty
    strCurrentTableLinkBackEndPath = Empty
    strTableName = Empty
    strTablesLinkedTo = Empty
    blnLocalDriveBackEndExists = Empty
    strLocalDriveBackEndPath = Empty
    blnSameFolderBackEndExists = Empty
    strSameFolderBackEndPath = Empty
    blnCurrentlyLinkedBackEndExists = Empty
    'PRINT TO IMMEDIATE WINDOW
    Debug.Print "Network: " & blnNetworkDriveBackEndExists & " | Local: " & blnLocalDriveBackEndExists & " | Same Folder: " & blnSameFolderBackEndExists & " | Current: " & blnCurrentlyLinkedBackEndExists
    Debug.Print ""
    'PRINT TO IMMEDIATE WINDOW
    Debug.Print "LnkTo | LnkCt | LnkSt"
    Debug.Print " " & Format(intTablesLinkedTo, "000") & "  |  " & Format(intRelinkedTableCount, "000") & "  |  " & Format(intTableLinkStatus, "000")
    Debug.Print ""
    'PRINT PROCEDURE END MESSAGE TO IMMEDIATE WINDOW
    Debug.Print "Reconnect_Linked_Tables (modUtilities) - END"
    Debug.Print ""
End Function

The offending line is 155, which produces error 91 “Object variable or With block variable not set”.

Well, the ‘db’ variable is declared as Public at the top of the module and Set to “CurrentDb” in the above procedure, so I am at a loss as to what the compiler is looking for here. Option Explicit is also set at the top of the module.

Where am I going wrong here?

2 Spice ups
2

Hi @donaldfisher3 ​, that’s a big chunk of code! I asked AI and this is the result:

Your VBA function Reconnect_Linked_Tables is a comprehensive procedure designed to check and restore links to tables in a Microsoft Access database based on the availability of back-end databases across different locations. It prioritizes network paths over local, same-folder, and currently linked paths. The function includes error handling and detailed logging to the Immediate Window to aid in debugging and ensuring the reliability of database table links.

Here are a few suggestions to improve the function:

  1. Variable Declaration and Option Explicit: Ensure all variables are explicitly declared to avoid runtime errors due to typos. Use Option Explicit at the top of your module to enforce variable declaration.
  2. Error Handling Enhancements: The ErrorHandler section could be improved by including a Resume Next statement for continuing execution after logging an error, if that’s appropriate for your use case. Otherwise, clarify the intended error recovery strategy.
  3. Use of Global Variables: The function seems to rely on several global variables (e.g., blnNetworkDriveBackEndExists, strNetworkDriveBackEndPath). Consider passing these as parameters or returning them from functions to reduce dependencies and enhance modularity.
  4. Repeated Code Blocks: The code for checking the back-end file existence and relinking tables is repeated for network, local, and same-folder scenarios. This could be refactored into a separate function that accepts the path type (network, local, same-folder) as a parameter to reduce code duplication.
  5. Dynamic Path Checking: The function assumes the existence of certain paths (network, local, same-folder) without dynamically checking them. While the function stubs (Check_Network_Drive_For_BackEnd, Check_Local_Drive_For_BackEnd, etc.) suggest these checks are made, ensure that these methods robustly verify path availability, possibly catching exceptions for unreachable paths.
  6. Database Connection Optimizations: Repeatedly opening and closing the database connection or manipulating table definitions in a loop might be inefficient, especially for a large number of tables. Consider optimizing this aspect by minimizing the number of times the database connection is opened/closed and batching operations wherever possible.
  7. Modular Design: Break down the function into smaller, more focused subroutines. For example, separate the logic for determining which back-end path to use from the logic for actually relinking the tables.
  8. Comments and Documentation: While the function is well-commented, ensuring that these comments are up-to-date and accurately describe the logic and any changes is crucial for maintainability. Consider adding a header comment describing the purpose, parameters, return values, and any side effects.
  9. Validation and Feedback: Provide feedback to the user or calling process about the success or failure of the operation, possibly through a message box or a return value indicating the outcome.

Given the complexity of the procedure and without the full context (like the implementation of the Check_Network_Drive_For_BackEnd function and others), these suggestions aim to guide towards general best practices. If specific implementations for path checking and relinking are needed, consider implementing them in a way that encapsulates their functionality cleanly and reuses code efficiently.

Option Explicit

' This function attempts to reconnect linked tables based on the availability of back-end databases
' in a priority order: Network, Local, Same Folder, and finally the currently linked back-end.
Public Function Reconnect_Linked_Tables() As String
    On Error GoTo ErrorHandler
    Dim db As Database
    Dim linkType As String
    Dim relinkedCount As Integer
    
    Set db = CurrentDb
    relinkedCount = 0 ' Initialize the count of relinked tables
    
    ' Determine the best available back-end path and its type
    Dim backendPath As String
    backendPath = DetermineBackendPath(linkType)
    
    If backendPath <> "" Then
        ' Relink tables if a valid backend path is found
        relinkedCount = RelinkTables(db, backendPath)
    Else
        linkType = "Error - No accessible back-end found."
    End If
    
    ' Construct the return message with the outcome of the reconnection process
    Reconnect_Linked_Tables = linkType & " - Tables relinked: " & relinkedCount
    GoTo CleanExit
    
ErrorHandler:
    ' Error handling block, capturing and logging any errors that occur
    Debug.Print "Error " & Err.Number & ": " & Err.Description & " in Reconnect_Linked_Tables"
    Resume CleanExit

CleanExit:
    ' Cleanup operations and setting the db object to Nothing
    Set db = Nothing
End Function

' DetermineBackendPath checks for the availability of different back-end paths
' and returns the first valid path along with its type as a string reference parameter.
Private Function DetermineBackendPath(ByRef linkType As String) As String
    ' Check each potential back-end location in priority order
    If CheckPath("Network") Then
        linkType = "Network"
        DetermineBackendPath = GetPath("Network")
    ElseIf CheckPath("Local") Then
        linkType = "Local"
        DetermineBackendPath = GetPath("Local")
    ElseIf CheckPath("SameFolder") Then
        linkType = "Same Folder"
        DetermineBackendPath = GetPath("SameFolder")
    Else
        ' No valid back-end found
        DetermineBackendPath = ""
    End If
End Function

' RelinkTables iterates through all table definitions in the database
' and attempts to relink those that are linked tables to a new back-end path.
' Returns the count of successfully relinked tables.
Private Function RelinkTables(db As Database, ByVal newPath As String) As Integer
    Dim tableDef As TableDef
    Dim relinkedCount As Integer: relinkedCount = 0
    
    For Each tableDef In db.TableDefs
        If Left(tableDef.Connect, 10) = ";DATABASE=" Then
            ' Attempt to relink the table to the new path
            If RelinkTable(tableDef, newPath) Then relinkedCount = relinkedCount + 1
        End If
    Next tableDef
    
    RelinkTables = relinkedCount
End Function

' RelinkTable updates the connection string for a single linked table definition
' to a new back-end database path and refreshes the link.
' Returns True if successful, False otherwise.
Private Function RelinkTable(tableDef As TableDef, ByVal newPath As String) As Boolean
    On Error GoTo ErrorHandler
    tableDef.Connect = ";DATABASE=" & newPath
    tableDef.RefreshLink
    RelinkTable = True
    Exit Function
    
ErrorHandler:
    Debug.Print "Error relinking table " & tableDef.Name & ": " & Err.Description
    RelinkTable = False
End Function

' Placeholder function to check the availability of a given back-end path type.
' Actual implementation should return True if the path is valid and accessible, False otherwise.
Private Function CheckPath(pathType As String) As Boolean
    ' Implementation depends on specific checks for network, local, or same folder paths
End Function

' Placeholder function to return the actual path string based on the pathType argument.
' Actual implementation should return the path string for the specified type.
Private Function GetPath(pathType As String) As String
    ' Implementation depends on how paths are stored or determined in your application
End Function

Key Refactoring Changes:

  1. Modularization: The code is broken down into smaller functions: DetermineBackendPath to determine which back-end path to use, RelinkTable to relink a single table, CheckPath (a stub for path checking), and GetPath (a stub for getting the actual path based on the type).
  2. Error Handling: Improved error handling in the RelinkTable function, with specific error messages for relinking failures.
  3. Reduced Global Variable Usage: The function uses local variables and parameters to pass information, reducing reliance on global variables.
  4. Return Value: The main function now returns a string indicating the type of link established and the count of relinked tables, providing feedback on the operation’s outcome.

Implementing CheckPath and GetPath:

The CheckPath and GetPath functions are placeholders and should be implemented to check the availability of the network, local, and same-folder back-ends and return their paths. These implementations will depend on your specific environment and requirements.

This refactoring aims to make the code cleaner, more maintainable, and easier to adapt to changes in back-end configurations or requirements.

1 Spice up
3

Wow! Which AI and where can I find it? This should help me, but it will take some time to go through all of the suggested changes. Thank you!

4

I learned (painfully) long ago to never assume what VB is going to return …

Insert 2 lines right before the line that pukes. debug.print db.tabledefs.count and debug.print currentdb.tabledefs.count. See what number each returns.

Forgive the bad capitalization/syntax … not on a computer ATM

2 Spice ups
5

Interesting. I should have thought of that! It fails on the db.TableDefs.Count line and produces an accurate count of table definitions on the CurrentDb.TableDefs.Count line. Why would that be? I have gone through the code with a fine-toothed comb to determine whether the db variable is getting re-assigned or changed somehow, but do not see where it is going wrong. It should not be necessary to set db again once it is set in the procedure.

6

Oof. I see where I went wrong. Part of the cleanup process for the functions that check each file path is to close db and set it to “Nothing”, so as this function processes, the db variable is getting destroyed and when the code in this function reaches a db variable, it is not there and fails. This thread has helped quite a bit! Thank you!

7

ChatGPT4, it’s not perfect - often gives me stuff that doesn’t work including syntax/commands that aren’t even available in Access, however it is quite helpful especially to just optimize/refactor existing code like yours (I’m guilty of doing the same) that can be broken down into more manageable routines. It’s also great for explaining what code does, I have stuff I wrote decades ago that I didn’t want to try and figure out so just pop in ChatGPT and voila, tells you what it does. Also good for adding comments to the code, for example the original output it provided for your code didn’t have many comments (if any), I told it to put in the comments you had originally and it did so based on the code it had generated. Awesome stuff!

2 Spice ups