Access 2003 issue

Hi Experts,

I have a datasheet form that contains several controls with control source set as "=dlookup(..,..,..)"
Now they all work fine, however when I just added one control containing the following.
=DMax("ID","OrientationNotes","OrientationID = " & nz([TxtOrientationID],0))

Open in new window

That causes all my other dlookup text boxes to delay their display with a few seconds, what it actually does is it displays a few records, then pauses for a few seconds and then displays the rest, unless if I click on them that triggers a quicker response.

One interesting point, I realized that the records that get immediately displayed are always the same, for example record #1 5 and 6, and there is no obvious pattern.

FYI, this issue is only happening in 2003 version, while with the older version of 2000 it does immediately display all records.

The reason I have them in dlookup boxes instead of creating a view that contains all these as part of the record source, is that I need this form to be fully updetable, so users can add/edit and delete directly from the form.
LVL 5
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
Is there an index on OrientationID column in the table?
0
bfuchsAuthor Commented:
Yes Sir, clustered index.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
clustered ?
Is this SQL Server ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How many records are in the OrientationNotes table?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You need to use a different approach, such as building a temp table with all the data, and basing the sheet on that.

While the domain functions are handy, what you need to keep in mind is that   for every row, you are in effect running multiple queries for each.

Indexing will help to some degree, but there's no real "fix", for what your seeing.

Jim.
0
bfuchsAuthor Commented:
Hi Experts,

@DatabaseMX,
Yes, its an ADP project linked to SQL Server express 2005.

@Scott McDaniel,
close to 700K

@Jim,
Since this is not happening in Access 2000, I am wondering if MSFT released some patch to fix it, or perhaps someone came up with a workaround for this, as this looks more like a bug than an expected behavior.
This approach of maintaining data in temp tables gets complicated when users constantly need to see up to date info.

Thanks,
Ben
0
bfuchsAuthor Commented:
I tried having a view grouping it by OrientationID and returning Max(ID) of OrientationNote, then using dlookup for that view instead of dmax, thought that will have the server do the calculation, however it only got worse..
0
bfuchsAuthor Commented:
Just thinking of creating a SQL function that accepts OrientationID as a parameter and returns the Max(ID), then assign the text box control source = MySQLFunction(OrientationID), would that work?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Ben,

<<Since this is not happening in Access 2000, I am wondering if MSFT released some patch to fix it, or perhaps someone came up with a workaround for this, as this looks more like a bug than an expected behavior.>>

 That's probably wishful thinking unfortunately.

 You can certainly apply all the service packs and see if the problem goes away, but one of the things with situations like this that everyone forgets is that later versions typically have more features, and those features come at a cost.  

 Access 2003 does more behind the scenes and as a result, it just may be just enough to push it over the edge of what you call acceptable vs not.   From your question, it does complete the task and nothing is really wrong except it pauses.  That to me sounds a lot like normal background processing.

 Perfect example; you know how you get the record count in a form?   Often we see questions where someone says "I no longer see the record count right away when opening a form.  It must be a bug or something is broken".    It's not.  That's normal JET behavior.   What's happened is that the result set has grown to the point where Access feels it needs to display the form even though it's still populating the result set in the background, so it doesn't display the count right off.  

That's by design and background processing sounds exactly like what you have going on.  It might even be a result of something else you have running on your station, something on the network, or any number of reasons (including just being A2003) that cause it not to work as it did.

Jim.
0
bfuchsAuthor Commented:
Hi Jim,

The problem is that we use this approach extensively in our App, as many of our screens are dual purposes, they server as reports and in the same time as data entry or editing forms, and as mentioned above the easiest way to accomplish that is by making use of the domain functions, therefore its really frustrating that with the newer version performance degraded, (as a matter of fact, this is the main reason we are still in 2000/3 era, and not considering upgrading our Office version, as they usually come with a hidden price...and very likely higher than the known price of upgrading..).

Anyways, back to the original issue, what can I do in order to improve it at least to some degree?

Do you think its worth to try what I posted above (ID: 40724029)?

P.S. We have SP3 installed in all of our 2003 users, and the reason its solely A2003, as we still have some users with 2000 and they are not experiencing this issue.

Thanks,
Ben
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<
The reason I have them in dlookup boxes instead of creating a view that contains all these as part of the record source, is that I need this form to be fully updetable, so users can add/edit and delete directly from the form.
>>

Your only choice really is to build a temp table with the data or change the interface in terms of what is displayed/updated (i.e. maybe a pop-up to edit).

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
OK Jim,
Since no one is coming up with something better, I guess have no choice just accept the fact (although this was really my last resort..)
Thank You!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.