0

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!

Community
  • 1
  • 1
EJ Mak
  • 799
  • 1
  • 8
  • 29

1 Answers1

1

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
E Mett
  • 2,272
  • 3
  • 18
  • 37
  • Thanks for the lead. Can I specify the filename with a wildcard to look for the back-end access file? Fie example, (pseudocode): tdf.Connect "DigExams_backend_*"? Also, do I need to reference the directory of the CurrentDB? – EJ Mak May 23 '14 at 11:48
  • You cannot use wildcards in the `Connect` property. Use `Dir` with a wildcard to locate the file and then use the exact filenmae. – E Mett May 25 '14 at 06:33
  • `CurrentDB` is the database object of the current Access file. `TableDefs` is a collection of the tables in the specified database. – E Mett May 25 '14 at 06:34