Prev: Access 2007 - Run Time error '2114':
Next: oude metalen
From: Frank H on 18 Sep 2009 17:52
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


"S.Clark" wrote:

> 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: Access 2007 - Run Time error '2114':
Next: oude metalen