A Nontrivial Test bed for MS Access^©
Why You Might Need This Database

Before deploying your brand-new database into a production environment,  wouldn't you like to know whether it will stand up to a heavy load?

Here is a tool that can help in avoiding situations where you delivered your database and two months later the client is complaining that it takes FOREVER to get anything done. Is Access really that bad, or is it your code?

This is a sample database developed by Patrick Crews and Giuseppe Maxia. It provides a combination of a large base of data (approximately 220 MB) spread over six separate tables and consisting of 4 million records in total adapted into an Access database.

The database can be useful in assessing answers to various questions such as:

• Which general classes of SQL statement (e.g. Joins or subqueries) will perform better?
• How does Access use indexes and how efficient it is under what conditions?
• How well will it perform in a networked environment?

...among other things.

While it may always not be the answer to every question, it can be useful in answering questions that are best answered with a dataset consisting of more than ten rows of data and understanding how Access manage such loads.

Recently, one of my colleagues, who goes by the screen name BananaRepublic at Utter Access, and Banana, at Access World Forums, made available this testing database with several millions rows of data to his fellow Access Developers.

Most of us like to conduct tests prior to releasing our applications for normal use, but it always isn't meaningful to test on only a handful of rows of data. Banana decided to do something about that problem. He took an existing sample database and adapted it for Access. This database contains close to four million rows spread across six tables representing fictional personnel data.

GPC Data agreed to help host the downloads. All three versions (A2000, A2002-2003 and A2007) are free to anyone who want it. The data are licensed under a Creative Commons license. The downloads are now, or will be, available at  two or three other mirror sites as well.

Each of the three zipped files is apprximately 69MB.

This data can be used in comparing the general SQL statements in your database (e.g. will you get better performance from an outer join or from a subquery?) You can try out functions or a vanilla Select statement, test network performance, and other design considerations that could affect performance that is evident only with a large dataset.

The schema for the test bed database is shown at right. Banana didn't alter the design in the import in order to make it similar as possible as the original database. We understand it was the author's intentions to leave it up to users to modify the schema to suit their design preferences. It shouldn't be too hard to do so.

Access 2007 Version

Access 2002-2003 Version

Access 2000 Version

While you're here, check out some of GPC Data's sample mdbs.