Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zam1030
Starting Member
6 Posts |
 Posted - 2007-10-23 : 04:26:09
|
| Hi,I have an MS Access database (85MB filesize) with a Names table consisting of almost 400K records that is being accessed and updated by the 3 users at the same time using a VBA program. Each user search for a LastName, or a FirstName or Both, and the program will list all records from the Lastname, or Firstname that he entered, and then select a record from that list that the user may update. But due to it's large size, searching and refreshing of list is too long. And so is the updating. Is there a faster way how on how to do this?Names TableSrcIdFirstNameLastNameAddressYour help and suggestions will be highly appreciated.Thanks,Zam |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-23 : 04:30:07
|
| Moving the database to SQL Server might help.We "split" names into individual "words" and store them in a separate table (one "word" per row) and then do our lookup on that. The Lookup Table also has a Soundex column, and the only form of wildcard search we allow is "starts with" (rather than "contains") and that seems to work out pretty fast.Kristen |
 |
|
|
Bit Wrangler
Starting Member
9 Posts |
Posted - 2007-11-02 : 08:29:23
|
| I second the advice to move to SQL Server, but 85MB isn't gigantic for an Access file.Another little Access tweak that may help if you haven't already done it would be to split the file. Put the forms and reports in a separate MDB (or better and MDE) file and put that file on the local machines. Link the tables from the network. I don't expect that to make a huge difference, but it's a good idea when sharing an Access DB.You didn't mention anything about indexing. What indexes do you have set up on the names table? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-11-02 : 10:09:59
|
| 85MB is nothing for Access ... without knowing about your table structure, indexes, code, queries, and so on, it is impossible to give you a "quick fix", but I am certain that no limitation of Access in general is to be blamed. Review your code, your schema, your indexes, your criteria, your forms, your reports and so on and see what can be simplified and optimized. I've seen some really, really bad Access code in my days which is the bottleneck 9 times out of 10, not Access itself.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2007-11-02 : 12:02:05
|
| Depending the version of Access, the older ones(2.0, 95 and 97 but not newer than 2k?) have to lock up whole table in order for one user to modify the data. This makes the other users feel the performance is sloooow.The newer ones only lock the rows one is working on.Another point, ask your users not to filter on a row then leave the screen on. Most of the power users do that all the time. And that could slow down the whole thing.Other than that, you have to split up/break down the table and form to seperate the functional areas. If this is too painful to do, maybe it is time to upgrade to sql server. |
|
|
|
|
|
|
|
|
How to Speed up Query results in MS Access