Access 2007 running .mdb using ApplyFilter w parameter query very

[Frank H]

I'm using a db built "long ago" (probably Access 97), which is currently
being used mainly by Access 03 users. When used in Access 2007, performance
is MUCH slower. None of the posts I've found seem to be addressing the same
issue; I'm hoping someone can provide some insight.
I'm not looking to rebuild the db (multiple users enter "incident"
information). I only want to open this can of worms far enough to make
minimum modifications possible, so that its performance is acceptable when
opened with an '07 machine, without messing up it's excellent performance
when opened on an 03 machine.

The .mdb file is on our server. Tables are linked via ODBC to a SQL server
in the same room.

The form (call it frmEntry) has a query (3 joined tables) as it's
recordsource, has 2 tabs containing around 50 text boxes, check boxes, 3
combo boxes. frmEntry is opened via a button on a "main menu" form, via a
macro that opens frmEntry in Add mode (i.e., it's normal starting state is
filtered for entry of a new record). (The recordsource has nearly 89,000
records.)

Buttons on frmEntry run macros to filter the form a variety of ways, all of
the macros use the OpenForm action with the FilterName argument referring to
a named query; each FilterName query is a parameter query with an expression
such as ...Like [Enter the first few letters of the company] & "*"... . This
has worked great in versions up through 2003: although the form is open, the
macro "reopens" the form, causing the parameter query to prompt for user
entry. Performance is less than 1 second.

When the same .mdb is opened with Access 2007 and the users attempts to use
the buttons, the parameter message box does not display, allowing no user
input, causing all 89,000 records to be selected. Some users get it to work
once, but then not a second time. Apparently, 2007 handles this "reopening"
differentlly. And, in general does not seem to filter as quickly.

I've tried:
tweaking the macro:
....added a requery action (causes the parameter to pop up, but generates run
time error when you cancel the message box, and is really slow);
....added an OnError action to catch the runtime error (probably won't work
in 03, and is still really slow);
....tried the Where argument instead of the FilterName. (doesn't pop the user
input box, ergo no worky)
Tested converting a copy of the .mdb to .accdb (no performance improvement)

I can't seem to find any info that would explain this. Anybody got any
insight?

 

[S.Clark]

Rather than the hokey parameter query with a form reopening, build a search
form to capture the input. When the user clicks the go button, open the form
using the criteria. Form would have a textbox and a button at minimum.

docmd.openform "formname", wherecondition = "[Company Name] Like " & [field
from search form] & "*" (The * may need to be a % for SQL Server.)

I'm using a db built "long ago" (probably Access 97), which is currently
being used mainly by Access 03 users. When used in Access 2007, performance
is MUCH slower. None of the posts I've found seem to be addressing the same
issue; I'm hoping someone can provide some insight.
I'm not looking to rebuild the db (multiple users enter "incident"
information). I only want to open this can of worms far enough to make
minimum modifications possible, so that its performance is acceptable when
opened with an '07 machine, without messing up it's excellent performance
when opened on an 03 machine.

The .mdb file is on our server. Tables are linked via ODBC to a SQL server
in the same room.

The form (call it frmEntry) has a query (3 joined tables) as it's
recordsource, has 2 tabs containing around 50 text boxes, check boxes, 3
combo boxes. frmEntry is opened via a button on a "main menu" form, via a
macro that opens frmEntry in Add mode (i.e., it's normal starting state is
filtered for entry of a new record). (The recordsource has nearly 89,000
records.)

Buttons on frmEntry run macros to filter the form a variety of ways, all of
the macros use the OpenForm action with the FilterName argument referring to
a named query; each FilterName query is a parameter query with an expression
such as ...Like [Enter the first few letters of the company] & "*"... . This
has worked great in versions up through 2003: although the form is open, the
macro "reopens" the form, causing the parameter query to prompt for user
entry. Performance is less than 1 second.

When the same .mdb is opened with Access 2007 and the users attempts to use
the buttons, the parameter message box does not display, allowing no user
input, causing all 89,000 records to be selected. Some users get it to work
once, but then not a second time. Apparently, 2007 handles this "reopening"
differentlly. And, in general does not seem to filter as quickly.

I've tried:
tweaking the macro:
...added a requery action (causes the parameter to pop up, but generates run
time error when you cancel the message box, and is really slow);
...added an OnError action to catch the runtime error (probably won't work
in 03, and is still really slow);
...tried the Where argument instead of the FilterName. (doesn't pop the user
input box, ergo no worky)
Tested converting a copy of the .mdb to .accdb (no performance improvement)

I can't seem to find any info that would explain this. Anybody got any
insight?

 

[Frank H]

Yeah I agree it's hokey, but that is what is there.
I tried the core of suggestion, by using the Where parameter in the macro
rather than the Filter parameter, and still got poor performance.
Thing is; I don't want to have to rebuild the thing completely: I first want
to know what changed about Access 2007 to cause the change in performance.
Perhaps that would point towards a mimimal effort approach to fixing the
performance without rebuilding that form...

--
Frank H
Rockford, IL

Rather than the hokey parameter query with a form reopening, build a search
form to capture the input. When the user clicks the go button, open the form
using the criteria. Form would have a textbox and a button at minimum.

docmd.openform "formname", wherecondition = "[Company Name] Like " & [field
from search form] & "*" (The * may need to be a % for SQL Server.)

I'm using a db built "long ago" (probably Access 97), which is currently
being used mainly by Access 03 users. When used in Access 2007, performance
is MUCH slower. None of the posts I've found seem to be addressing the same
issue; I'm hoping someone can provide some insight.
I'm not looking to rebuild the db (multiple users enter "incident"
information). I only want to open this can of worms far enough to make
minimum modifications possible, so that its performance is acceptable when
opened with an '07 machine, without messing up it's excellent performance
when opened on an 03 machine.

The .mdb file is on our server. Tables are linked via ODBC to a SQL server
in the same room.

The form (call it frmEntry) has a query (3 joined tables) as it's
recordsource, has 2 tabs containing around 50 text boxes, check boxes, 3
combo boxes. frmEntry is opened via a button on a "main menu" form, via a
macro that opens frmEntry in Add mode (i.e., it's normal starting state is
filtered for entry of a new record). (The recordsource has nearly 89,000
records.)

Buttons on frmEntry run macros to filter the form a variety of ways, all of
the macros use the OpenForm action with the FilterName argument referring to
a named query; each FilterName query is a parameter query with an expression
such as ...Like [Enter the first few letters of the company] & "*"... . This
has worked great in versions up through 2003: although the form is open, the
macro "reopens" the form, causing the parameter query to prompt for user
entry. Performance is less than 1 second.

When the same .mdb is opened with Access 2007 and the users attempts to use
the buttons, the parameter message box does not display, allowing no user
input, causing all 89,000 records to be selected. Some users get it to work
once, but then not a second time. Apparently, 2007 handles this "reopening"
differentlly. And, in general does not seem to filter as quickly.

I've tried:
tweaking the macro:
...added a requery action (causes the parameter to pop up, but generates run
time error when you cancel the message box, and is really slow);
...added an OnError action to catch the runtime error (probably won't work
in 03, and is still really slow);
...tried the Where argument instead of the FilterName. (doesn't pop the user
input box, ergo no worky)
Tested converting a copy of the .mdb to .accdb (no performance improvement)

I can't seem to find any info that would explain this. Anybody got any
insight?