How can I automatically link an Access front-end file to a back-end?

0
votes

I have a pair of Access files, a front end and a back end, that I share with several users and update frequently. I'm looking for a way to automatically link the two files so my users don't need to use the Linked Table Manager. I suspect that a solution can be created with VBA. I reviewed a few possibilities, here and here, but they don't apply to my specific scenario, below.

  • The two files always exist in the same user directory.
  • These are the only two Access files in each directory.
  • The directories are located on the users' local C: drives (in a Dropbox folder).
  • The names of the two files change slightly with each version/build update.
    • Example front-end name: "DigExams_0.10.f005.u101.accdb"
    • Example back-end name: "DigExams_backend_0.10.b001.u101.accdb"
    • Naming convention: "Filename*_major#.minor#.build#.user#.accdb"
    • [ * The back end file also includes the word "backend". ]

I was thinking that some code could be embedded into a button on the front end that would (OnClick) run the Linked Table Manager, look for the only other Access file in the directory (or the file with "backend" in the name), and then link all of the tables to it. If there was a conflict, such as a third Access file in the directory, an error would appear.

Advice? Suggestions? Thanks!

1
ms-accessvba

1 Answers

1
votes

The target file is stored in the Connect property of the TableDef object.

You will need to replace the releveant part of the Connect property with the new file name.

Dim tdf as TableDef
For each tdf in CurrentDB.TablDefs
   StringToBeParsed =tdf.Connect
   .... 'here goes the code to replace the connection string with new target file
Next