Deny Database Access To Users When Design Work Being Done

  • Thread starter Thread starter iez44
  • Start date Start date
I

iez44

Overview:
My database has many users who input data throughout the day. The database
can become corrupted if someone attempts to add data at the same time I'm
making design changes. The compact/repair utility in most cases doesn't
work, so the database must be restored with a backup copy. Although the
files are backed up every morning and afternoon, information can be lost and
must be re-entered.

To prevent this from happening, how can I deny access (pun intended!) to the
database when this design work is being done? I'm guessing it's going to
take VBA code to accomplish this.

Is it also possible to have a pop-up message appear to notify the user that
the database is under construction and is not available?

Thanks, Jim
 
Have you split your application into a front-end (containing the queries,
forms, reports, macros and modules) and a back-end (containing the tables
and relations)? That's a must in a multi-user situation.

Only the back-end should be on the server. Each user should have his/her own
copy of the front-end, ideally on his/her hard drive. In that way, you make
your changes to your copy of the front-end, and simply redistribute to all
users once you're done. In fact, Tony Toews has a free Auto FE Updater that
will ensure each user has the correct version of the front-end. See
http://www.granite.ab.ca/access/autofe.htm (that page also links to
information about why it's critical that you do split your application)
 
There are a number of things you are doing incorrectly. You need to change a
lot of what you are dong.

First, it sounds like you have multiple users sharing the same copy of the
mdb. Bad Idea.
My guess is that you also don't have the database split. Another bad idea
You are doing development in a production environment. I would get fired
for that.

Here is what you need to do.

First split your database. Use the Spilt Database wizard in Access. That
will create two mdb files. One that is the same name the application is now.
The other will have _be.mdb added to it. So you would have MyApp.mdb and
MyApp_be.mdb

The _be (backend) will have only tables and relationships.
The other(frontend) will have all your forms, reports, queries, macros, and
modules.

Once the split is complete, put the backend on a shared folder to which all
users have read/write permissions.

Now, you need to change the links to the backend. Open the frontend. Open
the Linked Table Manager and link to the backend database. The most
important thing here is to use UNC paths instead of Drive Letter Paths.
UNC paths are like \\ServerName\FolderName\SubfolderName
That way, individual user drive mapping will not have any effect on your
application.

Now create an mde from your frontend.

Give each user their own copy of the mde version. This is the production
verison, you don't touch it except to replace it with an updated version.

Now, make a copy of the backend and put it in a different folder to use for
development and testing.
Use your copy of the frontend to do your development and testing. When you
are ready to distribute a new version, create a new mde file and distribute
that.

I didn't invent this concept, it is the way it is done correctly.

Best of luck and please post back if you have questions.
 
Doug & Dave,

Thank you very much for your help! You are both correct; my database is set
up incorrectly and I need to make the deisgn changes you suggested. I think
I understand what is required. If not, I'll be back with more questions.
Thanks again. Jim
 
Back
Top