Grover Park Consulting

Free Demos, Sample Databases and Resource Links for Access Relational Database Design

Download a demo accdb with working code and sample data

Accounting Functions in a Tenant Billing Database
Examples of some Simple Accounting Functions (Charges, Invoices and Payments) in a Tenant Billing Database.

Check for an Existing Value in a Table
Does a Value already exist in a field in a Table? Find out before trying to add it. This sample Access database uses different validation methods, including the form's Error Event, to make sure values entered into fields are valid and not duplicated before they are committed to the table.

Create and Completely Hide a DUAL Table in Access
You can COMPLETELY hide a table in Access. This demo shows how.
     Dual tables have these characteristics:
         a) They are hidden, so users are not tempted to use them for other purposes.
         b) They consist of a single column and contain a single record.
         c) They are not editable.

Read more in my blog on this topic.

Lose Referential Integrity by splitting tables into multiple back ends
When an accdb grows too big, problems soon follow. The nominal maximum size of an accdb is 2GB. However, in most applications, the practical maximum size is much less. Unfortunately, one common suggestion offered to deal with "Access Bloat" is to split the tables out into multiple Back End accdbs.

What Happens When You Split Tables into Multiple accdbs? While that does reduce the size of each individual accdb, it comes at a hefty price: you lose the ability to enforce Referential Integrity between tables in the different accdbs. Within any single accdb, of course, you can still enforce RI, but not across them. And, as you probably already know, you cannot enforce RI from the Front End accdb when the tables are in a separate accdb.

The solution? If a compact & repair doesn't restore the BE to a manageable size, you will need to consider a more robust Relational Database Management Server (RDBMS) like SQL Server Express or SQL Server Developer Edition or MySQL.

The sample accdbs in this download illustrate a single accdb with interface and tables, a properly split accdb with FE and BE files, and a multiple BE split version, in which RI has been lost across the different accdbs.

Main form/subform designs support many-to-many relationships.
Here's one way to enter records in all three tables in a many-to-many relationship.

Two main form/sub form pairs allow the user to enter either songs or artists or both and slip back and forth easily between them.

Multi-Field Query By Form Searching Combo Box
A question at Utter Access also led to this one. We needed to filter records on a form, but we found Microsoft templates were too limited. I was challenged to make it work for a variety of situations and the result is the Multi-Field Filter Form sample accdb.

A second form models the method I use to search for a record using a selecting combo box. This one if very common and there isn't anything too unique in my approach. This requirement is very common; one more good example seems worthwhile.

Open Form In Add Or in Edit Mode
Access forms can be opened as:

  -- Read Only
 -- Add New Records Only
 -- Add New Records and Edit Existing Records

A single argument on the DoCmd.OpenForm action determines which data mode applies.

This simple Access Relational Database Application illustrates two of those options, leaving it to the user to try the third.

Random Selection of "N" members in each Category
Randomly select "N" items from each of several categories with a Top "N" subquery. With no primary key, there is no way to order the subquery records. A temp table with randomly generated unique keys for each record solved that.

Bonus Code: Adding and deleting records in a temp table required creating a temp external accdb on the fly.

Scrolling Progress Message on a Form
A question at Utter Access led to this one.
The specific request was for a simple way to display a progress message in a scollable text box on a form with the most recently completed step always visible.

Another member suggested placing the most recently completed step at the TOP, rather than the BOTTOM, of the text box so that it is always displayed.

As is usually the case, I ended up adding a couple of additional features to make the demo more interesting. These can be used "as is" or replaced in your implementation of the scrollable text box into your Relational Database Application.

This demo incorporates a precise timer feature based on the GetTickCount() API to measure elapsed times as well as the scrolling message box.

Travel "Pack" Demo
One-to-Many Relationships in a main form/subform design.

This demo, based on a travel scenario where groups of people travel together "in a pack", was created for someone at Utter Access . It is a good illustration of the main form/sub form design required to manage one-to-many relationships in a transaction scenario. The transaction itself (the journey, or travel) is tracked in the one-side table, and the details of the transaction (the "pack members" traveling together) are tracked in the many-side table.

VBA Code to Find Previous Weekday
This Access database, available in Access 2003 (mdb) and Access 2007 (accdb) formats, is fairly simple. it takes two arguments--a start date and a weekday--and returns the date in the prior 7 days on which that weekday falls.

It also has some simple language swapping in it to change captions on controls on the forms from one language to another (English and Español).

Key Access database concepts demonstrated in one or more of the free demos on this page include the following: Table design or Normalization, the DLookup() Function, Form Level Error Handling, VBA error Handling, Embedded Macros, user defined system table for application level variables, Public Enums to provide user-friendly terms for error codes, use Control Tags to store values, Access 2010 Control Layout Grid, completely hide a table, create scratch accdb for temp table, VBA to export table, VBA to export querydef, delete a second accdb file, load performance of combo boxes with large recordsets, pre-filtered combo boxes, dynamic SQL load single record recordsets, cascading combo box

©Copyright 2010-2019 Grover Park Consulting. All Rights Reserved.
Free Resources and Full Working Demos for Microsoft Access Database Design and Development