Should you wish you discuss
these options further,
please contact us at

PASR Technologies Pte Ltd

Tel: +65 6238-3416
Email: sales@pasr.com.sg

Upsizing Access

After you have been using Microsoft’s desktop database application - Access – for some time you will have spent significant effort in creating complex tables and relationships, plus a user interface that enables your average users to interact with the data without having to know the nitty-gritty of database and application development. (If you’re unfamiliar with just how powerful Access is, and what can be achieved, you may care to visit http://office.microsoft.com/en-au/assistance for a detailed introduction to the product).

By this stage, you may have adopted a front-end/back-end approach, and provided all your users with the front-end module containing the forms, reports and logic – and are not sure how to go further. You’re probably starting to get concerned that the hard work put into designing the forms and the logic code – supposedly to protect the data and users – can easily be modified by any of the users. You’re probably also starting to amass quite a significant quantity of data in the back-end, and probably also seeing performance slowly deteriorate as you get more. Read on, as we explore the options and steps to address all these issues and more.

After you first develop your application, you will have a mdb file that contains all the tables (where the data is stored) plus all your application logic, including forms, reports, queries and general utility code. To make this into a multi-user application, you need to separate the data (the tables) from the application code. There are wizards within Access to do this automatically (the Database Splitter Wizard) and will create two separate mdb files:

• The Back-end database – containing the tables of data. This should be placed in a central location so that all users can connect to it

• The Front-end database – containing the application logic and presentation components. A copy of this should be placed on each user’s workstation.

A single keystroke at the wrong time in the wrong screen can take a user where you never intended. Now that each of your users has their own copy, any one of them could modify the code and possibly cause problems with the integrity of the data that you have tried to protect. The first step in moving on is to compile your front-end mdb into a mde file. Again, a wizard within Access can take care of this process almost automatically.

There are three benefits to this approach:

• As the code is compiled, it can no longer be modified by users. Of course, you must keep the original mdb file, as you will not be able to modify it either. If you need to change, change the original, and then compile and redistribute as necessary.

• Compiled code runs faster, as it strips out the comments etc in the source. Therefore, you also get a small performance boost.

• You save costs. Did you know that user’s do not need to purchase a copy of Access to run the compiled version? All they require is a copy of the Access RunTime to be installed. This is normally included on your Office CD, but may also be downloaded from Microsoft at no charge and distributed royalty-free.

As an Access application begins to grow in size and number of users, performance begins to suffer. This is because all processing of the database is done by the client workstation, and not on the server where the back-end database resides. The database engine used by Access is called JET. To retrieve data from the shared file, JET must copy the whole file to the workstation, perform it’s operations, and then put the file back again on the server. Naturally, as the file gets larger, this process takes longer and longer.

Let’s take an example:
Our contacts table has now grown to about 2,000 entries, with each one using 1KB of space. In total, we thus now have to transfer 2MB of data back and forwards every time we access the contacts table. Even if I only want one contact record, I have to retrieve the whole table, and use JET locally get the contact I want.

At this point, familiarity with relational database design practices will assist with performance, but the limitation of the JET desktop database engine will at some point result in unacceptable performance, and more so as the size increases.

Fortunately, there is a further upsizing step that can be taken to overcome this limitation. Microsoft provides yet another free tool to move to a real database engine technology. This has had various names – the Microsoft Data Engine (MSDE), or the SQL Desktop Data Engine. This is also available at no cost from Microsoft, and is included on the CD with some editions of the Office suite, and is available for royalty-free distribution in certain circumstances.
MSDE is a real database engine, and operates as a server process.

Within Access, there are tools that provide an automated facility to upsize an Access database (mdb/mde files) into an Access project (adp/ade files). The major difference between the two is that the JET database engine is no longer part of the application, and the shared data tables now reside within the SQL server environment. As with the database files, Access adp project files can be compiled into ade files for security, and will also run without the full Access product by installing the Access RunTime. So, let’s take our earlier example and compare the difference:

I now want to retrieve a single contact record – perhaps to update a field such as the telephone number. Instead of retrieving the whole 2MB, I send a request to the database server to return me the specific contact record. This request is likely to be only a few bytes in length, and returns to me the single 1KB contact record. All processing is thus done on the database server, with only very small amounts of information being transmitted between me and the server. Naturally, this results in a huge performance increase.

There are some limitations in using MSDE – that’s why it’s free, and if there weren’t nobody would ever purchase the full version of SQL server from Microsoft. However, upsizing from Access JET to MSDE will normally allow your user count to multiply by ten before you even need to contemplate such a step.

If after reading about Access, MSDE and SQL Server and you need further discussion, please call
PASR Technologies Pte Ltd

Tel: +65 6238-3416
Email: sales@pasr.com.sg