Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Red face Unanswered: Access is so slow

    Hi all I have a ADP Access is on the front end and SQL Server 2005 is the engine, my problem is that when I press a command button on the switchboard I created, to get to the data entry form it takes forever and I'm not sure why. I was wondering when we upgrade to Access 2007 will the problem continue? What could be the cause, there only 7998 records in it.

    Help Please

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Could depend on many things. Do you have 1 or more subforms or sub-subforms in your mainform. Are you opening data entry from, what is the recordset? Is it opening all the records? (which in turn loads in all the records to the subforms,etc...) Do you have tab controls which are based on subforms where you don't really need to populate the sourceobject of the subform until that tab is selected?

    A key way to get your forms to open fast and efficiently (with subforms), is to open your mainform based on 1 record (ie. use a search type form first to select the recrord you want and then open the data form based on that record. This limits the number of records being openened and will help speed up the process. Make sure the recordsource query in your subforms has criteria in it so it only pulls records with criteria matching an ID field on the mainform. This keeps from loading the entire subform recordset.

    Many comboboxes will slow down forms opening. As well as listboxes. Indexing fields in tables helps. Having a lot of events happen when the form opens causes delays. Using a lot of module calls causes delays. DLookup commands cause delays. Or simply poor coding causes delays. And even a slow connection to the server causes delays.

    You'd need to upload an example to see how we could help you tweak things so they load faster. For myself, I don't allow any more than 3 seconds for a form to load.
    Last edited by pkstormy; 06-29-08 at 21:01.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there are other things you can do, if after applying all of PKStormy's suggestions you still have a 'slow' form. you could load the pig of a form as soon as ADP is opened but keep it hidden, when a user finishes with the form instead of closing it hide it... and close it when the application ends.

    one thing to bear in mind is that it may be a problem to you (and your employers), but not to your users...

    the prime source of problems is, as PKStormy suggests are..
    loading necessary data
    doing necessary events in background as part of the laod process
    having loads and loads of combo/list boxes

    possibly using bound forms, although Im guessing you are using unbound forms....
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Aren't ADPs bound by definition?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    Aren't ADPs bound by definition?
    dunno.. never had the need to use 'em, and now they are deprecated I don't think I ever will use 'em
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Red face

    Quote Originally Posted by pootle flump
    Aren't ADPs bound by definition?
    I would love to get it off access but I'm coming against allot of resistance, if you know what I mean

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Desi - your description is pretty vague and Access is being blamed. Have you run server side traces? Tested the network? Run the same calls from SSMS on the same machine?
    If it is defo the Access client that is the problem exactly what happens when you press the switchboard button?

    In short - no - I would stop just short of guaranteeing that upgrading to 2007 will not sort out the problem.

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by healdem
    there are other things you can do, if after applying all of PKStormy's suggestions you still have a 'slow' form. you could load the pig of a form as soon as ADP is opened but keep it hidden, when a user finishes with the form instead of closing it hide it... and close it when the application ends.

    one thing to bear in mind is that it may be a problem to you (and your employers), but not to your users...

    the prime source of problems is, as PKStormy suggests are..
    loading necessary data
    doing necessary events in background as part of the laod process
    having loads and loads of combo/list boxes

    possibly using bound forms, although Im guessing you are using unbound forms....
    Thanks guys, how do I hide data entry form??

  9. #9
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Red face

    Quote Originally Posted by healdem
    there are other things you can do, if after applying all of PKStormy's suggestions you still have a 'slow' form. you could load the pig of a form as soon as ADP is opened but keep it hidden, when a user finishes with the form instead of closing it hide it... and close it when the application ends.

    one thing to bear in mind is that it may be a problem to you (and your employers), but not to your users...

    the prime source of problems is, as PKStormy suggests are..
    loading necessary data
    doing necessary events in background as part of the laod process
    having loads and loads of combo/list boxes

    possibly using bound forms, although Im guessing you are using unbound forms....
    I would like to make so that it doesnt load all the records at once, but unsure how to do that. Also it does have subforms and one tab control so there ya go that will take time to load.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    set a filter in the on load event, arguably you could lod with no records say where id< -1
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by pootle flump
    Desi - your description is pretty vague and Access is being blamed. Have you run server side traces? Tested the network? Run the same calls from SSMS on the same machine?
    If it is defo the Access client that is the problem exactly what happens when you press the switchboard button?

    In short - no - I would stop just short of guaranteeing that upgrading to 2007 will not sort out the problem.

    You know I know I think your right Pootle. Poor Access I've been so mean to it. Thanks Pootle

  12. #12
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Red face

    Quote Originally Posted by healdem
    set a filter in the on load event, arguably you could lod with no records say where id< -1
    Well for some reason I keep gettting an error message when I try to run the macro in the onload.
    Code:
    =[Forms]![EmployeeGamingLicense].[LASTNAME]
    I am defintely doing something wrong
    Last edited by desireemm; 07-02-08 at 15:49.

Posting Permissions

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