Wednesday, January 21, 2009

Log First, Focus and Then Look For Speed



A number of times in 2008 I was asked to speed up an Access database. This article outlines how usage data can make this quest more focused. The article also delves into an Access 2007 ACCDB only feature called TempVars. But firstly let's discuss some users’ scenarios that I've had to deal with.


Situation one: A skilled Excel technician has set up a database with numerous related tables. When he rang me for advice, he was adamant that he needed to upgrade to SQL server to speed up the database. As the database was only 20 MB in size, I doubted this but still we had the meeting. Some of the forms were very slow, had many Tab controls with hundreds of fields scattered across many subforms. In addition, the training users had was to scroll through records one at a time to find the record that they were interested in looking at. Just adding a find record box made finding the data a lot quicker.


Situation two: A complex database that had been in development for six years and now the developer had left the business. The last three years they had done nothing to the database apart from compacting and repairing the database but the performance was woeful. In this database, some forms could take up to two minutes to close when the close button was pressed. Also, some reports took nearly an hour to run.


Situation three: A huge database already converted to use SQL Server as a back-end; there were 250 forms and 80 tables. Performance was sluggish throughout during peak periods.



Tuesday, January 20, 2009

SQL Server Upsizer Tool (Special Deal 334)


In the last quarter of 2008 we started selling the MUST Access to SQL/.NET Upsizing Tool with reasonable success and good feedback. To start the new year off, we have a special offer only available to readers of Access Unlimited where the Standard MUST tool is included free with the other products. To read more about this, head to this page.

Offer Expires: End February 2009
Note: Version 7.2 is now available for a simpler interface for the .Net Web conversion component.

Friday, January 02, 2009

Caching For Access Databases Under Stress


I have revamped an article on using ADO to cache lookup tables into text files to confirm it works with Access 2007. An interesting sideline in this article is the technique of writing a custom function to fill your combo/list box rather than just using tables and queries.

Read more on ADO caching here

Garry Robinson
Editor of vb123.com.au