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.
Car Booking (method will also work for other situations where an item is booked rented, hired... for a period of whole days)
Multi-purpose queries amended 30/3/2010
VBA Wizard Code New March 2011
Some other links with example databases are:
http://www.databasedev.co.uk/downloads.html
http://office.microsoft.com/en-us/help/HA011201381033.aspx
http://www.access-programmers.co.uk/forums/forumdisplay.php?f=64
http://www.rogersaccesslibrary.com/OtherLibraries.asp
Points demonstrated:
Using a Recordset to control a 'last used key' table (see code for Student and Class forms).
Using a Recordset and the Seek command to check if a user key already exists (see code for Stock form).
Displaying a message on three lines (see code for Stock form). 5/12/2003 - this works in Access 97 but not in Access 2000/02. Use VB Constant vbCrLf to start a new line in a message.
Combining an AutoNumber key with a prefix (see code for Member form).
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.
Class form: where "C" = 1st char of key and "123456" = value from a table which keeps track of last key used
Member form: where "M" = 1st char of key and "123456" = value from AutoNumber field
Stock form: User key - coding to here to show how to check if key is already in use. Key construction and allocation here will be under the control of the user. (for stock, key could be made up of several factors - item, size, colour, etc).
Student form: where "yy" = year digits as 1st 2 chars of key and "12345" = value from a table which keeps track of last key used. This table would normally be reset to start again from 1 for each academic year, so AutoNumber is not appropriate here.
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.
Points demonstrated:
A query with several lines for the criteria
Using a NOT IN subquery.
Setting a listbox to Invisible initially, and changing it to Visible and requerying it (to show the latest results) when a command button is pressed.
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.
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?
Points demonstrated:
Using the wildcard character ( * ) to create variable parameter queries.
The wildcard cannot be used with date ranges, so an alternative method is demonstrated.
Use of the Forms Collection to reference fields on another form.
Setting the sort order for a report at run time.
Using colour on a report for fields determined at run-time.
Using a 'Field List' combo box so that the user can select column names for sorting the report version 2 of database 30/3/2010
Searching through controls to find a specific text box (rather than hard-coding the name) version 2 of database 30/3/2010
(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])
Points demonstrated:
Form and subform.
Use of the forms collection to reference fields on another form.
Use of a procedure in an access module (to calculate the total holiday days between any two given dates). This procedure uses a While loop.
Using embedded SQL and a Recordset to check for the dates on the BankHolDate table.
Combo box to find a record on a form (wizard).
DSUM VBA domain aggregate function, used to show the overall total days holiday so far for each staff member.
Hidden textbox on a form used as a flag; set when a command button is clicked and used by code elsewhere.
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...
Staff – contains very brief details for this example (could be extended as required, for email address, room no, tel. no, job title, date joined, splitting the name into two fields, etc)
Holiday – records each period of holiday (start and end dates) booked for a staff member
BankHolDate – records each bank holiday etc date for the year.
...and two forms:
Staff – based on the staff table, with the Holiday form as a subform. New staff and holiday details can be entered via the form, and details can also be amended.
Holiday – based on a query (qryHoliday), which contains the details from the Holiday table plus the number of days holiday booked. The number of days holiday is thus calculated each time the form is displayed. Note that this total is not stored in the holiday table, as it can be derived from the holiday dates, so would constitute redundancy if it were stored. If the BankHolDate table has changes made to it then these changes will be reflected automatically in all calculations. The calculation is done by the procedure myCalcHolDays in a separate Access module.
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:
The txtSumOfDays textbox on the Staff form. This shows the total number of days taken and is updated in the Holiday Form_Current event using the DSUM function, so that it always shows the latest total of days taken.
The purpose of the hidden textbox txtAddRec on the Staff form. If the user chooses to add a new record the DSUM function fails as there is no StaffID yet available for the new member of staff. So the record navigators at the bottom of the form are switched off and an Add Member command button is used instead. This button sets txtAddRec to True when clicked, so that the Holiday Form_Current event can know to avoid running the DSUM function. (Note – DSUM is one of several Domain Aggregate Functions available within VBA. These are very useful.)
Points demonstrated:
Disabling and Enabling a command button.
Use of the forms collection to reference fields on another form.
Use of the DSUM and DCOUNT VBA domain aggregate functions to work out booking and seat counts.
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:
Customer - may not be relevant to all applications, though some details would be useful if payment is made in advance, or if the cinema wants to contact customers in the event of a cancellation or if the cinema is only a club for members.
Film - the title (and possibly other details) of the film. A film could be shown more than once.
Showing - for the dates and times of each showing of a specific film.
Booking - to record the customer details and the number of seats booked for the showing.
There is one form, called Booking.
This has a list box showing only those film showings with a date after today.
There is a (greyed-out initially) command button to make a booking.
When a list box entry is clicked, the Make Booking button becomes active and the form shows the availability of seats for that booking.
There is a procedure called CalculateSeats which uses the domain aggregate functions DSum and DCount to work out the availability. This is called when a list box entry is clicked (to show the current availability) and again when the booking is made (to refresh the availability information on the form).
Enter a Customer Id, and click on the Make Booking button to see what happens.
Look at the VBA code behind the form.
Points demonstrated:
Use of the forms collection to reference fields on another form.
Disabling and Enabling a command button when an entry in a list box is clicked.
Outer Join query upon which the Results form is based.
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):
Form, subform and 'subsubform'.
Use of the forms collection to reference fields on another form, and on a subform.
Fields set to Invisible initially and then changed to Visible by code. See Artist form - code for Add Artist command button.
Using the AllowAdditions property on the Track and Artist forms to remove the 'new record' row at the end initially, but put it back if the user wants to add a new track or a new artist.
Creating a command button, deleting it, but using the generated code for a procedure. See code for Track form.
Using outer joins for queries by artist/title.
Suppression (i.e. not printing) of selected lines in a report.
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:
Starts with the Birthdays form opened and ready. This requirement is set in Tools--> StartUp.
Use of the Forms Collection to reference fields on another form - VBA is used to set up appropriate values on a form called from the Birthdays form. This code also uses the MonthName function to convert from a month number to the month in words. (Access 2000+ now has a WeekdayName function, but be careful, you need to specify vbSunday as the first day in the week, or else the result is incorrect).
Enabling a command button via VBA code when data is entered in a field.
Use of IIF function for a criterion in a query, to cater for optional parameter entered (or not) in a field on a form.
Use of Month and Day functions for query criteria.
Setting validation conditions in unbound controls.
Setting a postcode to upper case using the UCase function on a labels report.
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.
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:
One-dimensional array - elements initialised to 0 and set to 1 when the corresponding number has been chosen.
Rnd function (and the Randomise statement) to generate random numbers.
For...Next loop to initialise an array.
Do...Loop to get each number.
Dynamic additions to the Row Source property of a form list box at run time.
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:
Using embedded SQL to CREATE and DROP a temporary table.
Using the DCount function to check for duplicate numbers.
Using embedded SQL to INSERT new numbers into the temporary table.
Commented-out code also shows how to CREATE and INSERT for a more complex table.
Rnd function (and the Randomise statement) to generate random numbers.
Do...Loop to get each number.
Form has unbound list box which is bound at run-time by code to the Row Source property to display the numbers in the temporary table.
Points demonstrated:
Conditional formatting in frmBingo to 'remove' the numbers when they have been chosen; font and background are both set to grey when the cell content = 0.
One-dimensional array to ensure that numbers are not chosen more than once - elements initialised to 0 and set to 1 when the corresponding number has been chosen.
One-dimensional array used for the field names of the columns on frmBingo. The corresponding entry in here is then used for the embedded SQL to update the chosen number in tblBingo to 0.
For...Next loop used to initialise the column name array (see frmBingo Form_Load event).
While...Wend loop used while checking for next unique number. This uses a Boolean variable for the loop control.
Rnd function (and the Randomise statement) to generate random numbers.
Mod operator used to work out the column from the number.
Embedded SQL used to update tblBingo, to set the entry for the chosen number to zero. This SQL demonstrates how to use values from variables and how to split an SQL statement over several lines of code. See frmBingo.
Redrawing (using Repaint) frmBingo after tblBingo has been updated, to show the latest number 'removed' from the grid.
Deleting a table using embedded SQL. See frmStart.
Creating a table from another, via a Make table query.
Dynamic additions to the Row Source property of a form list box at run time.
This database has two tables and two forms.
Tables:
tblBingo - used for frmBingo and the game.
tblBingo bak - the master table, used to refresh tblBingo for each new game.
Forms:
frmStart - opens when the database opens. The code behind New Game button deletes tblBingo, recreates it from tblBingo bak, and opens frmBingo for the new game.
frmBingo - the actual game. This is a tabular form based on tblBingo. Conditional formatting is used to 'remove' the numbers when they have been chosen.
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.
There are two example databases here, showing two different possible methods of approaching the problem of recording and analysing attendances.
Common points demonstrated
Crosstab queries.
Building queries upon other queries.
Using the FormatDateTime function in a query to format the date as Short Time.
Concatenating fields into one field for a query
Calculated columns in queries.
Use of the combo box Column property to reference values in a selected combo box row.
The Forms Collection, in VBA code and for query parameters.
Attendance Register 1
Other points demonstrated:
Passing a label and a check box as parameters to a procedure.
Using the ABS function to get the absolute value of a total when adding up ticks in checkboxes.
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:
Outer join queries.
Use of a NOT IN subquery to allow a Crosstab query to use a parameter value on a form. See also VBA FAQ 14 How can I add a criterion to a Crosstab query? and section 8.4 of the Getting Started VBA Trainer for a further example of the use of a Crosstab query.
Embedded SQL to delete rows and add a row to a table. Also uses DoCmd.SetWarnings to suppress Access warning messages.
Use of IsNull function to see if the user has selected a value on a form.
Use of DCount function to check to see if there are any students registered for a class.
Deleting a selected record on a form by calling wizard Delete record command button code, after deleting the button.
Requery of a form after bound data has changed in order to show the new data.
Use of the IIF function for a variable query criterion to select all pupils or just a selected pupil.
Using a Crosstab query in another query and selecting all columns.
This is a more complex but more flexible method, allowing for varying occurrences of classes.
Click here for example database and explanatory document.
This database was originally developed to assist a FY Project student doing a customer purchasing database.
It shows how to create Order and corresponding OrderLines, with stock quantity updated as items are recorded as sold.
It restricts amendment of existing orders.
DAO and embedded SQL code are used to add stock back if an order is cancelled (say, customer’s credit card not accepted).
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
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:
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.
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.
The database attached here shows how to filter the contents of a list box with each character entered into a text box.
The database opens with a form which has a list box using data from the Chelmer Leisure Membership table.
The form has a textbox into which the user types the starting characters of a member's last name, and the list box contents change with each character typed (or backspaced). The Membership form (just a simple wizard form here) can then be opened using the list box to indicate the required Member (double-click on the selected list box row).
The form also has a Reset button which clears the textbox and restores the list box contents.
Points demonstrated
Use of the Requery method on a list box.
List box SQL using Like for SQL WHERE clause.
Use of the Forms Collection in the list box SQL.
Use of the text box KeyPress event to get each character the user types, check for backspace, and use the Chr function to convert the ASCII character code to a string. This uses a hidden field on a form to build up the characters checked by the list box SQL.
Use of the Len function to check the number of characters currently in the string (need to know when to stop backspacing).
Use of wizard code for an 'open form' command button. The button was created to open the form selecting records where the Membership No matched the entry in the list box. The button was then deleted, and the wizard code used as a sub called from the list box DoubleClick event.
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
Requery method on a form.
Use of wizard code for a 'save record' command button. The button was deleted and the code used as a sub which could be called to force a save of selections of records.
Enabled property to enable and disable a command button.
DCount function to check if at least one student has been selected.
OrderByOn property (can only be set in VBA, I think) and the OrderBy property (set in the form property box) to set the order of records to the Student name rather than the student ID.
DAO code to bookmark a record so can return to it after the form has been requieried. This code uses the same method as that used by the wizard combo box code to find a record.
DAO code to read the selected records to set up email addresses and subject line.
DoCmd.SendObject to send the email. The user has the option of using a BCC address list or a normal address list.
A combo box with contents based on the student records (attendance field) shown on the form.
A common procedure to select/deselect records. This uses the IsMissing function for an optional parameter value. The code then uses Embedded SQL (and DoCmd.SetWarnings) to update the form table tick boxes if the user wishes to select/deselect all, or select those with attendances <= the value in a combo box.
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
Pathname for image folder stored in separate path table and read when form opened. The data table stores just the filename for each image. If the image folder is moved then just the pathname in the path table needs to be altered.
Use of image control Picture property to store the linked path and filename at run time to display the picture.
Uses procedures (more flexible, especially if you have more than one form and a separate images folder for each form) and a file picker window dialog using FileDialog. added March 2008
File picker dialog window to allow the user to select image files from a given folder (but I do not know how to prevent the user from changing the directory in the file picker dialog). The Mid function is then used to strip the full path off to get just the filename for storing in the table record. added March 2008
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
Pathname for documents folder stored in separate path table and read when form opened. The data table stores just the filename for each document. If the document folder is moved then just the pathname in the path table needs to be altered.
Changing a command button to be a hyperlink which then opens an existing document with the Follow method, using the path and filename together to get the full path for the document. The code was developed following an example from Access 2002 VBA Help. This appears to work OK in Access 2007. Adapt this code to open other file types if required, as mentioned above.
Using Access 2002 Command Button Wizard code for opening Word to create a new document. This appears to work OK in Access 2007, although this wizard facility is not available in Access 2007 (perhaps because Access 2007 now has a facility for attachments?). The user must remember to save the document in the required folder used by the database, and must then select the document in order to store the name in the data table - this is all a bit 'clunky' but is the best I can think of at present
Uses procedures (more flexible, especially if you have more than one form and a separate document folder for each form) and a file picker window dialog using FileDialog.
File picker dialog window to allow the user to select document files from a given folder (but I do not know how to prevent the user from changing the directory in the file picker dialog). The Mid function is then used to strip the full path off to get just the filename for storing in the table record.
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
Using the TransferSpreadsheet method to import and export data.
Using a data range area defined in the import spreadsheet.
Public Subs to import and export.
Attaching the system date and time to the export filename.
Checking for error conditions on import.
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:
A simple criterion selecting member records by gender (Male/Female/All). This criterion is on the query for the subform.
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
The Forms Collection to reference a control on the subform: Me!SubformBasedOnQuery!txtTotal=...
The Forms Collection to reference a property on the subform: Me.SubformBasedOnQuery.Form.RecordSource=...
The Forms Collection to reference a method on the subform: Me.SubformBasedOnQuery.Requery
The Forms Collection with wildcards in query criteria to reference main form fields.
DCount to add up the number of records displayed in the subform.
Using the same Where condition for the extended RecordSource SQL and the DCount function criterion.
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.