Profiling the "Calculating ..." message.

[revver]

PRELIM INFO
ACC2003. The setup is typical FrontEnd / BackEnd both in the same folder.

I have a form with 2 subforms, one each on page2 and page3 of a tab control. There isn't much data in the main table, a little in one linked table and almost none in the other linked table. There are two DLookup text boxes on the form to display sensible data rather than a code which is stored in the main table.

This should not cause a slowness problem and doesn't on my development machine. In production, however, it can be frustratingly slow moving from one record to the next (ie populating the form).

There is some minor code in the OnCurrent event. It synchronizes 2 combo boxes with the current record and checks if the item is inactive and, if so, changes some font colours and labels.

THE PROBLEM
When moving to the next (or prev) record OR using one of the find combo boxes to display a different record, there is a delay of many seconds, sometimes close to a minute, before the form is populated. Neither subform is visible at this stage. The message "Calculating ..." appears on the status bar during this delay.

Is there any way I can find out where the problem is?

 

[HiTechCoach]

As far as I can tell, two users have never had the same FE open at the same time. Nevertheless, it is a small matter to give all three their own copy. This will be an interesting experiment because it means that the FE and Access will be on TS and data on the FS rather than Access only on TS and the rest on FS as it is now.
BTW, this FE links to 2 BEs, the main BE and the Customer BE (which is used for the lookup).

It is OK to keep the back end on the TS. If that is where most of the user are logged on that use the back ends then this will help with performance.

Also, it is nearing time to walk away from 2003 and I am considering SQL server or 2010. Any pointers about the conversion process to either of these?

If you upsize your back end to SQL server you still need a front end. Access can still be used for the front end. So you may mean SQL server AND Access 2010 together.

 

[revver]

Yep Norie, a union query. The FamilyNum (prev referred as customer code) is used as a Location code and we also have internal locations where the items can be stored so we invented fake FamilyNums for those and store them in the Location table.

SELECT FamilyNum, Surname & ", " & GivenNames &" @ " & Suburb As Locale, Suburb, RegionID 
FROM Child
UNION SELECT  FamilyNum, Surname & ", " & Suburb  As Locale, Suburb, RegionID
FROM Location
ORDER BY FamilyNum;

Boyd, After I clicked 'submit' I realized I probably meant AND. It's daunting and going to 2010 seems difficult enough. Would you

Stay 2003 and add SQL server, then 2010
Go 2010 only first, then add SQL server last
or really bite that bullet?

Obviously there are more dbs than just this one.

 

[Norie]

How long does that UNION query take when opened on it's own?

 

[HiTechCoach]

At this time I actually go with Access 2007 and SQL server 2005. Access 2010 is still to buggy for me.

I find it best to pair Access and SQL server by using a later version of Access than the SQL server. This helps insure that Access is updated and compatible with the the version of SQL Server.

I jhave sen peope tke Acess 2000 and SQL Server 2008 r2 and have many issues. Access 2000/2002/2003 with SQL Server 2000 works great.

 

[revver]

How long does that UNION query take when opened on it's own?

I just opened the db on the production TS. It took several seconds to populate the main form with the first record. I opened the db window and launched the query. It took < half second. Closed mdb. Repeat. Same result.

Nevertheless, It is worth examining. When I removed the lookup as Boyd suggested (and some combos with code too) response improved with most presses of 'next'.

Boyd,
I am just in the process of figuring 2010.
2007didn't seem too different from 2003 but 2010!!!!

I anticipate that earlier versions of SQL Server would not be available. I expect that the latest would be all I could get.
I agree that a later Access is more likely to understand an earlier SQL but, using that logic, am I not stuck with 2010?ray:

There is no great rush to switch but ...
I guess I should start to learn SQL Server while waiting for SP1+ of 2010.

 

[Norie]

Well if you aren't using the lookups or the UNION anymore I suppose it doesn't matter.

Just don't think I've seen a UNION query used as the domain for a lookup, though can't see why it couldn't be.

Regarding SQL Server, I'm no expert but I think you might be able to get earlier versions if you wanted.

Don't see why you would want to though, you can get various versions of SQL server free from Microsoft.

Not sure what the latest is, I was using 2008 R2 but I think there might be something called Denali.

There might even be 'lite' versions that are targeted for this sort of thing.

Here's one link:

http://www.microsoft.com/sqlserver/en/us/get-sql-server/try-it.aspx

Sure there are others.

 

[HiTechCoach]

I think you are suggesting that, say another page on the tab control with a subform where the customer details are displayed, derived from the customer code on the main form. I don't see how that helps. How does that avoid a lookup? I must be missing something.

Aaah. I think I get you. If I replace the present name address area with a subform ...
The relationship between the main table and customer table is many-many which I think makes it a non-viable choice in this case.
Or am I still missing your point?

If you are able to do the Dlookup on the form then I would think you should have the data required to use a sub form.

 

[HiTechCoach]

Boyd,
I am just in the process of figuring 2010.
2007didn't seem too different from 2003 but 2010!!!!

Um ... 2007 is radically different than 2003. 2007 introduced a totally new UI (the Ribbon), a new database format, and many other things.

Acces 2010 has improvements to the new UI introduced in 2007.

Access 2010 has some additional UI improvements when to comes to crating forms.

I eally do like the improvemnt to teru UI uin Office 2010. All the oterh Office apps work great. Acess 2010 still has some major bugs. Unfortunately it looks like until SP2 is released it probably will not be stable enough for production use. The was also true with Access 2000 when the new JET database engine was introduced. Again with Access 2007. I diod not use Access 2000 or Access 2007 in production until the the SP2 update was released.

I anticipate that earlier versions of SQL Server would not be available. I expect that the latest would be all I could get.

Goo thing they are still available. I still see more install of SQL Server 2005 that any other version. I also see more SQL 2000 that SQL Server 2008/R2.

Starting with Access 2000 Microsoft stated pushing to use Access as a front end to their SQL Server. That is also when they introduced ADP in Access for working directly with an SQL server and bypassing JET.

I believe it is never to soon to start learning to use an SQL server as your back end.