Access VBA - Using AddFromFile without locking the reference file

0
votes

I have five MS Access (Office 2013) databases which are split into five front-end (in several users desktops) and five back-end (in the server) files. I have most of my code in a .accdb file, and my front-end files reference to the code file (in the server) using:

Application.VBE.ActiveVBProject.References.AddFromFile "W:\DB\Code.accdb"

Everything works fine while people are using the front-end files.

However, every time I need to correct something in the code file, I cannot do it if someone has its front-end file opened (I get "You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later.").

Is there any way of the front-end files to reference the code file in a non-exclusive or read-only mode? I am not expecting to update the code in real time it the front-end files, I just want to be able to do the changes, save them, and then the users get it the next time they open their files.

I have already tried to:

  • Set Code.accdb as read-only file (which is not interesting because I need to edit the file anyway)
  • Set the permissions of the folder "W:\DB\" to read only for all users but me. This way, the lock file is not created when front-end files are opened, but the code file opens as read only for me.
1
vbams-access

1 Answers

0
votes

The simple solution is to distribute your front ends and that reference library to each user’s computer.

For the last 20 years we installed + deployed word on each desktop.

For the last 20 years we installed + deployed Excel on each desktop.

For the last 20 years, we installed APPLICATIONS on EACH desktop.

So you one need to join the rest of the IT industry in what they been doing for the last 20 years. Given that YOU are now creating and writing software as opposed to simply opening a word document, then such systems are by tradition installed on each desktop. The fact that you purchased some software and installed it on each computer, or that you are NOW writing your own software does not change this long time history of installing such software on each computer.

I also should point out that both your front ends not only need to be installed on each computer, but should be a compiled accDE. And your reference library should also be compiled code.

I mean, if one user’s copy of word were to go bad, the all 50 users would have to go home until word is fixed if you shared the one copy. So the solution is to do what everyone else in the industry does – install the software on each workstation and thus as a developer you can work on a copy of the next great edition of your software.