How to deploy ms access form in mulituser network server? Cant update because file in use

Question

I save the ms access file form in the network where is accessible by everyone. Everyone has a link on their desktop to the file in the network. Every time I change the file I have to ensure every one has the ms access file closed.

I really don't want to go looking everywhere to check if the app is closed. I've also set the file to read only, but no luck...

Answer 1

The basic concept for MOST software in the market places is you have two parts to application:

Word documents (data) + the word program executable

Excel document (data) + the word program executable.

So for 20 years in your company, how do you deploy software?

Answer:

You install the program on EACH computer.

So you are talking about a SOFTWARE program that you developed. Just because you use c++, vb.net or Access to CREATE SOFTWARE, then you don't break this rule. There is a difference between a data file a document and COMPUTER PROGRAM.

A COMPUTER program has code, forms and user interface. So if your computer support people don't know the difference between a document and a computer program then this would be the source of your problems.

As a result, you don't allow multiple people into the one program. I mean, if one person in your building has trouble with word does everyone go home?

And while you work on the NEXT great version of your software, users should not care.

So, this means:

Like most of your software, you deploy it to each user's workstation. This will allow you to work on the next great version of your software.

When you are done your changes and added new code and new features you then:

Compile the program into an executable (in Access, this is an mde, or now an accDE. You then deploy this to each users workstation.

The above approach thus allows you to work on the NEXT GREAT version of your software. You can add to your software some update code, or even just adopt a logon script that copies down the next great version of this software to each user's desktop.

The result is:

You don't care if users are in their current version of their software, since you are working on that copy which is not locked.

So, you should be distribution a compiled version of your software, and like most of your software you REALLY WANT to distribute that software to each user's workstation.

Last but not least:

Programs like Word, or Excel have what is called re-entrant code. So if you are working in a terminal server environment, then those office programs tolerate multiple users on that server, but your program created with Access DOES NOT. So even in this case, each logged on user is to receive their OWN SEPARATE copy of the COMPILED program you created.

So how c++ works, vb.net, VB6 or in this case Access works is the SAME for most programs:

You distribute that program to EACH user's desktop, and thus the issue of you working on the next great version is a non-issue. You simply have to setup and adopt a means to check for a new version. That means you distribute a program update, not a single form update. Even in the case of changing come code, or modify a report, that will cause you to like MOST software to have to issue a new version.

So, if you issue a new version of your software you not have the problem you outline. Those users don't have to get out of their current version since you are working on a copy of the program.

You thus need to adopt some system and code that will allow you to roll out that update to each user.

I explain in details not only should you run a split database with the data and tables separate (as you have), but ALSO why you distribute a compiled edition and ALSO why EACH desktop should receive a copy of this new program here:

http://www.kallal.ca/Articles/split/index.htm

So the ONLY basic concept you need to grasp here is that in the computer and software industry we have a concept of a computer program. Once you are able to grasp that you are creating a computer program, then you can NOW grasp the concept that such programs AFTER you create such a program or AFTER you modify or create a new version of that program, you THEN distribute that NEW program to your user base. This is quite much how all software works, and Access is no different in this regards.

You don't try to include one form into the production software, but add the new form, modify code and then COMPILE the software into an executable. In fact, using a compiled Access application is strongly recommend since un-handled errors NEVER re-set global variables and as a result your software becomes far more reliable and that even includes code without error handling since local and global vars are never re-set.

Answer 2

One solution would be to maintain a copy of all forms, reports, modules, queries, etc (front-end code) in a separate database from the tables. You can then link the tables to a 2nd database (back-end) in a shared network Access DB. The front-end database would be deployed to each workstation, while the back-end database is shared. Obviously, this introduces the need to update each workstation, but it means that when you need to roll out new front-end logic, you don't have to go around kicking everybody out at once.

Otherwise, sadly, you need to acquire an exclusive lock on the database, and the only way to do that, is to be the only one with it open.

With the help of a script, you could automate pulling the latest "client". You could just ask all users to exit the app and double-click a batch file which copies the front-end from some network location.