Access 2003 Question

[Herrdue]

My department has about a dozen users who put their hours-worked into
an Access database I developed years ago. The problem is, once a month
we run reports and it seems that the datase gets corrupted quite
frequently when we are trying to run reports AND when users are
entering their hours. This has happened enough for me to believe it's
a case of too many users doing too many things in Access
simultaneously.

Can anyone suggest a means of lessening this burden on Access? For
example, if we moved all the queries and reports into a different
Access database, and linked them to the tables in the original, we'd
then have all the main tables in once Access db where the main users
would enter their hours, and then the people who do all the crazy
queries and reporting would be using a different one. Would this make
a difference? Or is there some other way to go about this?

 

[Tony Toews]

My department has about a dozen users who put their hours-worked into
an Access database I developed years ago. The problem is, once a month
we run reports and it seems that the datase gets corrupted quite
frequently when we are trying to run reports AND when users are
entering their hours. This has happened enough for me to believe it's
a case of too many users doing too many things in Access
simultaneously.

Can anyone suggest a means of lessening this burden on Access? For
example, if we moved all the queries and reports into a different
Access database, and linked them to the tables in the original, we'd
then have all the main tables in once Access db where the main users
would enter their hours, and then the people who do all the crazy
queries and reporting would be using a different one. Would this make
a difference? Or is there some other way to go about this?

Yes, having all the users in one Access database is very likely the
fundamental cause of your problems.

You want to split the MDB into a Front End MDB containing the queries,
forms, reports, macros and modules with just the tables and
relationships in the Back End MDB. The FE is copied to each network
users computer. The FE MDB is linked to the tables in the back end
MDB which resides on a server. You make updates to the FE MDB and
distribute them to the users, likely as an MDE.

See the "Splitting your app into a front end and back end Tips" page
at http://www.granite.ab.ca/access/splitapp/ for more info. See the
no longer free Auto FE Updater utility at
http://www.autofeupdater.com/ to make the distribution of new FEs
relatively painless.. The utility also supports Terminal
Server/Citrix quite nicely.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

 

[John W. Vinson]

My department has about a dozen users who put their hours-worked into
an Access database I developed years ago. The problem is, once a month
we run reports and it seems that the datase gets corrupted quite
frequently when we are trying to run reports AND when users are
entering their hours. This has happened enough for me to believe it's
a case of too many users doing too many things in Access
simultaneously.

Can anyone suggest a means of lessening this burden on Access? For
example, if we moved all the queries and reports into a different
Access database, and linked them to the tables in the original, we'd
then have all the main tables in once Access db where the main users
would enter their hours, and then the people who do all the crazy
queries and reporting would be using a different one. Would this make
a difference? Or is there some other way to go about this?

By all means, split the database into a "backend" containing only tables and a
"frontend" containing the forms, reports, queries and code! This is "best
practice" for all versions of Access. See
http://www.granite.ab.ca/access/splitapp.htm or
http://allenbrowne.com/ser-01.html for discussions of why and how.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com