Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2006
    Posts
    60

    Unanswered: Slow Open - Sort of fixed it give me some advice.

    i am just wondering why my database was/is so slow. i was having some problems with it opening really slowly. When you first opened the front end, it would load a form and hide the database window. i set this up via the Startup option thingo in access. sometimes the database would open in ten seconds, sometimes a minute. i tried making it an mde but that was no better.

    so today i tried something else i seen. i took the open form thingo out of the Startup option and made a macro called autoexec. then in that i opened the form. now on the database this worked real well, and seems to open the database within a few seconds every time. i made it into an mde file, and it started taking 30 seconds. how come this happened and what else can i do to make it open quicker?

  2. #2
    Join Date
    Dec 2005
    Location
    Arnhem, Gld, NL
    Posts
    21
    I was just about to start a topic on this issue to..

    I have a MS Access application with about 30 screens,.. like ten modules, about 5 reports and very much queries.

    When i start the application i seems like it is loading ALL the forms and queries,.. It even loads form that are not visible and have the visible option set to false.

    This realy slows down my application because one of the slowest forms is not visible all the time, but it loads on startup..


    Does anyone know how to prevent particular forms from loading on startup, but only allwoing them to load when they showup?

    Cheers
    Eelko

  3. #3
    Join Date
    Feb 2006
    Posts
    60
    maybe set there enable to false or something? then enable them when they are initially opened then just invisible them etc.

  4. #4
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    most of the time, when in design time, the database will not be cleaned with deleted parts of code, changed parts will be doubled, which eventualy will lead to a growing and corrupted database, may be you can implement (during design time) the piece this code :

    If Not IsCompiled() Then
    MsgTxt = "The program file is currently in an uncompiled state" & Chr$(13) & Chr$(10)
    MsgTxt = MsgTxt & "This can result in severe performance problems." & Chr$(13) & Chr$(10)
    MsgTxt = MsgTxt & "Would you like to compile the program file?"
    Response = MsgBox(MsgTxt, vbYesNo + vbQuestion, "Performance Check")
    If Response = vbYes Then
    'You could display a message box that tells the user to be patient while the database is compiled
    DoCmd.Echo False 'Stop screen display to eliminate seeing the module open and close
    DoCmd.OpenModule "ANYMODULE" 'Open any of your modules you have in the database
    DoCmd.SelectObject acModule, "ANYMODULE" 'You must select the module after you open it
    RunCommand (acCmdCompileAndSaveAllModules) 'This command does the compile and save
    DoCmd.Close acModule, "ANYMODULE" 'Close the chosen module after the compile and save is done
    DoCmd.Echo True 'Start screen display again
    MsgTxt = " The program file has been compiled" & Chr$(13) & Chr$(10)
    MsgTxt = MsgTxt & "You should now select Tools | Database Utilities | Compact Database" & Chr$(13) & Chr$(10)
    MsgTxt = MsgTxt & "from the menu above the database container. "
    Response = MsgBox(MsgTxt, vbInformation, "Compile Completed Successfully")
    End If
    End If
    Last edited by emiel; 04-09-06 at 22:45.

  5. #5
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Some of the common tricks, starting with the a2k version, is

    * turn off the Name AutoCorrect thingie (Tools | Options - General tab) - this both helps avoiding corruption, and might also speed up load times
    * in each table (design view - properties), alter then Subdatasheet Nane property to "none"
    * compact repair from time to time - AND - do a /decompile (detailed instructions here http://www.granite.ab.ca/access/decompile.htm), the latter which can speed up both load time and resolve code corruption

    then all the other tricks and tips with regards to performance - indices ... here are two links http://www.granite.ab.ca/access/performancefaq.htm, http://www.fmsinc.com/tpapers/faster/index.html

    I think, starting with the a2k version, that when using routines in a module, Access will load the whole module into memory - if this is true, then don't call routines in "huge" modules from the startup routines/startup form - make it as lightweight as possible - perhaps only inline code?
    Roy-Vidar

  6. #6
    Join Date
    Dec 2005
    Location
    Arnhem, Gld, NL
    Posts
    21
    Setting enabled to false is one to go for,.. i have not tried that yet.. will give it a go.

    Emiel,.. i have seen your piece of code elsewhere before,.. does it do the same as starting access with /decompile?

    Roy,.. i have used the decompile option before,.. and i use optimze and decompress and repair often to keep file sizes as little as can be possible.
    But as i say,.. it decreases the file size of my access file. it does not make it load faster.
    I'm now looking to your links,.. thanx for those,.. it might help me some!

    I use Access to make forms and reports, the data is taken from a Mysql database to wich i connect using a odbc driver.
    All the functionality of my forms is written in VBA, i do not use the functions that access gives me, cuz i thnk they are impossible to maintain due to the strange codes they produce..

    I know i should look into a VB.net or something but the application started with a simple layout and i might be overgrowing access by now..

    Is there anyone with more tips on how to prevent access from loading all forms at startup, or how to optimize a application that uses a mysql database..
    Last edited by MoonCrawler; 04-10-06 at 07:00.

  7. #7
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    Yes it decompiles and re-compiles, the only thing you have to aware of that if there is a slight bug in the code, this piece of code will be invoked each time it is called. So compile in that case in codedesign to find the bug.

    emiel

  8. #8
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Are you really, really sure it actually decompiles as in using the /decompile option, or does it really just compile (or recompile, if you like)? It seems only to compile, and I only thought decompile was available as a commandline switch.

    Some info on /decompile http://www.trigeminal.com/usenet/usenet004.asp?1033
    Roy-Vidar

  9. #9
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    As far as the explanation for this code was introduced it is said that it does the same as the /decompile, i will try to find the site again, and post it here.
    emiel

  10. #10
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    Hello RoyVidar,

    i cannot locate the site where i found this particular piece of code, i found several other sites containing the same piece of code, but not one of them is telling something about doing /decompile, so i think i have to withdraw what i stated before about including the 'decompile and recompile'.

    probably i have misread the explanation.

    Though it would be nice if it could be implemented in this piece of code ...

    emiel

  11. #11
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I believe that /decompile is a command line switch only. Also, it is something you should do on a copy in case it screws it up as there is no undo option. Decompile is not something you need to do on a regular basis - it is something you do after a volley of development work.

    I'm not sure if decompile will provide a visible performance gain (unless you're app is huge and has never been decompiled). The explanation I remember is that as you edit code, create new objects, edit more code, remove code, etc. that fragments get broken off from their parent objects and are no longer visible to you, but still there, taking up space and may cause corruption over time. The principle function of decompile is to remove these fragments, make sure all the references and related code are current and leave you with a smaller file and reduce the risk of corruption and crashing.

    Another trick to increasing performance is to make your sub forms, list boxes and combo boxes unbound until they are called. When a form opens, a bound query object will requery 3 times as the form opens. If your query is slow to begin with, multiply it by 3. The alternative is to add an event to the Got Focus event that checks to see if rowsource is blank. If it is, then apply the desired rowsource and requery (use the hourlgass so the user knows it is thinking). This shifts the wait to when the control is clicked instead of On Open and reduces it by a factor of 3. Also, the delay is not there the next time they click on it.

    Speaking of query speeds, open each form to see which are slow. A poorly designed query can take a long time to run, but is generally easy to fix. Also, try to avoid using multiple functions in a query - Access has to recompile the function on every record. If you have stacked functions, move them to a module as a single function and reference that in your query.

    Finally, network perofrmance is a HUGE factor. Always try to have the front end local - running that from the network is just asking for delays. To truly test the speed of your app by itself, all of the data and the front end both need to be local when you test it. A sluggish network connection will slow down everything.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  12. #12
    Join Date
    Feb 2006
    Posts
    60
    how do you make the hourglass appear?

  13. #13
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    DoCmd.Hourglass True ' turns the mouse to hourglass
    DoCmd.Hourglass False ' turn the mouse back to pointer

  14. #14
    Join Date
    Feb 2006
    Posts
    60
    how do i make a progress bar. for like when importing a text file, or when initially opening the database?

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depending on what version of Access you have you may not have a licenseced version of the progress bar contol

    to find the control select the "more controls" button (looks like a hammer crossing a spanner) on the tool bar and scroll through the list untill you find something similar to "Microsoft ProgressBar Control", then add that control to your form.

    if you are importing a file, then you potretnail issue is how you will know when to upfdate your progress bar control. if you are handling your import using a file read and update cycle then you can approximate the progress bar. it won't however handle a file import, as effectively Access shells out to do the import process and then eturns once the import is complete. uyou have no indication during the import profcess if anything is happening, or how far the application is in completing the import.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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