Prev: How do i convert access 2007 to 2003 files using access 2003?
Next: Warehouse Organization and movement.
From: Frank H on 15 Sep 2009 11:07
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
Rockford, IL
From: S.Clark on 15 Sep 2009 13:44
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.)

"Frank H" wrote:

> 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
> Rockford, IL
 | 
Pages: 1
Prev: How do i convert access 2007 to 2003 files using access 2003?
Next: Warehouse Organization and movement.