Microsoft Access Tips for Serious Users

Provided by Allen Browne, August 2006.  Updated May 2009.

Splash screen with version information

To showcase your database, you want a nifty screen that splashes color at start-up. You need this screen to give version details also, so you can show it through About this Program (typically on the Help menu.)

The screen will show the software name, intellectual property rights, and your contact details just in case someone needs support. When they do call for support, it can help if this screen shows their Access setup and version details.

It splashes on screen for 2 seconds when your database loads. If you open it from a button or menu, click on the form to close it.

Download the sample database for Access 2000 and later or Access 97. (280kb, zipped.) You can also view the code from this utility.

Click an element for details

Copy to your database

1. Open your database, and import:
   • module ajbVersion,
   • form frmHelpAbout, and
   • macro AutoExec.
   In Access 97 - 2003, use Import on the File menu.
   In Access 2007, the External Data tab of the ribbon handles imports.
2. Open the module in design view to verify Access understands it.
   In the code window, choose Compile on the Debug menu.
   (In Access 2000 or 2002, you may need to set references.)
3. When the splash screen closes initially, it opens another one.
   If your next form is not named Switchboard, change the name in the code.
   For example, change the line:
       Const strcNextForm = "Switchboard"
   to:
       Const strcNextForm = "Form1"
   If you do not want another screen to open, use:
       Const strcNextForm = ""
4. Open the form in design view.
   If you have attached tables, replace "Test1" with the name of your table. Otherwise use:
       =GetDataPath("")
5. Change the Caption of the labels to show your software name, copyright, and developer details.
6. (Optional.) Set the form's Picture property to whatever you want.
    

Why show these details?

This table summarizes the information displayed on the splash screen, and why you want to show these details:

Version and Data File are the only ones you need to change. Let's see what each one tells you.

Your Version

This is a number you manually increment each time you modify the database, and distribute a version to your users.

MS Access Version

This number indicates the version of msaccess.exe. The major number (e.g. 12.0) indicates the office version. The minor number (e.g. 6423.1000) indicates what service pack has been applied. The number may be higher than shown at right if you apply a hotfix, such as Service Pack 2 for Access 2007, or kb945674 for Access 2003.

Service packs are available using Windows Updates from your control panel. Office 97 is no longer supported.

(Note: These are the version numbers of msaccess.exe, not the Office numbers shown under Help | About.)

File Format

This text box indicates what file format your database is using. If the database is split, it refers to the front end.

Access 97 has two possible formats:

• MDB (Microsoft Database),
• MDE (compiled-only database.)

Access 2000 uses a different format MDB and MDE, and added:

• ADP (Access Database Project, using SQL Server tables),
• ADE (compiled-only project.)

Access 2002 introduced its own file storage format, but supports the Access 2000 ones as well.

Access 2003 used the 2002 format (now called 2002/3), retaining support for the 2000 formats.

Access 2007 supports all eight 2000 and 2002/3 formats, plus four new ones:

• ACCDB (database based on the new ACE engine), and
• ACCDE (compiled-only ACE database.)
• ACCDR (ACCDB or ACCDE limited to runtime)
• ACCDT (database template)

(Note: descriptions may not be correct for versions beyond Access 2007.)

JET/ACE Version

JET (Joint Engine Technology) is the data engine Access uses for its tables and queries. Different versions of Access use different versions of JET, and Microsoft supplies the JET service packs for JET separately from the Office service packs.

Access 97 uses JET 3.5 (msjet35.dll). A fully patched version of Access 97 should show version 3.51.3328.0. Microsoft no longer supports Access 97, so it can be difficult to get service packs.

Access 2000, 2002 and 2003 use JET 4 (msjet40.dll.) They should show at least 4.0.8618.0. The minor version may start with 9 (depending on your version of Windows), but if it is less than 8, it is crucial to download SP8 for JET 4 from http://support.microsoft.com/kb/239114. The issue is not only that older versions have unfixed bugs, but that you are likely to corrupt a database if computers with different versions of JET use it at the same time.

Access 2007 uses a private version of JET call the the Access Data Engine (ace.dll), with a major version of 12. Since this version is private to Office, we expect it to be maintained by the Office 2007 service packs, and not require separate maintenance.

JET User

This displays the name the user logged into the database with. If you are not using Access security, it will be the default user, Admin. If you have secured the database, knowing the user name may help you track down problems related to limited user permissions.

The CurrentUser() function is built into Access, so no API call is needed.

Windows User

This displays the Windows user name (see User Accounts in the Windows Control Panel.) It can help in tracing a problem related to the user's limited permissions under Windows. You can also call GetNetworkUserName() in your database to log user activity.

We use the API call, as it is possible to fudge the value of Environ("username").

Workstation

This displays the name of the computer, as shown on the network. Corruption of the database is usually associated with the interrupted write (see Preventing corruption), so logging users in and out of the database with GetMachineName() can help to identify the machine that is crashing and corrupting the database.

Data File

Use this with a split database, to indicate what file this front end is attached to. Occasionally you may get users who attached to the wrong database (such as a backup.)

Specify the name of an attached table in place of "Table1." If you do not have an attached table matching the name you used, you see #Error. To suppress this option if you have no attached tables, use a zero-length string, i.e.:
    =GetDataPath("")

Note that the screen reports what data file is expected, whether found or not. For example, the sample database has a table named Test1 that it expects to find in C:\Data\junk.mdb. You probably have no such file, but the splash screen still indicates what data file it is looking for - useful if the user cannot tell you what data file they used previously.

Conclusion

That should help you to look good, and give good support for the databases you develop.