none
Linked tables created in Access 2013 not there in Access 2016 RRS feed

  • Question

  • I have a .accb file with several linked tables in an MS SQL source via ODBC.  The file was created in Access 2013 and all works fine.  When I now open that same file in Access 2016 those tables do not appear.  That means no queries or reports will work.  Opening the same file again in Access 2013 and sure enough, the linked table are there and everything still works.  So, what is different that opening the file in Access 2016 causes those links to disappear?


    • Edited by ghouser157 Wednesday, May 10, 2017 1:49 PM
    Wednesday, May 10, 2017 1:48 PM

Answers

  • You may be right as far as proper practice, but it is not gonna happen where we have multiple copies of the same database being used.  Only one person uses it at a time and then only to pull information and reports.  Your advice is sound and good Scott but does not speak to the actual question.

    That said.... I rebuilt both ODBC connections and for some odd reason I am not able to function.  Something with the new Windows 10 install must have been incomplete or broken...  I'm not sure exactly what did the trick.

    But a really big THANK YOU to those who replied and did try to assist.


    • Edited by ghouser157 Friday, May 12, 2017 7:10 PM
    • Marked as answer by ghouser157 Friday, May 12, 2017 7:10 PM
    Friday, May 12, 2017 6:03 PM

All replies

  • Is this on the same computer?

    If on different computers, did you install the required ODBC on each computer?


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, May 10, 2017 2:03 PM
  • Yes, the second computer has both the 32bit and 64bit ODBC configured.

    Wednesday, May 10, 2017 5:41 PM
  • When I now open that same file in Access 2016 those tables do not appear.  That means no queries or reports will work.  Opening the same file again in Access 2013 and sure enough, the linked table are there and everything still works. 

    Do you have 2013 & 2016 on same machine or different machine?

    If different machine did you try re-linking on the new machine?


    Build a little, test a little

    Wednesday, May 10, 2017 6:26 PM
  • Hi ghouser157,

    you had mentioned that,"Yes, the second computer has both the 32bit and 64bit ODBC configured."

    it means that you are trying to use same database on multiple machines.

    so what about your MSSQL Database?

    where you store this database?

    on the same machine or on server?

    it looks like links get broken on other machine.

    it can only work for a machine on that you had added it.

    so to relink the tables you can try to use code below may solve your issue.

    Public Function RefreshTableLinks() As String
    On Error GoTo ErrHandle
    
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strCon As String
    Dim strBackEnd As String
    Dim strMsg As String
    Dim intErrorCount As Integer
    
    Set db = CurrentDb
    
    ‘Loop through the TableDefs Collection.
    For Each tdf In db.TableDefs
    ‘Verify the table is a linked table.
    If Left$(tdf.Connect, 10) = “;DATABASE=” Then
    ‘Get the existing Connection String.
    strCon = Nz(tdf.Connect, “”)
    ‘Get the name of the back-end database using String Functions.
    strBackEnd = Right$(strCon, (Len(strCon) – (InStrRev(strCon, “”) – 1)))
    ‘Verify we have a value for the back-end
    If Len(strBackEnd & “”) > 0 Then
    ‘Set a reference to the TableDef Object.
    Set tdf = db.TableDefs(tdf.Name)
    ‘Build the new Connection Property Value.
    tdf.Connect = “;DATABASE=” & CurrentProject.Path & strBackEnd
    ‘Refresh the table link.
    tdf.RefreshLink
    Else
    ‘There was a problem getting the name of the back-end.
    ‘Add the information to the message to notify the user.
    intErrorCount = intErrorCount + 1
    strMsg = strMsg & “Error getting back-end database name.” & vbNewLine
    strMsg = strMsg & “Table Name: ” & tdf.Name & vbNewLine
    strMsg = strMsg & “Connect = ” & strCon & vbNewLine
    End If
    End If
    Next tdf
    
    ExitHere:
    On Error Resume Next
    If intErrorCount > 0 Then
    strMsg = “There were errors refreshing the table links: ” _
    & vbNewLine & strMsg & “In Procedure RefreshTableLinks”
    RefreshTableLinks = strMsg
    End If
    Set tdf = Nothing
    Set db = Nothing
    Exit Function
    
    ErrHandle:
    intErrorCount = intErrorCount + 1
    strMsg = strMsg & “Error ” & Err.Number & ” ” & Err.Description
    strMsg = strMsg & vbNewLine & “Table Name: ” & tdf.Name & vbNewLine
    strMsg = strMsg & “Connect = ” & strCon & vbNewLine
    Resume ExitHere
    
    End Function

    Reference:

    Automatically relink Microsoft Access tables

    Note:- above code has some requirement to run successfully. please try to check the requirements in link above before you use it. if you are not able to fulfil those requirement then you need to modify code to work as per your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Thursday, May 11, 2017 5:22 AM
    Moderator
  • The .accb database is on a network drive and several of us use it.  It has worked for a couple years and still works fine for others.  The SQL database is also on an app server on that same network.  My IT department decided I needed a workstation upgrade and gave me a Windows 10 machine running Office 2016.  When I open the file using my new system the linked tables do not appear.  I then open that same file on my old workstation (Windows 7 and Office 2013) and the linked tables are there and everything thing works just fine.  So, this is not a problem with the .accb file but something to do with the upgrade to Windows 10 and Office 2016.
    Friday, May 12, 2017 1:40 PM
  • You should NOT be sharing the same copy of the database, especially if you have multiple versions of Access running it. Instead, deploy a copy of the database to each machine and run that local copy. 

    After you get the local copy on your machine, you may simply need to relink the tables. This can be done using the Linked Table Manager in Access, or you can run code to do it.


    -- Scott McDaniel, Microsoft Access MVP

    Friday, May 12, 2017 2:14 PM
  • You may be right as far as proper practice, but it is not gonna happen where we have multiple copies of the same database being used.  Only one person uses it at a time and then only to pull information and reports.  Your advice is sound and good Scott but does not speak to the actual question.

    That said.... I rebuilt both ODBC connections and for some odd reason I am not able to function.  Something with the new Windows 10 install must have been incomplete or broken...  I'm not sure exactly what did the trick.

    But a really big THANK YOU to those who replied and did try to assist.


    • Edited by ghouser157 Friday, May 12, 2017 7:10 PM
    • Marked as answer by ghouser157 Friday, May 12, 2017 7:10 PM
    Friday, May 12, 2017 6:03 PM
  • Using multiple versions of Access to run the same copy could certainly cause troubles like you describe. Older versions of Access (pre-2007) didn't have this issue, but since 2007 we've seen quite a lot of troubles when different versions run the same file.

    I was of the assumption that your database was split into a Backend (Tables only) and a FrontEnd (everything else), and that users were running the FE only. If that's the case, then everyone would work on the same copy of the DATA, but they would have their own copy of the FE. That's the right way to handle multi-user applications (and especially where you have multiple users with different Access versions).

    Not trying to be argumentative, just want to be sure that others who might come across this understand the right way to implement a multi-user Access application.


    -- Scott McDaniel, Microsoft Access MVP

    Friday, May 12, 2017 9:09 PM