Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post

    ADOX Catalog (Access XP - SP3)

    I have been using ADOX to list and manage linked tables for some time with no problems. Suddenly the code is now taking forever to run. Everything compiles OK. The problem seems to occur at the first execution of the For each statement in the following example. My FE has had some 50 linked tables, even when there was no speed issues. About half the time the routine hangs, and the times that it does work it takes about 1 to 2 minutes to complete the first occurrence of the For each statement versus jsut a fes seconds to run perviously. I know I've inadvertantly done something stupid, but I don't seem to be able to figure out what. Any thoughts would be most appreciated.



    Dim cat1 As ADOX.Catalog
    Dim tbl1 As ADOX.Table

    Dim mwd As Integer, i As Integer
    Set cat1 = New ADOX.Catalog
    cat1.ActiveConnection = CurrentProject.Connection
    mwd = 1
    For Each tbl1 In cat1.Tables
    If (tbl1.Type = "LINK" Or tbl1.Type = "PASS-THROUGH") _
    And Left(tbl1.Name, 4) <> "~TMP" Then
    If mwd < Len(tbl1.Name) Then mwd = Len(tbl1.Name)
    End If
    Next tbl1

  2. #2
    Lounger
    Join Date
    Oct 2001
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADOX Catalog (Access XP - SP3)

    If the code complies fine and no changes were made to the coding to account for this sudden decrease in performance in your front end, then I would concentrate on the back-end. You said the front-end has some 50 linked tables, but are they all linked to the same source or different sources. First thing I would do is look at the permissions and make sure that nothing has been changed there.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: ADOX Catalog (Access XP - SP3)

    Appreciate your thoughts/suggestions.

    All the linked tables reside in the same BE mdb. Permissions are still OK ... there have been no changes.

  4. #4
    Lounger
    Join Date
    Oct 2001
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADOX Catalog (Access XP - SP3)

    One more thing - did it slow down after installing SP3 which came out recently. If it did, then I would uninstall SP3 and try the performance. The only other thing is maybe there is some corrupt data within one of the tables.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: ADOX Catalog (Access XP - SP3)

    Another good idea. However, I have a second machine that I keep one Service Release back to test just such situations. I'm getting the same performance issues under SP-2.

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

    Re: ADOX Catalog (Access XP - SP3)

    Do you have the subdatasheets turned off on these tables , in both the front and back end? Subdatasheets are a noticeable drag on performance and they're on by default. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

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

    Re: ADOX Catalog (Access XP - SP3)

    Try turning of the AutoCorrect feature, this definitely slows a database.

  8. #8
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: ADOX Catalog (Access XP - SP3)

    Charlotte,

    Exactly right. In testing I had set up a large number of these over time. Now removed the code runs great.

Posting Permissions

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