Example Databases

Last Updated 24/03/11



There are various little databases on, or linked to, this page for students to access and look at.


The HCI is pretty basic, as the databases are only 'quick and dirty' ones created to illustrate a way of solving various problems.



Some other links with example databases are:








Creating custom primary keys


Points demonstrated:


The methods that you have probably used so far in labwork and assignments is to use either an AutoNumber key or to enter your own key values. Some of you may also use the field format to 'add' a prefix letter to an AutoNumber key, but this method can cause problems if you do not realise that the prefix only appears on the format, not on the actual data!



Sometimes you would like an AutoNumber key with a prefix (e.g. Member No = M123456 or Student No = 03123456 [where 03 = the year of registration] ), or to have your own control over a 'last used key' value.


Allowing the user to allocate their own keys can also be problematic as Access does not check to see if the key already exists until after the user has completed the form and then tries to save it - most annoying! So you will need to do the check yourself.




All this really quite easy. Click here for example Access 2000 database which demonstrates  methods of doing different types of keys. Look at the code for each form module to see comments and code.



If you wanted to show AutoNumber keys with leading zeros, then simply change the field format property in table design view. For example, set the format property to  000000  if you wanted the number to show always as 6 digits.




Car Booking


Points demonstrated:

The example here can also be adapted for booking, hiring, renting... items that are booked for whole days between two dates, such as hotel room bookings.


See the example database and explanation document on the FAQ page.

This example is the second of three examples showing how to use NOT IN sub queries.



Mailing Letters    

Points demonstrated:

This example arose out of a query from an HNC Project student who was designing a system for an Optician's Practice and was then extended following a request from an MSc student. Examples of using mail merge with Word were also added.

You can find an Access 2000 example database here and an explanatory document here.

You might also like to look at How do I reconnect a mail merge Word document when I have moved the Access database?


Multi-purpose queries


Points demonstrated:

(The method shown here may not work if you are using a calculated value or a value from a function. You may have to use the IIF function in the query criteria. See the Birthdays example database for an example of how to do this).


This example arose out of a query from an HND project student who was doing a project for a local hospital. The hospital wanted to analyse data by a number of criteria such as date of birth, postcode, nature of illness. They wanted to vary the criteria each time the query was run.


See example databases, discussion and explanation document on the FAQ page for VBA FAQ #4.


Note: There is a procedure in the Getting Started VBA Trainer that shows how you can calculate a person's age from their date of birth. If you put this procedure in an Access module in your database you can then reference it in a query to provide a column that will show the age. You could have a calculated column something like:   Age: myCalculateAge([the date])



Staff Holiday Booking


Points demonstrated:


Click here for a copy of the database.


This example arose from an enquiry from a student on placement. The organisation wanted a database to record and calculate staff holidays. It works only for one year; for a ‘real’ system it needs to be extended to have a Holiday and BankHolDate table (and associated forms) for each holiday year, possibly with associated command buttons on a main menu. This database probably needs yearly maintenance as well, to remove old year forms and add new year forms. The Holiday table has field validation conditions set for the dates to be in 2003 and a record validation condition set to check that the end date is not before the start date.


The example database has three tables...


...and two forms:


There is VBA code behind both forms, much of which you should be able to follow. But there are two things that may need further explanation:



Cinema Seat Booking


Points demonstrated:

This database arose from a query by a Final Year Project student. He wanted to know how to work out availability of seats when a booking was placed. The cinema seats were not numbered; customers just turned up and sat where they liked.


Click here for the example database.


The database here has four tables:


There is one form, called Booking.


School Tests


Points demonstrated:

This database arose from a query by an HND Project student who was writing a database for a school to record SAT results.


Click here for the example database.


The database shows a possible method of selecting the required test, then entering the results against each student name. An Outer Join query is used for the results form, to show existing marks and also where marks have not yet been entered.


The database uses the same class list for each test. The scenario probably needs to be extended to allow the School to choose both the class and the test for the results form.


There is some VBA code behind each form.




CD Collection - example databases


Points demonstrated (in CD Collection version 2):



Explanation document (Word 2000)


Click here for a copy of CD Collection - version 1 Access 2000


Click here for a copy of CD Collection - version 2 Access 2000





This page contains an explanation of, and links to, two example databases for a CD collection.


The databases on this page were originally developed by Mary Spence following an enquiry from a student who had just started the module COMP1003 (Systems and Data Analysis for Database Design), and who was trying to apply his knowledge to a database to record details of his CD collection.


They are linked here for the information and (possibly) interest of other students, to provide further examples of the use of database design, implementation and code using MS Access.


They are just 'quick-and-dirty' implementations, thus are not perfect (far from it). The forms need to be developed further to provide a better, more error-proof, and more attractive interface for the user. The attributes may need to be refined to fit the particular requirements of a specific collection. You may like to consider making Title a separate table.


Feel free to use these example databases for your own CD Collection. Possibly use your skills to extend them to cover records, cassettes, videos, DVDs, etc. If you think your extended version could be of use to others, and wish to make it freely available, please send me a copy to post here.





Points demonstrated:

This database extends an example used in a CSCI1403 (Introduction to Database Implementation) lecture. There is a Read Me table (and a Read Me report, to make reading the table easier) explaining the database and the features.


Click here for the example database.



Lucky Numbers


These databases each generate 6 random numbers in the range 1-49, and present the results in a list box on a form. They show alternative ways of achieving the same result.



Version 1 - No tables are used; there is just one form and the code behind the Form_Open event.

Click here for the example database.

Points demonstrated:


Version 2 - Creates and drops a temporary table. This version is discussed in version 5 of the Getting Started VBA Trainer.

Click here for the example database.

Points demonstrated:





Points demonstrated:


This database has two tables and two forms.




Use Tools-->Options, Edit/Find tab, and then clear the Action Queries check box, to suppress the various Access information messages when the make-table query is run.

Added later - you could also alter the code to use DoCmd.SetWarnings to suppress these messages; see the end of section 6.2 in the Getting Started VBA Trainer. This is probably the better method as it applies to the application rather than just the machine on which the application is run.


Click here for a copy of the database.




Access 2002 has some new features for list boxes where the RowSourceType property is Value List. These include the AddItem and RemoveItem methods.

The Listbox97 database linked here is an Access 97 database, demonstrating how to add and remove rows using just the facilities within Access 97 (and Access 2000). It uses the Len and Mid functions to count through the Rowsource property looking for the semi colon (;) data separator.

The Listbox97-2000 database is as above, but converted to Access 2000 file format.


Attendance Registers


There are two example databases here, showing two different possible methods of approaching the problem of recording and analysing attendances.


Common points demonstrated



Attendance Register 1


Other points demonstrated:


This uses a very simple method, and assumes each class occurs a fixed number of times.


Click here for example database and explanatory document.




Attendance Register 2


Other points demonstrated:


This is a more complex but more flexible method, allowing for varying occurrences of classes.


Click here for example database and explanatory document.


Orders database


This database was originally developed to assist a FY Project student doing a customer purchasing database.

I have not had time to develop this into anything more useful or write an explanatory document, but there are extensive comments in the VBA code so I hope you can work out what's going on. Note that the HCI is very basic and needs a lot of improvement.

Click here for the example database


Event History demonstration


The database attached here has been written by my colleague Dr. Peter Bounds to show the events generated on a data entry form for recording membership of a leisure centre. It shows how form and control (text box, button, etc) events are triggered and the order in which they occur, and will help you understand how events happen and how they can be used.


The database contains two forms:

  1. EventHistory form. This form opens first and has a text box on it into which is recorded details of each event that takes place on the second form. The second form is opened in the Form_Load event of this EventHistory form.

  2. Membership_Events form. This form is based on the Membership table of the Chelmer Leisure scenario used in McBride. There is code behind most of the form and control events which simply writes a line in the EventHistory form text box to record which event has been triggered. Thus, when the database is first opened, the textbox shows the events that occurred when the Membership_Events form was opened. When you move between fields, press buttons, close the Membership_Events form, for example, you will see the list of new events being triggered. The Clear button on the EventHistory form can be pressed to restart the list of events if it gets too cluttered with old events.

List Box Filter


The database attached here shows how to filter the contents of a list box with each character entered into a text box.


Points demonstrated

Sending Emails


The database attached here is an extract from the CSE Student Monitoring database, and shows the form used to show students on a selected Course and Year with their total attendance for a specified time-period in the current academic year. This form is used by year tutors to email students who have poor attendance. It can also be used to email all the students. Students are selected for emailing by clicking on a tick box. The email subject line is set to contain the student name(s), except when more than one student is selected and BCC (blind carbon copy) is used.


Points demonstrated


Linking Images


This database implements the linking in of images to an Access form. The images are in a separate folder from the database, as discussed in Access Q&A #34 "Should I embed or link images into my database?"


Click here for a zipped folder which contains the images folder and the example database.


Points demonstrated


Opening Word (and other) Documents


The database attached here was created for a Final Year Project student who wanted the user of her system to record certain details on a Word document rather than in a 'Comment' field in a table. This facility would enable the document to be formatted and printed out, and could thus prove more flexible than recording these details in a table.


This facility could also be useful for reading files of other types, for example: PDF files (scanned documents such as application forms, correspondence, plans  - the documents could be set to 'read only' if necessary); images (useful perhaps for estate agent systems). Simply change/remove the coding in the attached database that checks for documents with Word file extensions.


The method used here is similar to that for the Linking Images example, in that it is assumed that all documents for a table are put into the same folder, however it is simple to adapt it to allow documents from any location accessible by the database.


Click here for a zipped folder which contains the documents folder and the example database.


Points demonstrated


Importing/Exporting Spreadsheet Data


The example database attached here follows the example in section 7.3 of the Getting Started VBA Trainer.


Click here for a zipped folder containing the database, an Excel spreadsheet to import and a sub-folder into which the database will export.


Points demonstrated


Subform Referencing


The example database attached here shows how to use the Forms Collection to reference controls, properties and methods in a subform from the main form.


The database is based on a 'real-life' database where the client was creating a multi-purpose query to allow the user to query data by different combinations of criteria (similar to that demonstrated in the Multi-Purpose Queries database example).


The database has two forms: a main form with query criteria from which the user chooses, and Search and Reset buttons; a tabular format subform which displays the selected data.


There are just two query criteria in the example:

  1. A simple criterion selecting member records by gender (Male/Female/All). This criterion is on the query for the subform.

  2. A more complex criterion to allow the user to select ages <, =, or > a selected age. I don't think it's possible to do this with a query criterion (at any rate, I couldn't work out how) so I added it to the query SQL in the subform RecordSource property at run-time.


Points demonstrated


VBA Wizard Code


Microsoft used to discourage Macro use in favour of VBA code, but from Access 2007 this policy appears to have been reversed. The various form wizards now generate macros rather than code. In Access 2007 it is possible to convert these wizards to code, but there is currently a bug in Access 2010 that prevents this conversion.


I find this bug (and, indeed, the reversal of policy) highly annoying as I much prefer to see, and edit if required, the VBA code. I find macros to be rather inflexible and non-transparent.


On my Access 2010 page I suggested that if you want to use code for buttons you will have to code it yourself from scratch. To help you do this I have created a simple .mdb database in Access 2002 (using an old home PC with Office 2002) with loads of wizard buttons and code. The 2002 code can be copied/pasted from here to your own database, and adapted as appropriate. It should still work in Access 2010.