Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form Load Time (again) (2002)

    I've written before but I am still having problems loading an access form. Some information I can provide is:
    When I am the only user on the network, the form takes 20 seconds to load. When there are other users on the network, the form will take 1.5 minutes to load on my computer and can take as much as 2 to 2.5 minutes to load - depending on the capabilities of the computer being used.
    I am attaching a screen shot of the network activity that occurs when I am the only user. When there are other users, there are intervals where the network usage goes to 2% or so with spikes of activity. This continues until the form loads.
    The form is somewhat complex, but all the data is accessed by code- all the fields are unbound on the form. The form is in a front end database and the data is in a separate database. The tables are linked so that they can be utilized by queries and are also addressed through code.
    We are using Windows Sever 2000 and all computers on the network are using XP - some have XP SP2 installed and others do not (those users insist that part of the problem is XP SP2 but I do not think it makes much of a difference.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Load Time (again) (2002)

    I don't have a solution for your problem, but I don't think Windows XP SP-2 has anything to do with it. I have many databases with the frontend running on Windows XP SP-2, and there has been no change in performance compared to before the upgrade to SP-2.
    Can you have the network administrator perform some tests to see if there is a bottleneck somewhere in the network?

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Form Load Time (again) (2002)

    You situation sounds similar to a problem that existed some time ago with the lock file (.LDB), where the workstation tried several times to delete the lock file before it finally decided someone else had the file open. However that was fixed with one of the later service packs for 2000, and in SP1 of 2002 as I recall. Some suggested things to look at can be found in Microsoft Access Performance FAQ and you might also want to look at a specific bug documented in <!mskb=275085>Microsoft Knowledge Base Article 275085<!/mskb>.
    Wendell

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Form Load Time (again) (2002)

    You said the controls were unbound, but is the form itself bound to a recordsource? And if so, how big is it? Are there alot of of subforma and/or combo boxes with large recordsets? Is Name AutoCorrect still active?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Load Time (again) (2002)

    Mark,
    Yes, the controls are unbound and the form is not bound to a record source. I access all the data by code. The form has a number of tabs, on which are a variety of combo boxes. The largest file addressed by any one of the combo boxes has about 5,000 records.
    John

  6. #6
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Load Time (again) (2002)

    Wendell,
    I used the code at KB 275085 to change the subdatasheet property to [None] for all the tables in the back end database. There may be a slight improvement. I'm attaching a picture of the network activity screen in Task Manager that shows the amount of network time used between the beginning and ending of the form load procedure. The network utilization goes down to less that 1% at times.
    John

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Form Load Time (again) (2002)

    The optimal arrangement for tab controls is to load a tab the first time the user clicks on it by putting code in the tab control's change event. Then all you need do is update it. If you have a lot of tabs and a lot of code, the optimization for the form could be slowing you down, not to mention that SQL built in code can't be optimized.
    Charlotte

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Form Load Time (again) (2002)

    I've gone back and reviewed your previous posts on this issue, but it certainly looks very much like the old problem of Access trying to delete the .LDB file associated with the backend database when someone else has it open. So I think we need a bunch more info to try to help you:<UL><LI>What service packs have been applied to Access 2002?<LI>What version of the MDAC do you have?<LI>What version of the Jet engine do you have? (See <!mskb=239114>Microsoft Knowledge Base Article 239114<!/mskb> to figure that out.)<LI>Do you have a persistent connection to the backend that you leave open?[/list]Finally, would it be possible to post a stripped down version of your front-end database so we can see the basic strategy you are using with the form? Charlotte's suggestion is one we use very frequently - our front-end designs often have 6 to 10 tabs with multiple subforms on each tab. We only link the subforms (and on occasion the combo boxes) when the tab is selected. Using that strategy we routinely get 1 second response times on the selection of a new record.
    Wendell

  9. #9
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Load Time (again) (2002)

    Wendell,
    Those are all good things to know and I will find out that information - I've inherited this system from the previous administrator and not certain all the updates have been applied.
    However, I'm not certain I made my problem clear. We do not have any trouble accessing the data once the form is open - one second access time is the norm. The delay we experience in only when opening the form. Once the form is open, it runs fine.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Form Load Time (again) (2002)

    Hi John,
    Understood about the issue with the form load time - but 20 seconds is fairly long in my view. We try to limit open times for a form to 3 to 5 seconds maximum regardless of the circumstances. One suggestion I intended to put in my previous response was to look at the possibility of using SQL Server 2005 as the back-end database. The Express version of that can be downloaded and played with to get an idea of the benefits, but the primary thing is that it handles multiple users far better than Access, and it only gives you the data that you request, while Access often has to load an entire table across the network. Another question: What is the speed of your network? Access works significantly better on a 100Mbit LAN.
    Wendell

  11. #11
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Load Time (again) (2002)

    Wendell:
    Sorry it has taken so long to reply.

    I have SP3 applied to Access 2002
    MDAC - 2.8 SP1 on Windows XP SP2
    Jet - 4.00.8618.0

    Sorry, I don't understand your question about persistent front end.
    The basic strategy of the form is that it has no data source and all the text boxes, combo boxes are unbound as I access all the data through code. I'm an old COBOL programmer and I like the flexibility that gives.
    The form accesses data from 14 tables when a particular constituent record is chosen. That combo box is loaded by a query on the Names table. It does not access this data upon form opening however as all the controls are unbound. Data access is about 1-2 seconds. The time involved loading the form is 15 seconds when no one else is accessing the back end data database and up to 2 minutes when there are multiple users. We also experience the delay when switching from form view to design view and also from design view to form view. Also experience the delay when saving the form.

    I'd be glad to post a db with the form in it but don't understand how to circumvent the workgroup requirements.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Load Time (again) (2002)

    Access keeps track of who has a database open in the .ldb file associated with the .mdb file. Accessing the .ldb file is slow, so it helps to keep it to a minimum.

    If your forms are unbound, the .ldb file will be accessed each time you open a connection to the backend, and each time you close that connection. To avoid this, you can open a connection when the frontend is opened, and keep this open until the frontend is closed. Such a connection is called a persistent connection. Alternatively, you can open a form bound to a table in the backend and keep it open until the frontend is closed.

    What format is the backend database? I experienced delays like you describe when we were converting from Access 97 to Access 2002 at work. During the transition, users with Access 2002 had Access 2000 format frontends connecting to the old Access 97 backends. This was VERY slow. The delays disappeared once everybody had Access 2002, and the backends were converted to Access 2000 format.

  13. #13
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Load Time (again) (2002)

    Wendell,
    Both the front end and back end are Access 2000 format.
    Regarding the persistent connection: I understand the concept of having a form open all the time that is bound to a table in the backend. I've created such a form and the results are promising.
    However, I'd like to know about the procedure you described of opening a connection when the frontend is opened and keeping it open until the frontend is closed. How does one accomplish that?
    Thanks for the help,

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Load Time (again) (2002)

    In a standard module:

    Public cnn As ADODB.Connection

    You could use an unbound startup form, and open the connection in the On Load event:

    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=CataBasePathMyDb.mdb;Jet OLEDB:System Database=CataBasePathMySystem.mdw;"

    and close it in the On Close event of the form:

    cnn.Close
    Set cnn = Nothing

  15. #15
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Load Time (again) (2002)

    Hans,
    Sorry for my ignorance but when I try to do what you described in your prevous post, I get the error "User defined type not defined"
    I suspect this is due to the fact that I'm using DAO for data access and it appears that's old technology.
    How do I get access to the ADODB capabilities?

Page 1 of 3 123 LastLast

Posting Permissions

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