Multiple Access Databases for Forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I starting writing what was a small database back in 2000. It has now grown
to be what I consider quite large and I spend almost all my time modifying
something or adding features. While I have a working copy and a production
copy, it invariably becomes necessary to update the database during the day,
either because of a previously unknown bug or a "gotta have it" feature.
Right now, this requires asking everyone to exit the database while I update
the file.

Therefore, my question is: Is it possible for me to break all of the forms,
queries, and reports into separate .mdb files and have the vba programming in
one form open a form in another mdb file? Note that each file would have
forms, reports, and queries that all reference a single area.

Any help or advice would be appreciated.
 
I think you must have everything including your data in one mdb file that all
users are sharing. This is not the way to do it. You should have your
database split. You can use the database splitter (Tools->Database
Utilities->Database Splitter).
This puts all your tables in one mdb and all other objects in another. The
mdb with the data is refered to as the Back End (be). The other is known as
the Front End (fe). The be should be in a shared folder where all users can
get to it. Each user should have a copy of the fe on their own local hard
drive. You then link to fe to the be. Be sure not to use drive letter
addressing:
Not:
F:\SharedData\AccessData
Like This:
\\MyServerName\SharedData\AccessData

Then to make changes to any code, forms, reports, etc, you just copy the new
version of the fe to each user's local disk. It will also greatly improve
performance and avoid corruption problems. It will not prevent corruption,
but they way I think you are doing it is very prone to corruption problems.

Good Luck!
 
Yes, my front and back ends are split. My database is divided into probably
7 distinct areas which are all somehow related. For example, I have a
Contacts section from which Purchase Orders and Sales Order draw their
Vendors and Customers.

Anyway, My hope would be to break the front end down into even smaller
sections (like a seperate mdb for each PO, SO, Contacts, etc) so that if I
update the Contact form, I can update just one file without kicking everyone
out of the whole database. Since people are using this thing 10 hours a day,
and I am here somewhat less than that, finding time when the database is not
in use is difficult.

By the way, I have both the front end and back end on the server. Back end
for obvious reasons. But I found it much easier for updating the front end
if everyone shares a single copy. Otherwise I would have to go 'round to
everyone's computer to do the update. Unless of course, someone knows of a
way of "pushing" updated files to the users PC's on login? WHich would solve
my initial problem of not being able to update the file until everyone logs
out.
 
Having everyone share a single copy of even the fe is a bad idea. It slows
response, doubles network traffic, creates extra growth of your mdb, and
increases probability of corruption. I strongly advise you to reconsider
this approach.

Splitting the front end into smaller pieces can be done, but it complicates
your world. There are a number of ways you can manage this deployment.
1. If you have access to all the users' hard drives, you can copy the new
version over the old.
2. You can send each user an e-mail and tell them there is a new version
3. Add a constant to whatever code module is called when you open the fe.
That constant will be the current version of the fe. Then create a table in
the be that has only one row in it with the current version. Each time the
user opens the fe, check the version number in the table in the be with the
version constant in the fe, and if it is not up to date, present a message
telling the user to copy the new version on to their hard drive, then quit
the database.
 
Reading your post has given me an idea which I might try, but let me run it
past you first. Do you see any problems with creating a batch file that
copies the fe from the server to the users computer and then starts the
program? That way, the production copy can be updated and if the user wants
to use a new feature, all they have to do is end the program and start it
again by double clicking the bath file again.
 
Are you saying that instead of opening the mdb each time, the user would
instead run the batch file to do the copy then start the app? That seems
like a good start, but wouldn't that really slow the app opening from a user
perspective? They would have to wait for the copy then wait for the app to
close?

Maybe I don't understand the sequence of events.
 
Your understanding of the sequence of events is correct. So it would take a
little longer to start the app. I wrote the file and on my computer, it took
about 3 seconds to copy the file. So maybe I'll start with this and see what
happens. Maybe my users will be happy with the slow start if the app really
does run faster from the local drive instead of the server's drive.

Thank you for all of your help and advice!
 
Ok, that would do for a start. Be sure you have the linking set up
correctly. In the long run, you could use the idea of checking version
number and give them a message to run the batch file.

good luck
 
Hi

If instead of using a batch file you used VBScript you could check the dates
of the local fe and shared fe and only download (copy) to local
if the shared copy was newer.

Same idea, a bit more flexibility.

HTH John
 
brba said:
Reading your post has given me an idea which I might try, but let me run it
past you first. Do you see any problems with creating a batch file that
copies the fe from the server to the users computer and then starts the
program? That way, the production copy can be updated and if the user wants
to use a new feature, all they have to do is end the program and start it
again by double clicking the bath file again.
Click to expand...


You should take a look at:

http://www.granite.ab.ca/access/autofe.htm
 
Back
Top