Upgrade to 2007 causes slowdown

[DanJ]

I have a large Access database that was originally designed in Access 97 and
has since been upgraded through all the subsequent versions as my customers
purchase the newest versions of Access. One of the forms utilizes a Tab
Control which has 20+ tabs. Most of the tabs contain sub-forms to manage the
one to many data issues. However, the sub-forms are not bound to the Parent
until the user first clicks on the appropriate tab. This is done on the On
Change event of the tab where the Object Source is set at that time. In
addition, when the parent is opened, only specific tabs are made visible
based on a table where the user can designate when to 'reveal' specific tabs.
This of 'late binding' keeps the sub-forms from loading on open of the
parent reducing the time required to open the parent form substantial. It
also makes it easy for the user to navigate to the sub-form they need by
hiding tab pages that are not needed. This method has worked great for all
previous versions allowing users to open the parent and move from one parent
record to another in less than 1 second with Access 2003 and before.
However, now with Access 2007 (not converted - still .MDB), the opening and
moving from one record to another takes over 25 seconds each time the record
is changed. I have narrowed down the bottleneck to Access 2007's speed when
iterating the Page collection of the tab control to 'reveal' the tab pages.
The code is similar to the following:

Public Sub RevealTabs(strStage as String, byVal frm as Form)
Dim db as Database, rst as Recordset, pge as Page

set db = Currentdb()
set rst = db.OpenRecordset("SELECT * FROM qryPageConfig WHERE Stage = '" &
strStage & "'"
With frm.TabCtl1

For Each pge in .Pages
pge.visible = False
Next pge

Do until rst.EOF
.Pages(rst!Pagename).PageIndex = rst.Rank
.Pages(rst!Pagename).Visible = True
rst.movenext
Loop
rst.Close
End with
Set rst = Nothing

End Sub

Why would this code run so slow in Access 2007 versus Access 2003? It would
be a major undertaking to redesign this program from scratch to eliminate the
Tab control. Any help would be greatly appreciated.

 

[Tigerfish]

I am experiencing the same problem with an application recently converted
from Access 2000 to 2007. It has many tabbed forms that uses similar code.
THe back end and front ends have been converted to .accdb format. The back
end resides on a Linux server. I've posted this questions elsewhere. So far
no one has provided much info on what could be causing the slowdown.

Could the Trust Center Settings be causing this problem?

 

[Dan B]

Access 2007 does this with all previous versions of access. If you run an
earlier version of access on 2007 it's dreadfully slow. Microsoft needs to
come up with a patch for this. If you upgrade previous versions of databases,
your forced to do a rewrite. Where's the backwards compatiblity Microsoft?
Where's the patch for this?

There is no patch. SP1 for Office 2007 doesn't help this issue. Do we need
to rewrite in something like OpenOffice before you get the point?

This is a common problem and there is no resolution, aside from rewriting
your code to something else.