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 that deal with manipulating external data sources. It looks like some variation of TableDefs.Connectis what you're looking for to specify a new path. — Mako212
– Mako212, Commented Jan 4, 2019 at 23:43

Chip Bender · Accepted Answer · 2019-01-11 20:43:35Z

0

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!

answered Jan 11, 2019 at 20:43

Chip Bender

11 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use VBA Code to Update External Datasource Links

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related