Two Front-Ends for same db

[Pam]

Hi,

I have a large database that is split fe/be and seems to be constantly
growing with user needs. I have shop employees who use it to enter work
times, coordinators who quote and enter progress of jobs, and supervisors
who use it to track and trend data. My question is would it be better (as in
preventing slow db conditions) to have all queries and reports for tracking
to be in a separate front end for only supervisor use or because the tables
are still linked it wouldn't be worth creating another front-end?

Thanks in advance for any suggestions.
Pam

 

[John Spencer]

I doubt that you would see much performance change by having multiple front ends.

You might consider having separate front ends for other reasons. Such as you
want to limit certain queries and reports to certain types of users. Of
course that is often better controlled through knowing which user is logged in
and then controlling access using that information.

If your database is slow, it may be that you have missed some of the better
design decisions.

For instance,
== never (well, almost never) use a table as a record source always use queries
== Limit the number of records you retrieve to just the records needed by the
user at that time
== Index fields that are used often in query criteria and sorting (especially
in large tables). DO NOT index every field

Quote from Tony Toews
The three most common performance problems in Access are:
- LDB locking which a persistent recordset connection fixes
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off
(Tools: Options: General Uncheck all name AutoCorrect features)
Other reasons are
- New format of Access 2000 MDB
- Place backend MDB on the root of the network share rather than several
folders down
- Shorten the name of the backend MDB
- Miscellaneous Performance Suggestions
End quote

Check out the following URLs for a more complete discussion.

http://www.granite.ab.ca/access/performancefaq.htm

http://support.microsoft.com/?id=209126

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Arvin Meyer [MVP]]

I agree with practically everything John remarked on the subject. It really
depends upon how many users and how the work is divided. I have implemented
12 different front-end databases connected to a single back end, but there
were as many as 53 users implementing 75 total instances at the same time.

In the case above, there was a strong division of labor with almost no one
needing to open the entire thing. Indeed, since the database wasn't secured
for most of its life, it kept workers from meddling where they shouldn't.

As John said, performance-wise, there won't be a noticeable difference. If
RAM is marginal, and a network is slow. initial load times may be affected,
but generally a database is only loaded a few times a day, and a few seconds
won't matter much.

 

[David W. Fenton]

You might consider having separate front ends for other reasons.
Such as you want to limit certain queries and reports to certain
types of users. Of course that is often better controlled through
knowing which user is logged in and then controlling access using
that information.

If you're contemplating this and you're going to end up with
significant overlap between the two front ends, I'd definitely
recommend not forking the two front ends, and simply showing/hiding
whatever objects/functionality based on whatever different user
populations you are serving. How you manage group membership is
something of an issue, but it could be as easy as making one group
log on as the default admin user and having a second group log on as
a different user.

 

[Pam]

Thanks to all for the info provided. I'm just trying to figure which would
be the best format to use.

Pam