First Published 8 Jan 2023 Difficulty level : Moderate
Section Links (this page):
Introduction
How can these features be made useful?
Create a Template Database
Create An Application Part
Save a field as a data type template
Summary
Further Reading
Feedback
1. Introduction Return To Top
This is the third part of a series of articles about creating databases using code and templates.
This article discusses several features first introduced in Access 2010 that are little known (and even less widely used).
Application parts and database templates are variations on the same idea.
Both are intended to make it easy to reuse database design elements or an entire database from template files.
Many people are aware that a number of template database files can be downloaded from the Microsoft site.
These include the venerable Northwind database (various versions of which have been available since Access 1.0 in 1992)
Also available are a number of other templates such as Asset Tracking and Contacts.
These can all be accessed from the backstage view of the File menu:
The main purpose of these templates is mainly to help new users get started with the many features of Access.
Unfortunately, many of these templates are poorly designed and make extensive use of features that most experienced developers avoid such as:
• Multivalued and attachment fields
• Table level lookup fields
• Table & field names with spaces . . . etc, etc
As a result, the Access templates generally have a poor reputation.
NOTE:
Two new versions of the Northwind database (beginner/developer) should be available later in 2023.
These are being developed by a dedicated group of experienced Access developers including past and present MVPs
Hopefully these two new 'feature databases' will be a significant improvement on what is currently available.
Unfortunately, the built-in Application Parts are also very basic and are very unlikely to enthuse many people
2. How can these features be made useful? Return To Top
Hopefully this article will help inspire you to make these features work for you
It is very simple to create your own database templates which can then be used as 'starter apps'.
For example, I have created a large number of Speed Comparison Tests for publication on this website and elsewhere.
Many of these make use of the same objects including forms and reports. To save reinventing the wheel, I have created a speed comparison test template (.ACCDT).
I can import this into a new database as an application part and adapt it for each new speed test.
More generally, I have several preferences that I use in all my database apps including:
• The use of overlapping windows display instead of the default tabbed documents
• Disable layout view
• Disable design changes for tables in datasheet view
• Disable compact on close
• Always use event procedures
• Use form headers & footers . . . etc
Many of these can be set from Access Options but some settings such as Overlapping Windows do not 'stick'.
Re-setting this for each new database is very tedious.
a) Current Database Options
b) Object Designer Options
For my commercial apps, I also usually apply various levels of security including:
• disabling full menus, default shortcut menus, special keys and the shift bypass
• hiding the navigation pane
• hiding the ribbon . . . or using a custom ribbon
There are also a number of other settings which can only be done using code. For example, I often:
• Disable subdatasheets (as these reduce performance and can be confusing for end users)
• Hide the Access application window
I often use code similar to this to set various database properties at startup
Sub ModifyStartUpProps()
On Error GoTo Err_Handler
'Delete existing start up properties
DeleteStartupProps "AllowFullMenus"
DeleteStartupProps "StartUpShowStatusBar"
DeleteStartupProps "AllowBuiltInToolbars"
DeleteStartupProps "AllowShortcutMenus"
DeleteStartupProps "AllowToolbarChanges"
DeleteStartupProps "AllowSpecialKeys"
DeleteStartupProps "StartUpShowDBWindow"
DeleteStartupProps "AllowBypassKey"
'By default, set all start up properties to False
StartUpProps "AllowBypassKey", False, True
StartUpProps "AllowFullMenus", False, True
StartUpProps "StartUpShowStatusBar", False, True
StartUpProps "AllowBuiltInToolbars", False, True
StartUpProps "AllowShortcutMenus", False, True
StartUpProps "AllowToolbarChanges", False, True
StartUpProps "AllowSpecialKeys", False, True
StartUpProps "StartUpShowDBWindow", False, True
'for developer mode on specified workstations, enable props
If Environ("ComputerName") = "XXXXXXX" Or Environ("ComputerName") = "YYYYYY" Then
If CurrentDBFileType = "accdb" Then
StartUpProps "AllowBypassKey", True, True
StartUpProps "AllowFullMenus", True, True
StartUpProps "StartUpShowStatusBar", True, True
StartUpProps "AllowBuiltInToolbars", True, True
StartUpProps "AllowShortcutMenus", True, True
StartUpProps "AllowToolbarChanges", True, True
StartUpProps "AllowSpecialKeys", True, True
StartUpProps "StartUpShowDBWindow", True, True
End If
End If
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " in ModifyStartUpProps procedure : " & Err.description, vbOKOnly + vbCritical
Resume Exit_Handler
End Sub
I also re-use several objects in each of my apps such as a settings table, several standard functions used on all forms etc, etc . . .
Most developers will have similar lists of standard objects and Access options to suit their own preferred methods of working.
Creating a standard default database template for widespread re-use can therefore save a lot of time.
3. Create a Template Database Return To Top
a) Create a blank database and import all the objects you always want in a new database. For example:
• Default form & report with your preferred design features.
• Tables containing settingsor configuration data.
• Modules with commonly used functions.
• Splash screen / startup form.
b) Set the database properties in Access Options (Current Database / Object Designers tabs)
c) Set other options in code and add this codeto the Form_Load event of your startup form or in an autoexec macro
NOTE: a startup form loads BEFORE the autoexec macro runs. See my article: Debunking Myths about the Autoexec Macro
d) Add any non-standard references you regularly use e.g. Microsoft Office xx.0 Object Library
When you have set everything you need, create a new template database (.ACCDT)
From the File menu backstage view, click Save As . . . Save Database As . . . Template
Now click the Save As button. The Create New Template dialog appears:
Now specify the following information:
Name (REQUIRED)
Enter text that will identify the template. Access displays this name alongside the template or application part.
Description
Enter text that describes the contents or purpose of the template. This appears in the tooltip for the template.
Category
Select User Templates to have the template show up under User Templates on the backstage menu (the default).
You can also add a category by typing it, and you can then assign this and future templates to that category. The new category will show up in the backstage menu.
Icon
Only used for application parts
Preview
Specify a larger image to display for the template in Backstage view. When you browse for a template, this image is what you will see for this template.
Primary Table
This is disabled for templates (only used for application parts)
Instantiation Form (OPTIONAL)
Specify a form that will open by default when databases made from this template are first opened. This form by default only opens once.
The form can contain code that it runs when it opens, but the presence of code will generate a security warning.
Application Part
Leave this check box unticked to save the database as a database template.
Include Data in Template (OPTIONAL)
Select this check box to save the data that is in the database as part of the template. When new databases are created from the template, they include this data.
For example:
Click OK to save your template. By default, Access saves your template to
C:\Users\CurrentUserFolder\AppData\Roaming\Microsoft\Templates\Access\
The saved template can then be selected from the Personal tab on the File . . . New database menu
Click Create to make a new database based on the template.
If you specified an Instantiation form containing code , you will then see this message:
4. Create An Application Part Return To Top
You can save an individual object such as a standard form or a group of objects
The process is almost identical to the above.
When you have collected all the objects you need, create a new template database (.ACCDT)
From the File menu backstage view , click Save As . . . Save Database As . . . Template
Now click the Save As button. The Create New Template dialog appears:
Now specify the following information:
Name (REQUIRED)
Enter text that will identify the application part. Access displays this name alongside the application part.
Description
Enter text that describes the contents or purpose of the application part. This appears in the tooltip for the application part.
Category
Select User Templates to have the application part show up under User Templates on the ribbon (the default).
You can also add a category by typing it, and you can then assign this and future application parts to that category. The new category will show up in the ribbon.
Icon
Specify an icon to display for the application part. This icon will show up in the application parts gallery on the ribbon.
Preview
Not required for application parts.
Primary Table (OPTIONAL)
Specify a table that will be the primary table for the application part.
This table will be used by default to relate the application part to other tables in a database to which the part is added.
When someone uses the application part, Access starts a wizard to help create the relationship.
Although the Primary Table is used by default, you can select a different table when the wizard runs.
Instantiation Form
Specify a form that will open by default when databases made from this application part are first opened.
This form by default only opens once. The form can contain code that it runs when it opens, but the presence of code will generate a security warning.
Application Part
Select this checkbox to save the database as an application part.
NOTE: You must select this check box before you can specify a value for Primary Table.
Include Data in Template (OPTIONAL)
Select this check box to save the data that is in the database along with the application part.
When new databases are created using the application part, they include this data.
Click OK
You can now import the application part(s) into another database as required
NOTE: You can also save and reuse templates / application parts created by another developer that you trust.
To do so, save the supplied ACCDT file in your Templates\Access folder
5. Save a field as a data type template (*.accft) Return To Top
To create a data type template , you select one or more table fields, and then use a command on the ribbon to save them as a template.
Make sure that the field(s) are set up the way that you want them before you start
Set any properties that you want to be preserved as part of the data type template.
For example, if you want to create a 'euro' data type template , you might create a currency field , and then set its Format property to Euro.
Next open the table that has the field(s) that you want to save as a template.
Select the field(s) that you want to save for reuse.
On the Table Fields tab, in the Add & Delete group, click More Fields , and then click Save Selection as New Data Type.
In the Create New Data Type from Fields dialog box, specify the following:
Name (REQUIRED)
It identifies your data type template, and is displayed in the list of available fields when you choose a field from the More Fields list.
Description
Enter text that describes the contents or purpose of the data type template.
This appears in the tooltip that is displayed when you hover over the data type template in the More Fields list.
Category
Select a category in which to list the data type template in the More Fields list. By default, the category is User Defined Types.
Instantiation form
If desired, specify a form that will open by default the first time that the data type template is used.
Click OK to save your data type template.
The new datatype will now be available from the Table Fields . . . More Fields . . . User Defined Types menu
After adding these items, your Templates\Access folder may look something like this:
6. Summary Return To Top
Although the built-in Access templates and application parts are of limited use, both features can be extremely useful for developers.
By making frequently used databases or application parts easily available for re-use, developers can save time and avoid repeating tasks unnecessarily
7. Further Reading Return To Top
External links:
Microsoft Access tips: Default forms and reports
Save and reuse database design elements
Other related articles on this website:
Create ACCDB & ACCDE Files Using Code
Add References Using Code
8. Feedback Return To Top
I would be grateful for any feedback on this article including details of any errors or omissions
If you have any comments or questions, please contact me using the feedback form below to send me an
email
Colin Riddington Mendip Data Systems Last Updated 8 Jan 2023
|
Return to Access Articles Page
|
Return to Top
|