Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Split satabase slow (Access 2000)

    I have a database where splitting into a front end and back end has caused a critical form to be very slow to open.

    The form in question has 7 tabbed pages, and about 11 subforms. When the database is just a single file, this form opens in about one second, but when split it takes somewhere between 20 and 30 secs to open. The problem occurs in multiple locations, with the data both on a server and on the local hard drive.

    I looked in the Knowledge Base at ACC2000: Slower Performance on Linked Tables which told me to " set the subdatasheet Name property on each table in the back-end database to [NONE]."

    I did that and it didn't help at all - if anything things seemed to get even slower. I have also imported everything to new databases, decompiled, recompiled, compact and repair. I also checked that all the foreign keys were indexed.

    Does anyone have any suggestions of other things to try.
    Regards
    John



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

    Re: Split satabase slow (Access 2000)

    Have you turned off Name AutoCorrect in the General tab of Tools |Options...? This is a per database setting. <!profile=Darsha>Darsha<!/profile> recently reported a fourfold speedup after doing this - see <post#=314102>post 314102</post#>.

    Another thing that may help is to open a persistent connection to the backend that is open during the entire session. This will cause some delay when starting the database, but should speed things up after that. A persistent connection can be a form bound to a table in the back end that is open all the time (possibly hidden), or a recordset that is opened in code when the database is opened.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Split satabase slow (Access 2000)

    Thanks Hans

    I am aware of problems with Autocorrect and keep it turned off. This form is still slow when I convert back to 97 where AutoCorrect didn't exist.

    I will experiment with a persistent connection.

    Thanks also for the link to post 314102 . I will try the suggestion of setting the control source of each subform in the change event of the tab control.
    Regards
    John



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

    Re: Split satabase slow (Access 2000)

    You may need to change the subdatasheet property in the front end as well.
    Charlotte

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Split satabase slow (Access 2000)

    Where do you clear the subdatasheet property?

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

    Re: Split satabase slow (Access 2000)

    You have to do this for each table separately, in design view. Activate the Properties window.

    Alternatively, the following code turns off subdatasheets for all tables in a database:

    Sub TurnOffSubDataSheets()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Set dbs = CurrentDb

    On Error GoTo ErrHandler

    For Each tdf In dbs.TableDefs
    tdf.Properties("SubdatasheetName") = "[None]"
    Next tdf

    ExitHandler:
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    tdf.Properties.Append tdf.CreateProperty("SubdatasheetName", dbText, "[None]")
    Resume
    End Sub

    This code needs a reference to the Microsoft DAO 3.6 Object Library.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Split satabase slow (Access 2000)

    Thanks Hans

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Split satabase slow (Access 2000)

    In the front end what can you do other than "format...subdatasheet..remove" for each table.


    By removing the SourceObject for each subform, and setting them in code using the onchange event for the tab control, I have reduced the load time of the page from 20 secs to about 5. It is still not terrific, but is a big improvement.
    Regards
    John



  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split satabase slow (Access 2000)

    Hi Hans,

    I forgot to ask in the thread I started but how do you actually do this. I created a form and opened a connection with a recordset, but how can I open the form (and keep it hidden) when the database opens?

    Just had a thought, I'm using a switchboard (not a 'proper' one, just a form with buttons to link to other forms) which is always open, so I would this form be a candidate for opening the connection?

    I've never just run code when the database opens, where does this actually go. I'm sure I could muddle through but what concerns me is making sure everything gets closed properly, especially if the the user just quit access, what event needs to be caught to then clean up and close the recordset?

    Just realised this post is a tad heavy on the questions, a shove in the right direction is probably all I need <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

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

    Re: Split satabase slow (Access 2000)

    If you have a switchboard form that is always open, that is a good candidate. You can open a recordset on a table in the back end in the On Open or On Load event of this form, and close it in the On Close or On Unload event of this form; set the object variables (such as the recordset variable) to Nothing as well there.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split satabase slow (Access 2000)

    That'll do for me. At the end of the data any app I'm involved with is likely to have a switchboard of some kind so it makes sense to use it this way if I need to.

    Cheers <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

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

    Re: Split satabase slow (Access 2000)

    Hans,

    In your error handling procedure within your code to remove all datasheets, you actually add the subdatasheetproperty. What are ramifications of this property not being there such that you needed to add it to set it to "[None]"? i.e., what if you just used an "On Error Resume Next" instead?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Split satabase slow (Access 2000)

    Mark,

    Tables set out without having a SubdatasheetName property. You do need the error handler, since trying to set a non-existent property causes an error. Using On Error Resume Next would mean that all tables that don't have this property yet will be skipped, we don't want that. But since I create the property and set its value to "[None]" in the error handling section, I could have used Resume Next instead of Resume in the next to last line; this would be slightly more efficient.

  14. #14
    Star Lounger
    Join Date
    Mar 2003
    Location
    Ilkeston, Derbyshire
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split satabase slow (Access 2000)

    John

    I've been having performance issues similar to yours and have just tried removing the SourceObject for sub forms and coding in the OnChange event. I have to say it's made a helluva difference.

    Many thanks for the tip

    Mark

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Split satabase slow (Access 2000)

    I have found that setting the Source of each of the Combo boxes on a form in the GotFocus event also speeds the form opening quite significantly, 11 seconds down to about 1.5 seconds.
    In other words the form loads quikly when the source of the combo boxes are blank.

Page 1 of 2 12 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
  •