First Multi-User Application

  • Thread starter Thread starter Kevin Sprinkel
  • Start date Start date
K

Kevin Sprinkel

I am not an IT professional, but have, over the years,
created a number of Access applications, all of which up
to now have been for a single user.

We are a small consulting firm with 13 staff members, and
recently installed a file server, prior to which we were
on a peer-to-peer network.

Now so empowered, we are interested in Access applications
we might all use over the network. I've read a little
about this, but it's not clear to me how large a learning
curve is ahead, or if it is worth my time to climb it,
considering I don't have any substantive knowledge of
network topography or system software, and might not know
how to diagnose and correct system-level issues.

I'd appreciate any advice or opinions.
 
Access is already a multi-user application, so you know need to know a lot
about network systems and so forth. However, there are some things to do in
the creation of your application:

1) Split the database into a FrontEnd (FE) and a BackEnd (BE). The BE holds
just the tables and the FE has everything else *except* the tables.

2) Put the BE on a network drive. Link the tables from the BE to the FE.
Use the UNC by going to network neighborhood to search for the server,
rather than a drive letter.

3) The FE can be either on the server or on each individual machine. There
advantages and disadvantages to each.
a) FE on the Server. All you have to do is make application changes to
this one file and everybody automatically gets them. However the
performance can suffer and if you use any temporary tables, people can
collide while using them.
b) FE on each workstation. Performance is better. No problem with
collisions of temporary tables. However you have to build some mechanism to
make sure everyone's copy is updated each time a change is made to the
application. There are several methods available out there. I have one on
my website called "KeepingDatabasesInSync", but there are others as well.

4) Read up on Record Locking. Access 2000 and up has several options
available. Most of my applications run just fine with standard page
locking, but if you are data entry intense, record-level locking might be
better.

Sometimes performance can become an issue when you split a database and put
it on a network. If this happens there are additional things you can do:
Limit the number of values in combo boxes, load subforms only when used,
load only one record at a time into a form, rather than loading the whole
table and filtering, and others. I'd wait to see if it is an issue first.
 
Thank you, Roger, for all your on-target information. It
sounds like it will be worth the effort.

One issue I forgot to mention. Only I own a valid copy of
Access. Since I'm the only in-house developer, we were
planning to upgrade to or buy the Developer Edition to
enable those without Access to use the application.

Does this in any way complicate application upgrades?

TIA
Kevin Sprinkel
 
Creating a run-time package of your application requires some additional
considerations.

1) You do NOT want to use ANY macros. If the macro halts using the run-time
version of Access, the entire application crashes, not just the macro.

2) A related issue is that you will want to error trap EVERY function and
subroutine to allow for a graceful exit in case of an error. You'll also
want to provide meaningful error messages.

3) Most of the standard Access menus and toolbars will not be available, so
you will have to be sure to program all functions that the user wants. You
won't (for instance) be able to use the Find (binoculars) function on the
toolbar.

There may be others, but that's all I can think of at the moment.
 
Back
Top