Front-end, back-end MDBs

[Guest]

Hi all,

I have a conference registration MDB (code & data) which, until now, has
been mainly used by a single user. That is going to change with multiple
users needing to simultaneously access the data across a mini-LAN. My
questions:
1 - is it better to separate the MDB into the front-end (code, forms) and
back-end (tables)? I suspect it is but am not sure why.
2 - if I split the MDB, is there a "best practice" approach?

Many thanks!

 

[Guest]

I split all my databases into front/back end, for two reasons.

1. Multi-user environment, as in your case.
2. If I need to update the program file (add a report or fix some code), I
do not have to lock the users out while I am fixing it - only while I am
copying it back in place. With a single file, I have to deny data entry
almost any time I am programming, not just for the 30 seconds it takes to put
a new copy in place.

In addition, I usually just give each user a shortcut to a centralized copy
of the program file so that I do not have to distribute program updates; all
I have to do is copy the new version over the old one at a moment when nobody
has the DB open.

Notes:

Network latency: be forewarned that beyond one or two users, Access backend
over the LAN can slow down the front end if you use complex queries and/or a
lot of controls on your forms. Eventually, you may find yourself migrating
towards Access projects with SQL (MSDE back end)) to resolve this one.

Paths: To ensure that the linked files work for the clients, make sure the
path to the data file is the same for the client PC's as for your development
PC, or else use the Windows API call to allow each client to browse for and
refresh the file links when you issue an update.

Relationships: set all the referential integrity (table relationships) in
the data file so that the system can manage it for you.

Transition: Backup your database first. Then copy the entire database as-is.
Strip all the tables from one (this will be the program file) and all the
forms, queries, and reports from the other (this will be the data file).
Compact and repair both. In the program file, create links to the tables in
the data file.

Security/Audit: depending on the security and audit requirements, you may
want to set up a user login structure and possibly record the current user's
ID/name, along with the record creation and/or edit time in certain key
tables. This can add a lot of programming time, though.

DB integrity: the more users there are, the more error-prone is the
application. Strong referential integrity helps, as does turning off most
menu options so that users cannot add/delete records except as your program
allows.

 

[Douglas J Steele]

1 - is it better to separate the MDB into the front-end (code, forms) and
back-end (tables)? I suspect it is but am not sure why.

Definitely yes. It reduces the chance of corruption. As well, you cannot
make design changes unless you have the database open exclusively.
Unfortunately, sometimes changing the recordset or filter for a form
consitutes a design change, and you can run into problems.

2 - if I split the MDB, is there a "best practice" approach?

Only the back-end should be on the server. Each user should have his/her own
copy of the front-end on their hard drive.

 

[Guest]

Great! Thanks Brian - a far more comprehensive replay than I ever expected!

Fortunately, I'm only dealing with a couple of users so latency shouldn't be
too bad. The path issue is going to be "interesting", I suspect...

Many thanks,
Zanuck
So many places, so little time.

 

[Guest]

Path won't be a problem if you use a mapped drive letter and ensure that the
full path is the same for you while developing as it is for clients while
using. You can even map a drive to another system and just make sure the rest
of the path is the same as it will be for the clients.

If you absolutely must have different paths or it may change, I can get you
the code on how to allow the client to first browse for the data file, then
refresh the links. I use this when distributing custom db's to multiple
clients that may have the data file in various locations.

 

[Guest]

mapped drive letter >> sadly, I've found that our drive letters are not
standardised. I get around that by using the UNC of the data file in the
link process, not the mapped letter. However, I'd love to have a look at
the code to allow remapping as it is an issue in another app which,
fortunately, I'm not supporting.

 

[Guest]

Thanks Doug!

 

[Tony Toews]

Network latency: be forewarned that beyond one or two users, Access backend
over the LAN can slow down the front end if you use complex queries and/or a
lot of controls on your forms. Eventually, you may find yourself migrating
towards Access projects with SQL (MSDE back end)) to resolve this one.

Would you like to tell that to my client happily running 25 users on
Access with no performance problems?

Also in the event that you do end up with lots of users doing updates
using linked tables to SQL Server is a much easier migration path than
using ADPs.

Otherwise your points are just fine.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm