Linked tables don't appear to be

[ryetee]

Ah. Sorry I misunderstood

Try running this query & post a screenshot of the results

SELECT
    MSysObjects.Name,
    MSysObjects.Type,
    MSysObjects.Flags
FROM
    MSysObjects
WHERE
    (((MSysObjects.Type) IN (4, 6)));

 

[ryetee]

The Connect property is used for RDBMS links. The Database property is used for others.

View attachment 121181

OK the connect property has no value. Madpiet says it, or access think it's a local table. It is not so how can i rectify that

 

[ryetee]

Incidentally, I've just tried to run the database documenter and got these 2 messages when I've switched tabs
Could this have something to do with it?

 

[isladogs]

Bingo! The information in post #24 was very helpful.
Although you haven't said so, I think you may be running 32-bit Access 2016 version 2507.
If so, you may be affected by a bug that affected various wizards

See https://www.accessforever.org/post/wizard-activexs-are-broken-in-32-bit and https://techcommunity.microsoft.com/discussions/access/ver2507-bug/4441089

If I'm right, the good news is that an update was released on Tues 19 Aug that fixes the problem.
The fixed build is 19029.20208

 

[ryetee]

Bingo! The information in post #24 was very helpful.
Although you haven't said so, I think you may be running 32-bit Access 2016 version 2507.
If so, you may be affected by a bug that affected various wizards

See https://www.accessforever.org/post/wizard-activexs-are-broken-in-32-bit and https://techcommunity.microsoft.com/discussions/access/ver2507-bug/4441089

If I'm right, the good news is that an update was released on Tues 19 Aug that fixes the problem.
The fixed build is 19029.20208

I mentioned I was using 2016 but not the build version which is as you rightly point 2507.
I'll read the links which hopefully tell me how to apply the fixed build.
Do you know when this "went wrong" as I'm sure the linked table manager worked not more than 2-3 weeks ago

 

[isladogs]

As the AFo article states in the first line, various 32-bit wizard related issues were triggered with version 2507 build 19029.20136 released on 30 July.
Just run a version update in the usual way to get the latest current channel version.

 

[ryetee]

I mentioned I was using 2016 but not the build version which is as you rightly point 2507.
I'll read the links which hopefully tell me how to apply the fixed build.
Do you know when this "went wrong" as I'm sure the linked table manager worked not more than 2-3 weeks ago

As a matter of interest @isladogs my build version is 19029.20184 which was released on the 12th August. I didn't apply it so it must have been an automatic update. In that respect why haven't I got the latest 20208 which the article says was released on the 19th? I'm a bit wary to update myself as I've not done it before

 

[ryetee]

As the AFo article states in the first line, various 32-bit wizard related issues were triggered with version 2507 build 19029.20136 released on 30 July.
Just run a version update in the usual way to get the latest current channel version.

see my other reply about doing the update myself!!

 

[isladogs]

You’ll get that version eventually via an automatic update but it’s easy enough to do

From backstage view, click Account | Update Options | Update Now.
If the update is available to you the update process will start automatically

 

[ryetee]

You’ll get that version eventually via an automatic update but it’s easy enough to do

From backstage view, click Account | Update Options | Update Now.
If the update is acc bc bailable to you the update process will start automatically

I thought about restarting machine to push it along a bit but it now seems to have died on me! You've got to love technology eh!?
Thanks for all your help btw

 

[isladogs]

Restarting is unlikely to help accelerate matters (assuming it recovers from the dead).

Don’t be afraid of forcing the update in the way I described. It’s easy.

 

[ryetee]

Restarting is unlikely to help accelerate matters (assuming it recovers from the dead).

Don’t be afraid of forcing the update in the way I described. It’s easy.

The restart worked and now I can see the linked tables. I was sort of confident that it would> I can bore with you with why I was if you want!! Anyway, great spot appreciate your help.

 

[ryetee]

You could include your own interface for validating and refreshing the links. The attached zip archive includes two files which illustrate this, one being the front end, the other the back end. The routines in the front end in this case are designed to work with multiple Access back ends.

When the front end is opened, code in its opening form examines the current links. If any are not valid a dialogue form opens in which the validity of each link is given in a list box. One or more items in the list can be selected, and the links refreshed.

I can't actually "get into" the code on the refreshlinks_07 to see what's going on but not sure it would work for me.
Is it possible to pick up the root directory in the code (it may do this but I can't see the code).
So if the FE is in
Drive Letter:\DirectorYForFE can I pick this up ?

 

[isladogs]

The restart worked and now I can see the linked tables. I was sort of confident that it would> I can bore with you with why I was if you want!! Anyway, great spot appreciate your help.

Glad it worked out for you. I knew it would once I saw those screenshots, which is why I pushed you to update.

 

[Ken Sheridan]

I can't actually "get into" the code on the refreshlinks_07 to see what's going on but not sure it would work for me.
Is it possible to pick up the root directory in the code (it may do this but I can't see the code).
So if the FE is in
Drive Letter:\DirectorYForFE can I pick this up ?

I don't know why that should be. Have you placed the .accdb files in a Trusted Location?

The code for the opening form's Open event procedure, which validates the current links, is:

Private Sub Form_Open(Cancel As Integer)
 
    On Error GoTo Err_Handler
 
    Const conFILENOTFOUND = 3024
    Const conPATHNOTFOUND = 3044
    Const conINPUTFILENOTFOUND = 3078
 
    Dim dbs As DAO.Database, rst As DAO.Recordset, tdf As DAO.TableDef
    Dim strTable As String, strConnect As String
 
    Set dbs = CurrentDb
 
    DoCmd.SelectObject acForm, Me.Name, True
    DoCmd.Minimize
 
CheckLinks:
    For Each tdf In dbs.TableDefs
        If Len(tdf.Connect) > 0 Then
            If tdf.Connect <> strConnect Then
                strTable = tdf.Name
                Set rst = dbs.OpenRecordset(strTable)
                strConnect = tdf.Connect
            End If
        End If
    Next tdf
 
Exit_Here:
    Set rst = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub
 
Err_Handler:
    If Err.Number = conFILENOTFOUND Or _
        Err.Number = conPATHNOTFOUND Or _
        Err.Number = conINPUTFILENOTFOUND Then
        DoCmd.OpenForm "frmUpdate_Links", , , , , acDialog
        Resume CheckLinks
    Else
        MsgBox Err.Description & " (" & Err.Number & ")"
        Resume Exit_Here
    End If
 
End Sub

The code for the frmUpdate_Links form's module is:

Option Compare Database
Option Explicit

Function GetList(Fld As Control, ID As Variant, row As Variant, col As Variant, Code As Variant) As Variant
 
    Dim dbs As Database, tdf As TableDef
    Dim varFld As Variant, varReturnVal As Variant, n As Integer
    Static intEntries As Integer
    Static aData() As Variant
 
 
    Select Case Code
        Case acLBInitialize
            Set dbs = CurrentDb
            intEntries = dbs.TableDefs.Count
            ReDim aData(intEntries, 2)
            For Each tdf In dbs.TableDefs
                If Len(tdf.Connect) = 0 Then
                    intEntries = intEntries - 1
                Else
                    On Error Resume Next
                    varFld = tdf.Fields(0).Name
                    If Err <> 0 Then
                        Err.Clear
                        aData(n, 1) = "Broken"
                    Else
                        aData(n, 1) = "OK"
                    End If
                    On Error GoTo 0
                    aData(n, 0) = tdf.Name
                    aData(n, 2) = tdf.Connect
                    n = n + 1
                End If
            Next tdf
            varReturnVal = True
        Case acLBOpen
            varReturnVal = Timer
        Case acLBGetRowCount
            varReturnVal = intEntries
        Case acLBGetColumnCount
            varReturnVal = 3
        Case acLBGetColumnWidth
            varReturnVal = -1
        Case acLBGetValue
            varReturnVal = aData(row, col)
        Case acLBEnd
CleanUp:
            Erase aData
    End Select
    GetList = varReturnVal
 
End Function

Private Sub BrowseButton_Click()

    On Error GoTo Err_Handler
 
    Dim strFullPath As String, strFolder As String, strFile As String
    Dim intLength As Integer, intCount As Integer
 
    strFullPath = BrowseFile("Access Databases", "accdb")
 
    If strFullPath = "" Then Exit Sub
 
    ' Loop backwards through selected full path
    ' until backslash encountered
    intLength = Len(strFullPath)
    For intCount = intLength To 0 Step -1
        If Mid(strFullPath, intCount, 1) = "\" Then
            ' Store folder and file names in variables
            strFolder = Left(strFullPath, intCount - 1)
            strFile = Mid(strFullPath, intCount + 1)
            Exit For
        End If
    Next intCount
 
    ' Populate text boxes on form
    Me!PathToSource = strFolder
    Me!DbFile = strFile
 
Exit_Here:
    Exit Sub
 
Err_Handler:
    MsgBox Err.Description
    Resume Exit_Here

End Sub


Private Sub CloseAppButton_Click()

On Error GoTo Err_CloseAppButton_Click

    Application.CloseCurrentDatabase
 
Exit_CloseAppButton_Click:
    Exit Sub

Err_CloseAppButton_Click:
    MsgBox Err.Description
    Resume Exit_CloseAppButton_Click

End Sub

Private Sub OKButton_Click()

On Error GoTo Err_OKButton_Click
 
    Const conTITLE = "Refresh Links"
    Const conINVALIDPATH = 3044
    Const conMISSINGSPEC = 3625
 
    Dim dbs As Database
    Dim tdf As TableDef
    Dim varItem As Variant
    Dim strMessage As String, strDatabaseName As String
    Dim strPath As String, strTable As String, strDbType As String
    Dim intPathLength As Integer
 
    Set dbs = CurrentDb()
 
    ' get path to back end database from text boxes
    strDatabaseName = PathToSource & "\" & DbFile
 
    ' Ensure path to back end database has been entered
    ' Ensure path to back end database has been entered
    If IsNull(PathToSource) Then
        MsgBox "Path to source database must be " & _
        "entered before updating links", vbInformation, conTITLE
        Exit Sub
    Else
        If IsNull(DbFile) Then
            MsgBox "File name must be " & _
            "entered before updating links", vbInformation, conTITLE
            Exit Sub
        End If
    End If
 
    strMessage = "Are you sure you wish to link to " & _
    strDatabaseName & " as the source database?"
 
    ' If link not confirmed abort procedure
    If MsgBox(strMessage, vbOKCancel + vbQuestion, conTITLE) = vbCancel Then
        Exit Sub
    End If
 
    ' Check that source database exists
    If Dir(strDatabaseName) = "" Then
        MsgBox "The database you have specified as the " & _
        "source was not found.", vbInformation, conTITLE
        Exit Sub
    End If
 
    ' Show hourglass
    DoCmd.Hourglass True
 
    ' Loop through selected items and refresh link to each table
    For Each varItem In lstTables.ItemsSelected
        strTable = lstTables.ItemData(varItem)
        Set tdf = dbs.TableDefs(strTable)
        strDbType = Left$(tdf.Connect, InStr(tdf.Connect, "DATABASE=") + 8)
        tdf.Connect = strDbType & strDatabaseName
        tdf.RefreshLink
    Next varItem
 
    ' Hide hourglass
    DoCmd.Hourglass False
 
    MsgBox "Linking completed.", vbInformation, conTITLE
 
Exit_OKButton_Click:
    DoCmd.Hourglass False
    Me!lstTables.Requery
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub
 
Err_OKButton_Click:
    Select Case Err.Number
        Case conINVALIDPATH
        strPath = Left$(strDatabaseName, Len(strDatabaseName) - Len(Dir(strDatabaseName)) - 1)
        tdf.Connect = strDbType & strPath
        Resume
        Case conMISSINGSPEC
        DoCmd.Hourglass False
        strMessage = "No specification exists for " & tdf.Name & "." & _
            vbNewLine & vbNewLine & "Click OK to rebuild the link."
        dbs.TableDefs.Delete (tdf.Name)
        MsgBox strMessage, vbInformation, "Error"
        RunCommand acCmdLinkTables
        DoCmd.Hourglass True
        Resume Next
        Case Else
        DoCmd.Hourglass False
        MsgBox Err.Description & " (" & Err.Number & ")"
        Resume Exit_OKButton_Click
    End Select
 
End Sub


Private Sub CloseButton_Click()
On Error GoTo Err_CloseButton_Click


    DoCmd.Close

Exit_CloseButton_Click:
    Exit Sub

Err_CloseButton_Click:
    MsgBox Err.Description
    Resume Exit_CloseButton_Click
 
End Sub

and the code for the basBrowse standard module is:

' basBrowse
Option Compare Database
Option Explicit

Public Function BrowseFile(varDocType As Variant, varExtension As Variant) As String

    Dim strFile As String
    With Application.FileDialog(3)
        .Title = "Select File"
        .Filters.Clear
        If Not IsNull(varDocType) Then
            .Filters.Add varDocType, "*." & varExtension
         End If
        .Filters.Add "All Files", "*.*"
        If .Show Then
            strFile = .SelectedItems(1)
        Else
            MsgBox "No file selected", vbInformation
            Exit Function
        End If
    End With
 
    BrowseFile = strFile
 
End Function

When the BrowseFile function is called in the frmUpdate_Links form's module the dialogue opens by default at the current user's Documents folder, but you can easily browse to the root folder, or anywhere else, in the dialogue. Note that the dialogue opens by default filtered to .accdb files, but you can easily change that in the calling code if necessary.

 

[ryetee]

I told you how to verify a non-RDBMS linked table. Look at the DATABASE property.

That's filled with the link to the back end. I didn't quite get what you were getting at

 

[ryetee]

I don't know why that should be. Have you placed the .accdb files in a Trusted Location?

The code for the opening form's Open event procedure, which validates the current links, is:

Private Sub Form_Open(Cancel As Integer)
 
    On Error GoTo Err_Handler
 
    Const conFILENOTFOUND = 3024
    Const conPATHNOTFOUND = 3044
    Const conINPUTFILENOTFOUND = 3078
 
    Dim dbs As DAO.Database, rst As DAO.Recordset, tdf As DAO.TableDef
    Dim strTable As String, strConnect As String
 
    Set dbs = CurrentDb
 
    DoCmd.SelectObject acForm, Me.Name, True
    DoCmd.Minimize
 
CheckLinks:
    For Each tdf In dbs.TableDefs
        If Len(tdf.Connect) > 0 Then
            If tdf.Connect <> strConnect Then
                strTable = tdf.Name
                Set rst = dbs.OpenRecordset(strTable)
                strConnect = tdf.Connect
            End If
        End If
    Next tdf
 
Exit_Here:
    Set rst = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub
 
Err_Handler:
    If Err.Number = conFILENOTFOUND Or _
        Err.Number = conPATHNOTFOUND Or _
        Err.Number = conINPUTFILENOTFOUND Then
        DoCmd.OpenForm "frmUpdate_Links", , , , , acDialog
        Resume CheckLinks
    Else
        MsgBox Err.Description & " (" & Err.Number & ")"
        Resume Exit_Here
    End If
 
End Sub

The code for the frmUpdate_Links form's module is:

Option Compare Database
Option Explicit

Function GetList(Fld As Control, ID As Variant, row As Variant, col As Variant, Code As Variant) As Variant
 
    Dim dbs As Database, tdf As TableDef
    Dim varFld As Variant, varReturnVal As Variant, n As Integer
    Static intEntries As Integer
    Static aData() As Variant
 
 
    Select Case Code
        Case acLBInitialize
            Set dbs = CurrentDb
            intEntries = dbs.TableDefs.Count
            ReDim aData(intEntries, 2)
            For Each tdf In dbs.TableDefs
                If Len(tdf.Connect) = 0 Then
                    intEntries = intEntries - 1
                Else
                    On Error Resume Next
                    varFld = tdf.Fields(0).Name
                    If Err <> 0 Then
                        Err.Clear
                        aData(n, 1) = "Broken"
                    Else
                        aData(n, 1) = "OK"
                    End If
                    On Error GoTo 0
                    aData(n, 0) = tdf.Name
                    aData(n, 2) = tdf.Connect
                    n = n + 1
                End If
            Next tdf
            varReturnVal = True
        Case acLBOpen
            varReturnVal = Timer
        Case acLBGetRowCount
            varReturnVal = intEntries
        Case acLBGetColumnCount
            varReturnVal = 3
        Case acLBGetColumnWidth
            varReturnVal = -1
        Case acLBGetValue
            varReturnVal = aData(row, col)
        Case acLBEnd
CleanUp:
            Erase aData
    End Select
    GetList = varReturnVal
 
End Function

Private Sub BrowseButton_Click()

    On Error GoTo Err_Handler
 
    Dim strFullPath As String, strFolder As String, strFile As String
    Dim intLength As Integer, intCount As Integer
 
    strFullPath = BrowseFile("Access Databases", "accdb")
 
    If strFullPath = "" Then Exit Sub
 
    ' Loop backwards through selected full path
    ' until backslash encountered
    intLength = Len(strFullPath)
    For intCount = intLength To 0 Step -1
        If Mid(strFullPath, intCount, 1) = "\" Then
            ' Store folder and file names in variables
            strFolder = Left(strFullPath, intCount - 1)
            strFile = Mid(strFullPath, intCount + 1)
            Exit For
        End If
    Next intCount
 
    ' Populate text boxes on form
    Me!PathToSource = strFolder
    Me!DbFile = strFile
 
Exit_Here:
    Exit Sub
 
Err_Handler:
    MsgBox Err.Description
    Resume Exit_Here

End Sub


Private Sub CloseAppButton_Click()

On Error GoTo Err_CloseAppButton_Click

    Application.CloseCurrentDatabase
 
Exit_CloseAppButton_Click:
    Exit Sub

Err_CloseAppButton_Click:
    MsgBox Err.Description
    Resume Exit_CloseAppButton_Click

End Sub

Private Sub OKButton_Click()

On Error GoTo Err_OKButton_Click
 
    Const conTITLE = "Refresh Links"
    Const conINVALIDPATH = 3044
    Const conMISSINGSPEC = 3625
 
    Dim dbs As Database
    Dim tdf As TableDef
    Dim varItem As Variant
    Dim strMessage As String, strDatabaseName As String
    Dim strPath As String, strTable As String, strDbType As String
    Dim intPathLength As Integer
 
    Set dbs = CurrentDb()
 
    ' get path to back end database from text boxes
    strDatabaseName = PathToSource & "\" & DbFile
 
    ' Ensure path to back end database has been entered
    ' Ensure path to back end database has been entered
    If IsNull(PathToSource) Then
        MsgBox "Path to source database must be " & _
        "entered before updating links", vbInformation, conTITLE
        Exit Sub
    Else
        If IsNull(DbFile) Then
            MsgBox "File name must be " & _
            "entered before updating links", vbInformation, conTITLE
            Exit Sub
        End If
    End If
 
    strMessage = "Are you sure you wish to link to " & _
    strDatabaseName & " as the source database?"
 
    ' If link not confirmed abort procedure
    If MsgBox(strMessage, vbOKCancel + vbQuestion, conTITLE) = vbCancel Then
        Exit Sub
    End If
 
    ' Check that source database exists
    If Dir(strDatabaseName) = "" Then
        MsgBox "The database you have specified as the " & _
        "source was not found.", vbInformation, conTITLE
        Exit Sub
    End If
 
    ' Show hourglass
    DoCmd.Hourglass True
 
    ' Loop through selected items and refresh link to each table
    For Each varItem In lstTables.ItemsSelected
        strTable = lstTables.ItemData(varItem)
        Set tdf = dbs.TableDefs(strTable)
        strDbType = Left$(tdf.Connect, InStr(tdf.Connect, "DATABASE=") + 8)
        tdf.Connect = strDbType & strDatabaseName
        tdf.RefreshLink
    Next varItem
 
    ' Hide hourglass
    DoCmd.Hourglass False
 
    MsgBox "Linking completed.", vbInformation, conTITLE
 
Exit_OKButton_Click:
    DoCmd.Hourglass False
    Me!lstTables.Requery
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub
 
Err_OKButton_Click:
    Select Case Err.Number
        Case conINVALIDPATH
        strPath = Left$(strDatabaseName, Len(strDatabaseName) - Len(Dir(strDatabaseName)) - 1)
        tdf.Connect = strDbType & strPath
        Resume
        Case conMISSINGSPEC
        DoCmd.Hourglass False
        strMessage = "No specification exists for " & tdf.Name & "." & _
            vbNewLine & vbNewLine & "Click OK to rebuild the link."
        dbs.TableDefs.Delete (tdf.Name)
        MsgBox strMessage, vbInformation, "Error"
        RunCommand acCmdLinkTables
        DoCmd.Hourglass True
        Resume Next
        Case Else
        DoCmd.Hourglass False
        MsgBox Err.Description & " (" & Err.Number & ")"
        Resume Exit_OKButton_Click
    End Select
 
End Sub


Private Sub CloseButton_Click()
On Error GoTo Err_CloseButton_Click


    DoCmd.Close

Exit_CloseButton_Click:
    Exit Sub

Err_CloseButton_Click:
    MsgBox Err.Description
    Resume Exit_CloseButton_Click
 
End Sub

and the code for the basBrowse standard module is:

' basBrowse
Option Compare Database
Option Explicit

Public Function BrowseFile(varDocType As Variant, varExtension As Variant) As String

    Dim strFile As String
    With Application.FileDialog(3)
        .Title = "Select File"
        .Filters.Clear
        If Not IsNull(varDocType) Then
            .Filters.Add varDocType, "*." & varExtension
         End If
        .Filters.Add "All Files", "*.*"
        If .Show Then
            strFile = .SelectedItems(1)
        Else
            MsgBox "No file selected", vbInformation
            Exit Function
        End If
    End With
 
    BrowseFile = strFile
 
End Function

When the BrowseFile function is called in the frmUpdate_Links form's module the dialogue opens by default at the current user's Documents folder, but you can easily browse to the root folder, or anywhere else, in the dialogue. Note that the dialogue opens by default filtered to .accdb files, but you can easily change that in the calling code if necessary.

Don't think I've placed it in a trusted location. When I opened it in the first place it says it was read only and to save it with a different name. I did, to my documents folder and ran it from there

 

[isladogs]

That should be Database field in MSysObjects (not property). Similarly for the Connect field (not Connection property).

 

[Ken Sheridan]

Don't think I've placed it in a trusted location. When I opened it in the first place it says it was read only and to save it with a different name. I did, to my documents folder and ran it from there

You can see the current trusted locations from Access with File | Options | Trust Center | Trust Center Settings | Trusted Locations. It is likely that your Documents folder and its sub-folders are set as trusted locations, which is why you were able to open the file once you'd moved it to there.

 

[ryetee]

If your linked table is SQL Server or other RDBMS, the link to the table will be in the Connection property.
If your linked table is anything else, the link to the table will be in the Database property because it is a file name. So links to spreadsheets and text files are stored here also.
If you download the database (FE) from the internet, you end up with other security concerns and that causes the "save as" dialog.

ok i'm all sorted now. there was an issue created by july's update that was corrected by the one in august