Tasked with "Speeding up" database

[Guest]

I have an access database (Access 2003) designed to track claims audits
There are anywhere from 15 to 25 folks entering data on a daily basis. There
are also as many as 5 to 10 additional logging in to check current data.
There are, by years' end, over 300,000 records in the database.

I am plagued constantly with record locks that slow down the work being
done. I inherited this thing and I am not sure what to do with this most
recent task of "speeding it up" and limiting downtime due to locks.

I have thought of transferring the entire thing to SQL server and work from
that but I (the company) don't have one here yet and no word on buying one
yet. I have thought of having more than one database (break the audit dep't
into teams) and have each write back to a main each day, but this leads to
duplicates problems (they may audit the same claim number and I need to check
for dupes so they can modify the number entered). Would linking back to the
main cause the same slowdown?

I have also thought of keeping the others out and providing reports but this
will make them most unhappy also. So, I am stuck. I am not looking for
answers in deep detail. Just looking for some ideas.

Other assests I have are Crystal reports. I can export the data table to a
Sybase server and draw reporting from there. I would still have the "real
time" reporting problem.

How many people can traditionally function in an access db at the same time
without too much issue? Is there a way to eliminate record locks (or at
least stave them off some)? Because they are audits, I need to report on
these throughout the year so I have been keeping a years' worth in the db and
pulling out the previous into a now rather large archive db (~1.62 GB right
now).

Any ideas on where to start?

Thanks, John

 

[Paul Overway]

Performance in Access is very much dependent on the application design. You
might be able to continue using Access with some modifications to the
current design...or maybe not. The size of your archive is approaching the
file size limit (2GB) for Access..but you might be able to get around that
by creating archives by year.

Check out http://www.granite.ab.ca/access/performancefaq.htm and make
changes as necessary to your existing design. There are a variety of other
tips to improve performance that I've picked up over the years, but Tony's
page covers the biggest issues pretty well.

 

[Guest]

Thank you very, very much for this link! It led to more links and other
great tips. Looks like I will try the "two database" (FE and BE) approach.
I found great tips and code from the site you listed which will allow the
client side to stay updated.

Thanks again, Paul. I have bookmarked the site and many it led to.

John

 

[Joseph Meehan]

I have an access database (Access 2003) designed to track claims
audits
There are anywhere from 15 to 25 folks entering data on a daily
basis. There are also as many as 5 to 10 additional logging in to
check current data. There are, by years' end, over 300,000 records in
the database.

I am plagued constantly with record locks

I suggest batch processing. Those that are entering data need to do
that on their front ends and on an agreed schedule process the saved data to
the primary database.

There are a lot of factors involved, but you may want to have two copies
of your database. One primary copy and only those editing data can access
it, and a copy that may be refreshed once or multiple times a day, and one
backup file in a safe place, backed up once a day.