Microsoft Access Tips for Serious Users

Provided by Allen Browne, August 2006.  Last updated: Dec 2010.


Splash screen with version information

Note: This code will not work with the 64-bit version of Office. (It does work with 64-bit Windows.)

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 (zipped) for Access 2000 and later or Access 97. You can also view the code for this utility.

Your software name Your Version MS Access File Format JET/ACE JET User Windows User Workstation Data File Copyright and Developer About this program - screenshot

Click an element for details

Copy to your database

  1. Open your database, and import: In Access 97 - 2003, use Import on the File menu.
    In Access 2007 and 2010, 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:

Caption Control Source Description Purpose
Version: ="1.00" Whatever you want. Indicates if the user has your latest version.
MS Access: =GetAccessVersion() Version of msaccess.exe Indicates if Office service packs are needed.
File Format: =GetFileFormat() db format, e.g. 97, 2002/3, accdb Helps identify version-specific problems.
JET/ACE: =GetJetVersion() Version of the query engine Indicates if JET service packs are needed.
JET User: =CurrentUser() User name (Access security) Helps identify problems with Access Permissions.
Win User: =GetNetworkUserName() User name (Windows) Helps identify problems with Windows permissions. Useful for logging.
Workstation: =GetMachineName() Computer name Helps identify corruptions from faulty hardware. Useful for logging.
Data File: =GetDataPath("Table1") Location of back end database Indicates if the front end is connected to the right data file.

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.

GetAccessVersion()

MS Access Service Pack Version
97 SR-2 8.0.0.5903
2000 SP-3 9.0.0.6620
2002 SP-3 10.0.6501.0
2003 SP-3 11.0.8166.0
2007 SP-3 12.0.6607.1000
2010 - 14.0.4760.1000

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 from http://support.microsoft.com/sp. Office 97 is no longer supported, but you may get the patch here.

(Note: These are the version numbers of msaccess.exe, not the Office numbers shown under Help | About.
The Access 2010 numbers seem unstable at release time.)

File Format

GetFileFormat()

Access 97 Access 2000 Access 2002 Access 2003 2007 & 2010
97 MDB
97 MDE
2000 MDB
2000 MDE
2000 ADP
2000 ADE
2000 MDB
2000 MDE
2000 ADP
2000 ADE
2002/3 MDB
2002/3 MDE
2002/3 ADP
2002/3 ADE
2000 MDB
2000 MDE
2000 ADP
2000 ADE
2002/3 MDB
2002/3 MDE
2002/3 ADP
2002/3 ADE
2000 MDB
2000 MDE
2000 ADP
2000 ADE
2002/3 MDB
2002/3 MDE
2002/3 ADP
2002/3 ADE
2007 ACCDB
2007 ACCDE
2007 ACCDR
2007 ACCDT

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:

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

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 and 2010 support all eight 2000 and 2002/3 formats, plus four new ones:

Note: Even though Access 2010 uses the 2007 ACCD* file format, you will no longer be able to use the tables in Access 2007 if you add calculated fields to them.

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

GetJetVersion()

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 (acecore.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.

GetNetworkUserName()

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").

GetMachineName()

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.

GetDataPath()

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.


Home Index of tips Top