report problem

[brino]

hi all !

i have a database with multiple users accessing it.
the problem is when one user tries to generate a report and another
user is entering data , the report takes a very long time to
generate.there are quite a number of queries needed to generate the
report.
if only one person is accessing the database then the reporting time is
fine.
how can i fix this problem ???

thanks
brino

 

[strive4peace]

Hi Brino,

does each user have thier own front-End (FE) ?

Access 97 was very stable and multiple users in the FE seemed to be
okay. With later releases of Access, however, it has become much better
to give each user their own FE.

This also has advantages... for instance, you can have tables resident
in the FE with user preferances -- such as last used criteria on a
report menu, path to import/export files, default form, ...

to manually split a database into front-end (FE) and back-end (BE):


1. Make a blank database -- this will be your Back-End, BE

Name it "YourDatabaseName_BE.mdb"

2. import all the tables -- this will bring in the relationships too
File, Get External Data, Import...

This will be the common Back-end (BE) that everybody shares if you have
multiple users.

3. Then, open your working database... which is now your Front-End, FE

4. DELETE all the tables

5. Compact/Repair
Tools, Database utilities, Compact/Repair

6. link to the user tables in the BE
File, Get External Data, Link Tables...

7. this will be your Front-end (FE)

8. Close the FE database

9. make a copy of the FE for each of your users.
(if there are multiple users) and make a backup

I like to name database files like this:
databasename_yymmdd.mdb

ie: AddressBook_061027.mdb

maybe even add a time indicator onto the end...

ie: AddressBook_061027_5p.mdb

that way, you can ctrl-drag the database to another directory, then
rename it, then continue development -- and the backups will be in
chronological order when sorted.
Unlike the other Microsoft applications, where you specifically save,
when you open a database, Access changes the time/date stamp, so the
file Date Modified is not a good indicator of the latest version of a
database.

so now, you have a FE for each of your users -- but everybody links to
the same BE

once you get used to working this way, you will appreciate the
convenience of having shared data and forms/reports/programs seperated.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[brino]

thanks for your help crystal !
however this is what i did in the first place - i created a front end /
back end database and i came across problems.
the database was running "unworkably" slow.
i then changed it back to a single file database and it worked heaps
faster across the network ... dont ask me why i dont know.
it was much better especially the report side of things.
i was thinking of running another database along side the main one to
use just for reporting so that when a user is entering data in it
doesnt interfere with the reports.
the 2 databases will be be synchronized.
any way thats all i could think of to solve the issue.

thanks
brino

 

[strive4peace]

Hi Brino,

A database to linked tables is going to be slower than one with resident
tables. For the code that is "unworkably" slow, what is it doing?

There are a number of things you can do to improve performance. One
thing is to set the Subdatasheet property in all tables to [None].
Another is to remove Name AutoCorrect, and the list goes on...

Here are a couple links for you:

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

http://allenbrowne.com/ser-25.html

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[aaron.kempf]

if you used SQL Server then you would have a profiler to be able to
DETERMINE exactly what was happening.

as it is; MDB isn't a usable functional tool for most business.

I would migrate it to SQL 2000 or 2005 (especially if you can upsize to
ADP 2007)

-Aaron