MS Access Linked Table Manager doesn't update new data source

Question

I have an MS Access 2010 Database that has a table that is linked to a CSV file. Upating the CSV files location using the inbuilt Access "Linked Table Manager" doesn't work.

I check the file i want to update, choose "always prompt for new location" and select the new file. I get a message telling me that the update was successful, but when I go to check, the table is still linked to the old file.

Is this a MS Access bug and if so what is the most efficient workaround?

I ended up deleting the old table and manually recreating a new table with the same specifications.

Are you trying to select a file with a different name (i.e. FileA.csv vs FileB.csv)? If I link to FileA.csv, then use Linked Table Manager and link to FileB.csv, it says it worked, but the same old file is retained. If I relink to FileA.csv in a different folder, then yes, it does work correctly. I also tested the code at the following link and that seems to work for deleting / relinking: tek-tips.com/viewthread.cfm?qid=865710 — Wayne G. Dunn
You are correct. However, I could not simply use a subfolder of the current path. I had to pick an entirely new folder. That was part of the reason why it did not work for me when I was trying it. An odd bug and one that Microsoft should have addressed a long time ago. Please post your comment as an answer so I can check it off as the solution! Thanks — rohrl77

Wayne G. Dunn Wayne G. Dunn · Accepted Answer · 2017-05-23T20:09:08

*Updated: -- I forgot to include the referenced Function Relink_CSV :(

Yes, I would call it a bug. Microsoft probably calls it a 'design characteristic'.

As you have discovered, you can manually fix the issue. If you are interested in a code solution, then I may have something that will work for you -- if your CSV file is delimited by comma's.

The following code (which you need to modify!) will delete the existing linked csv file, then add a link to the same file. For debugging, my code then deletes that link and adds a link to a different file name, but in the same folder.

There are other solutions that make use of a saved Import Specification, that you can reuse, if your csv format is not simple.

Option Explicit
Option Compare Database

Sub Call_Relink()
    Dim dbs         As DAO.Database
    Dim tdf         As DAO.TableDef
    Dim strTableName    As String
    Dim strPath     As String
    Dim strFile     As String
    Dim iReply      As Integer

    iReply = MsgBox("WARNING!!!! This code will remove the linked tables 'FileA' and 'FileB'" & vbCrLf & vbCrLf & _
            "Click 'Yes' to Continue" & vbCrLf & "Click 'No' to Stop", vbYesNo, "CAUTION!! Will remove linked table(s)")
    If iReply <> vbYes Then
        Exit Sub
    End If

    On Error GoTo Error_Trap
    Set dbs = CurrentDb
    dbs.TableDefs.Delete "FileA"                    ' For testing; delete table if it already exists
    strPath = "C:\Temp\"
    strFile = "FileA.csv"
    strTableName = "FileA"                          ' Table name in Access
    Relink_CSV strTableName, strPath, strFile       ' Call function to link the CSV file
    dbs.TableDefs.Refresh                           ' Refresh TDF's

    Debug.Print "Pause here and check file link"    ' Put a breakpoint here; pause and look at the table in Access

    dbs.TableDefs.Delete "FileA"                    ' For testing; delete table if it already exists
    strPath = "C:\Temp\"                            ' Path to next csv
    strFile = "FileB.csv"                           ' Name of next csv file
    strTableName = "FileA"                          ' Table name in Access
    Relink_CSV strTableName, strPath, strFile       ' Call function to link to a different CSV file
    dbs.TableDefs.Refresh

    Debug.Print "Pause here and check file link"    ' Put a breakpoint here; pause and look at the table in Access


My_Exit:
    Set dbs = Nothing
    Exit Sub
Error_Trap:
    Debug.Print Err.Number & vbTab & Err.Description
    If Err.Number = 3265 Then           ' Item not found in this collection.
        ' Ignore this error
        Resume Next
    End If
    MsgBox Err.Number & vbTab & Err.Description
    Resume My_Exit
    Resume
End Sub

Function Relink_CSV(strTableName As String, strPath As String, strFile As String)
' (1)   Name of the table in Access
' (2)   Path to the file
' (3)   File name

    On Error GoTo Relink_Err
    DoCmd.TransferText acLinkDelim, , strTableName, strPath & strFile, False, ""
Relink_Exit:
    Exit Function
Relink_Err:
    Debug.Print Err.Number & vbTab & Err.Description
    MsgBox Err.Number & vbTab & Err.Description
    Resume Relink_Exit
    Resume
End Function

MS Access Linked Table Manager doesn't update new data source

1 Answers