Visualize - ASP Tips (Active Server Pages tips and hints on ASP and IIS

home

resources

asp tips

access 97 memory

tips menu | printable single page version

Note: this is my old ASP tips page and is no longer supported. See the new Visualize web site..

IIS/NT Tips - MS Access 97 Memory/Performance

MS Access is not the ideal database system on which to build scaleable web based applications. 'True' multi-threaded DBMSs such as SQL Server, Oracle or DB2 are much better suited to medium to large scale development. However, the convenience of MS Access and also the likelyhood of having to 'tap into' existing application data can not be overlooked, hence this makes it highly probable that as some time we will want to connect to this (what is essentially a desktop) DBMS. To be fair, MS Access often holds up very well (on small intranets for example), considering some of the conditions it is often put under.

Tips? Make sure you have up to date drivers (see previous point) which may offer better performing and more robust connection mechanisms. Also, if you are experiencing memory/performance problems through ODBC, try tweaking the following settings in the ODBC data source administrator:

Select relevant Access 97 DSN on the System DSN tab
Select Configure, then Advanced.
Select MaxBufferSize and change the value to 8192 (512 is the default)
Select Threads, and change the value to 20 (3 is the default)
Confirm all the changes and exit the administrator

These new settings should provide you with better performance in most cases. Try increasing them slightly more if necessary, depending upon the resources you have available.

Finally, if you are still using ODBC you should really consider connecting directly using OLEDB. An example connection string could be:

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\websites\yourdir\yourdb.mdb;"

This is more efficient and reliable than ODBC.

DISCLAIMER: Note these pages are a free resource for anyone wishing to reference them. Although every care is taken to ensure their correctness, the author takes no responsibility for any errors or problems that may occur through their use, or indeed misuse. These pages are copyight of Dave Clarke, Visualize Software Ltd 1997-2000 (all rights reserved).