Use VBA Code to Update External Datasource Links

Question

I am looking to use VBA to update links for an external input file. I am a developer and the path for the linked input file I use will not be the same as the end user will need once it is placed in a production folder.

Is there a way to update the linked file location using VBA? I already have code that allows the user to specify the input file location and that information is saved in the [InputFolder] of the [Defaults] table. Is there a way to use VBA to update the Linked Table using the InputFolder field info?

The stored InputFolder data looks like this: C:\Users\CXB028\OneDrive - Comerica\Projects\HR\Input Data

The new folder info would have a network drive location path defined that I do not have access to but the user would.

Here is the code I use to define and store the Input Folder location:

Private Sub btnInputFldr_Click()
On Error GoTo Err_Proc

Const msoFileDialogFolderPicker As Long = 4
Dim objfiledialog As Object
Dim otable As DAO.TableDef
Dim strPathFile As String, strFile As String, strpath As String
Dim strTable As String
Dim fldr As Object

Set fldr = Application.FileDialog(msoFileDialogFolderPicker)

With fldr
    .Title = "Choose Folder"
    .Show
    .InitialFileName = "" 'DFirst("InputFolder", "Defaults")

        If .SelectedItems.Count = 0 Then

            Exit Sub

        Else
            CurrentDb.Execute "UPDATE Defaults SET InputFolder='" & .SelectedItems(1) & "';"

        End If

End With

Me.txtInputFldr.Requery

Exit Sub

Err_Proc:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Process Error"

End Sub

The linked table (an external excel spreadsheet) needs to be re-linked after the database is moved to the production location using VBA code when the new Input Folder is redefined.

There seem to be some good answers [here](https://stackoverflow.com/questions/4928134/changing-linked-table-location-programmatically) that deal with manipulating external data sources. It looks like some variation of `TableDefs.Connect`is what you're looking for to specify a new path. — Mako212, Jan 04 '19 at 23:43

score 0 · Answer 1 · answered Jan 11 '19 at 20:43

I found some very simple and short code the worked great!! Please see below.

 On Error Resume Next
 'Set new file path location if the TABLE.FIELDNAME location exists
 Set tbl = db.TableDefs("ENTER THE LINKED TABLE NAME HERE")
 filePath = DLookup("ENTER YOUR LOOKUP TABLE FIELD NAME HERE", "ENTER YOUR LOOKUP TABLE NAME HERE") & "\ENTER YOUR EXCEL SPREADSHEET NAME HERE.XLSX"
     tbl.Connect = "Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & filePath
     tbl.RefreshLink
 On Error GoTo 0

Hope someone else finds this as useful as I did!

Use VBA Code to Update External Datasource Links

1 Answers1