Access: A resource hog?

[jodjim]

We are using Microsoft Access (97 and 2003) to retrieve data from an SQL database and generating reports. An ODBC connection was created for the link and a pass-through query is use to select a particular table from the database or a set of tables during consolidation. When running reports we noticed the program, the CPU performance in Task Manager hovers around 100% and it's a bit slow especially when there's more than one user is logged-in.

Is Access really a resource hog? That's the impression we have and the information we got from someone. I'd like to know the opinion of the experts.

Thanks a lot.

 

[JeremyNYC]

Yes.

---
Jeremy Wallace
METRIX Lead Developer
Fund for the City of New York
http:// metrix . fcny . org

 

[alvechurchdata]

YOu should be grateful that it hogs all the CPU going. If it only used 50% then the query would take twice as long. Access was (I think) designed on the premise that there was a user sat there waiting for the results and so it takes what it needs to get the job done.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[KAT001]

Hi jodjim,

To confirm what has already been said - All versions of Access are resourse intensive. They will use what ever is available to the job.

Good Luck.

 

[jsteph]

JodJim:
Check out these threads that discuss this--specifically the 100% cpu when access is apparently idle:
thread181-989907
Thread181-814758

It's my guess that Access is constantly polling/refreshing the odbc connections, even when access is idle.

If Access is really idle, then as soon as another app wants the cpu time, it will give it up.

However, if you're running a query or something, Access, being inside the loop with Microsoft (both literally and figuratively) Access does not share and takes the inner-ring priority of the cpu slices.
--Jim

 

[jodjim]

Thank you all for your answers. But a quick question, if I may, what is the best option then to MS Access when it comes to data retrieval, manipulation and reporting? VB6, VB.Net? Are these less resource intensive?

Thanks again.

 

[alvechurchdata]

what is the best option then to MS Access when it comes to data retrieval, manipulation and reporting?

Depends how much data you want to manipulate and what you want to do with it. The next step up in the Microsoft database world is Visual FoxPro but if all you want to do is retrieve data then Crystal Reports might be the best bet.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[jsteph]

jodjim
If you have rights to the sql database, and it supports a full stored procedure language, then the best bet is to still use Access, but write stored procedures that do the query and/or logic needed for the reports.

A good general solution is to start backwards from the report. Build a temp table that fits the fields shown on the report. Then build the sql and logic to gather the data to fill that table, then when you open the report based on that table, it's very quick.

Even using Access pass-thru queries as a recordsource for reports can show a huge increase in response. Further, if you aren't well trained in the procedural language of the sql database (Psql for Oracle, Tsql for ms sql-server, etc) you can still build a permanent 'temporary' table, run pass-thru queries to load that table, then open the report based on that table. This can all be done in an Access module, the pseudocode being:
1. Clear 'temp' table/tables
2. Run pass-thru queries to load temp table/tables
3. docmd.openreport (based on the final 'temp' table)
--Jim

 

[jodjim]

Whooa! The suggestions are quite interesting. I've done some programming in dBase and Foxbase years & years ago but it would be nice to revisit Visual FoxPro. Also, one of my Access projects is a reporting tool that uses 5 tables link to an SQL database two of which I'm using pass-thru queries. This could be a good candidate for a creating a temp table.

Thanks a lot. I really appreciate your responses.

jodjim

 

[alvechurchdata]

dBase and Foxbase years & years ago

That's like comparing GWBASIC with dotNet<g>. Fox has changed a lot since those days.

Latest version has:
Native SQL but still supports xBase
Object oriented with proper inheritance
Remote views or PassThru into SQL Server
Built-in Report Writer or you can link into Crystal
ADO or DAO available if you want to use familiar techniques

Try Forum 1252 or go to the Fox Wiki

http://fox.wikis.com/wc.dll?Wiki~VisualFoxProBulletPoints

Geoff Franklin

http://www.alvechurchdata.co.uk