Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Oct 2007
    Posts
    214

    Exclamation Unanswered: Access Data Base Slowing Down :(

    Hi All,

    I have an access database that I designed and maintain myself. I'm approaching approximately 10 thousand records. Well, when I use the search feauture to search for records (I usually search via social security number) it takes longer and longer to find the records. Is there any way to speed up the search? Basically, I use the database to track workmans comp claims and perform a main search using the "find" button and I search by social.

    Is there a different way to speed up the search? Can access hold more than 10k records?

    Thanks for any help!!!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Index the Social security Number field.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    It also depends on how you're doing the search. You'll need to explain how you're doing that a little more in-depth for more help.
    Last edited by pkstormy; 08-25-08 at 04:04.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Instead of using a find button, also try setting recordsource to query with parameters of a "search ss:" type textbox where a value is entered into an unbound text box and then the recordsource (with the ss criteria) is requeired.
    Last edited by pkstormy; 08-25-08 at 04:05.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    There are some examples in the code bank on different ways to search on different things. Try looking at some of the different ways and you'll get some ideas on how do searches differently.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    And Yes. Access can hold far more than 10,000 records, even into the 100,000's records (and millions records).
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Oct 2007
    Posts
    214
    Hi there,

    Thanks for responding. Can you give a run down on how I might do that? I'm still learning access. How would I go about that? Do I need to go in the code builder or just make a text box and define it as an SS search? How would that be done? Thanks I appreciate the advice!

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    OMG nice posting pkspammy

    Just index the social security number field first and see if that solves the problem
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agree index on the relevant columns. .that shoud speed things up

    however it could also be that the db is bloating, try a compact and repair of the db (look under the tools tab).. make certain you take a backup copy of the db prior to doing anything.

    Compact and Repair should be doen periodically on any Access/JET MDB which is being used for development, and any production db which uses a lot of temporary tables or ads & deletes records frequently. as to frequency of C&R, for a development db I'd argue at least weekly, possibly daily if you are constantly making major changes, a production db probably weekly or mebe monthly (or immedaitely after adding/deleting a large number of records). But I'd be pragmatic about it rather than dogmatic.. if after doing your C&R you are retrieving less than... say a Mb or couple of Mb (Or less than say 10% of the fle size on small db's) then extend the time between C&R's, if you are regularly retrieving multi MB's & over say 20% of space then reduce the interval between C&R's.

    you can do the compact and repair as part of a scheduled event.. eg after overnight backups, but thats for another post (IM sure there are threads n the access forum which already cover that)
    Last edited by healdem; 08-25-08 at 05:19.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Oct 2007
    Posts
    214
    Hi,

    The only reason I haven't indexed the SSN as a primary key is becase sometimes there are more than one record with the same ssn. As you guys know access will flag that and not allow you to create more than one with the same numbers. I'm in my learning stages with access so it's taking me awhile to work out the kwirks. Thanks!

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Nobody mentioned "primary key"

    Just index the field: change its Indexed property to Yes (Duplicates OK).
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Oct 2007
    Posts
    214
    Okay got ya. It's indexed as duplicates ok. But it still takes a while to search ... I'm just using the basic find and replace function built in to access. I'm trying to understand how to do this a bit different.

  13. #13
    Join Date
    Oct 2007
    Posts
    214
    I understand pkstormy's idea, but I'm a bit unclear how to make the unbount text box display the record after I enter the SSN in the text box. Perhaps I'm not understand the recordsource function. Thanks!

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Search and Replace is VERY slow!

    What is your Record Source now?

    It should just be a matter of changing it... for example, assuming SSN is TEXT and your Record Source is "qryMyForm" you could alter it:

    Me.RecordSource = "SELECT * FROM qryMyForm WHERE SSN = """ & Me.SSN & """"

    Using a tiny bit of code... which I have just given you
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  15. #15
    Join Date
    Oct 2007
    Posts
    214
    Hi,

    How would i find out what my record source is? Where would i enter that code? When i open my form, then click design view, then properties, i see nothing that reads "record source" like the access help page tells me is should see. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •