VBA to link new table from selected back-end file

Question

Referring to the post;

https://social.msdn.microsoft.com/Forums/office/en-US/0b0fc064-2d33-4f3f-8fa1-e65eece3963e/vba-to-automatically-create-linked-tables-from-selected-backend-file?forum=accessdev

Ken, What I have is FE Updater that was created by Bob Larson and for some reason it inserts the table into the BE but doesn't link it to the FE. So I figured ill just have it automatically do it.  I did try the below code but it created a link in the FE updater and not a link in the intended FE.

  Dim strFEFile As String
    Dim strBEFile As String
    
    strBEFile = Me.txtBackEnd
    strFEFile = Me.txtFEMaster
     
    DoCmd.TransferDatabase acLink, "Microsoft Access", strBEFile, acTable, "tbl-version_fe_master", "tbl-version_fe_master"

The text boxes have the full path to FE and BE how using the above do I get it to link to my FE and not the utility?

Answer 1

I'm not familiar with the FE Updater file in question, but generally, to update a front end in the event of a back end file being relocated or renamed, code would be executed in the front end at start-up to check the validity of existing links and refresh these where necessary.  You'll find examples in Refresh.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Where the back end is a single Access file the RefreshLinksSingle_07.accdb file is the appropriate example.  In this, code in the opening form's Open event procedure examines the current links and, if these are invalid, opens a simple dialogue form in which the user can browse to the current back end to refresh the links.  I've used this method in countless files which I've distributed over the years, and it's always been foolproof.  The user only needs to do this at first start-up of course.  Subsequently, unless the back end is moved or renamed, the links will be correct.

---

Ken Sheridan, Stafford, England