Which is the best way???

[Anakardian]

After getting everything to work in the first place I am now looking at improving the speed of my queries since it takes 5 to 10 seconds to make the final result.

As it is today, I have 16 queries that collect and make calculations from a variety of tables for different subjects such as budget and inspections.
Most of the queries are summed up by a single query before it is put into a temporary table.

I am wondering if it would improve the speed if I put the result of each query directly into the temporary table instead of having a query collect it all and then placing it into the temporary table?

 

[spikepl]

I doubt that very much, because that is not likely that that is where the heavy lifting is. Here is some reading for you:

http://msdn.microsoft.com/en-us/library/dd942824%28v=office.12%29.aspx#odc_ac2007_ta_PerformanceTipsToSpeedUpYourAccessDB_QueryPerformanceTips
http://office.microsoft.com/en-us/access-help/help-access-run-faster-HA010235589.aspx
http://office.microsoft.com/en-us/a...rmance-of-an-access-database-HP005187453.aspx
http://www.fmsinc.com/tpapers/faster/
http://www.blueclaw-db.com/access_consultant_rapid.htm
http://www.granite.ab.ca/access/performancefaq.htm

 

[mdlueck]

For Access, better speed resulting from the least shifting of records possible. That is, most restrictive query first, then subsequent queries with the small result set from the first query. Access DB's grow with use, so performing the most restrictive query first results in less DB bloat.

I suggest this method to prepare the DB file:
NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk/forums/showthread.php?t=219948

If you have possibility to upscale to a SQL back-end, DO SO! I have had excellent success with Access being the FE to a SQL BE and all access to the BE DB is via Stored Procedures. The least data possible is actually downloaded to the FE DB which reduces FE DB bloat.

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

Example Stored Procedure / Naming Standard
http://www.access-programmers.co.uk/forums/showthread.php?t=237387

 

[Anakardian]

Thank you for your replies.
It will take some time to get through everything but by first glance I think I have much of it in already.

Moving it to a SQL server is currently not an option but maybe sometime in the future if the evolution of the database becomes more important.

 

[mdlueck]

Just to clarify...

Moving it to a SQL server is currently not an option

I know of at least one person on this forum making use of Access FE / MySQL BE. You may host MySQL on a free Ubuntu Linux box.

In case $$$ was an obstacle.

 

[Anakardian]

It mostly has to do with the limits put in by the IT guys.
While I can make this in access, the rest is off-limits unless they have the full package including manufacturer etc. with hotline and all the bells and whistles.

 

[Lightwave]

I had similar restrictions managed to talk them round to a database in the test instance...............

It's a good long term plan because if like me you are given access rights to that environment you can work out all the problems and eventually they gave gave me a database in the production instance albeit I ask them to do the administration.