Hi Jim,
(disclaimer incase some decides to flame me for making the following
statements, but here are the facts)
Access is a micro database system and will function effectively for
micro operations -- even on the web. But for large scale operations
like yours a micro rdbms just isn't going to cut it as you are seeing.
You already have a macro back end (sql server). So you need to step up
to a macro system to overcome your issues. That would be a .Net front
end which can take advantage of ADO.Net.
ADO.Net was specifically designed to handle concurrency/deadlocking
issues and works like a champ. Performance is phenomenal because .Net
has reduced I/O to the minimum required amount. Classic ADO writes to
the disk for every operation (but still better than ODBC which has teeny
bandwidth compared to ADO and keeps a constant connection open). When
you pull data with classic ADO - it goes to a table on the disk.
ADO.Net pulls data into tables in memory. This is where you get
significant performance. And another improvement is that .Net has
reduce data looping to the minimum required (like for arrays). With
ADO.Net you Fill a dataset - no looping required. And, of course, you
have way more bandwidth.
I have users that need to scroll through hundreds of thousands of
records also. I feel your pain :). I still love Access for the kinds
of operations it was designed for. But for large scale stuff - nothing
beats .Net (unless you need opensource - then Java - but .Net still has
more performance than Java).
Rich
*** Sent via Developersdex
http://www.developersdex.com ***