Appendix D

Converting Existing Access Applications


"To convert or not to convert?" and "How much conversion work should we do?" are the two primary questions that trouble Access users and developers with each new version release. In Access 97 you can enable applications created in prior versions to run in the newer version without doing a complete conversion. Alternately, you can do a minimal conversion of an older application into the 97 format but without taking advantage of 97's new capabilities. The third, and most labor-intensive, option is for you to perform a complete conversion of the older application to 97 by reworking objects and recoding procedures to best utilize the new features.

Which of these options you choose will be determined by a mix of your users' needs, each application's structure and purpose, and budgetary issues. This chapter will help you pursue any of the three directions by providing:

Information on the conversion and enabling processes performed by Access

Descriptions of features that have changed between previous versions and Access 97

Instructions and examples for converting object properties and program code to take fullest advantage of Access 97

Understanding Conversion to Access 97

I cannot think of an Access application upgrade/conversion process that we have been through that went flawlessly. This is not to say that every conversion is fraught with problems, although many of them are. Rather, each new release of Access brings both challenges and opportunities.

The challenge is to move the application from the older version's format to that of the newer version without losing any of the functionality or integrity of the original application. The opportunity, in contrast, is to embellish the application to take advantage of the enhanced features of the newer version. Thus this dog-chasing-its-tail situation arises where you want to tinker with the application as little as possible to move it to the new platform (to minimize the risk), while at the same time incorporating all the new goodies that have been added into Access (to maximize the reward).

If you've read the rest of this book already, you can probably guess what my advice is: To survive this conundrum, like so many other development challenges, you should apply good planning. Most developers sit down and run Convert from the Access menu on an existing system without giving a minute of consideration to the issues involved. In fact, just a little forethought can make the conversion process much smoother.

Here are a few simple techniques that serve as examples of how a little forethought can improve the conversion process:


I use the phrase "Access 1/2" in this appendix when referring in aggregate to any version of Access 1 or Access 2. The string means "Access 1 or Access 2."

This appendix provides a comprehensive reference to help you understand the range of issues and options that you can encounter when converting older Access applications to 97.


As my company converts more and more applications, we plan to enhance this conversion document with new information as it comes to light. Check the Access Expert Solutions Web site at www.ldfinfo.com periodically for information on updates to this appendix.

Utilizing Older Formats

Here is a summary of what you can and cannot do between versions of Access:

Access 2 and 95 can do the following (this is called "forward compatibility"):

Access 2 and 95 cannot:

Access 97 can do the following (this is called "backward compatibility":

Access 97 cannot:

Conversion is a one-way street. Unlike Excel and Word 97, which can save files in pre-97 formats, Access 97 cannot create a database in an older format.

Defining the Conversion Methodology

At a high level, the primary conversion steps are these:

  1. Make changes to the application that are best made prior to conversion.
  2. Compile all code in the database. In Access 95, you can compile all of an application's code from the Run menu, but in Access 1 and 2 you must open each form and report and compile the code there.

Applications that are properly compiled before they are converted have fewer conversion problems. For example, converting an uncompiled Access 2 application can cause the converter to skip conversion of any uncompiled DoCmd actions.

  1. Rename the database to the name it will have when archived; this will facilitate the next conversion step. Repair and compact the database.

Do not rename the database to its archive name after the repair and compact operation. For an Access 95 database, this will decompile the code you just compiled. Rename the file before your repair and compact.

  1. Using Access 97, convert using the Tools, Database Utilities, Convert Database... menu option. You cannot convert a database over itself, you must choose either a new destination file name, file path, or both.
  2. Open the database in Access 97 and select Debug, Compile and Save All Modules if this option is available. When Access completes a conversion it attempts to compile the code, but if compilation was not successful you will want to retry compilation, note any error messages generated, and correct the described anomalies.
  3. Begin coding the Access 97-specific enhancements to the application.

Occasionally, errors are generated during the conversion process and repeated attempts to convert generate the same error message. In this scenario, creating a new database in Access 97 and importing all of the objects from the older database may prove a more successful method for converting the stubborn application.

Converting References

If you are converting an Access 95 application that includes a reference to another Access 95 database, follow these steps:

  1. Copy the library database and application database to a backup directory. (Do not rename the databases, which will cause the files to lose the compiled state you previously induced).
  2. Convert the library database from the backup folder into the directory it was originally located in, using the original file name. Do the same with the application database.
  3. Open the application database and check the References dialog to see that the reference to the library database is still intact. If necessary, compile the database, noting and correcting any failures.

Always open an Access 97 database immediately after conversion and check the References dialog for any references prefixed with "Missing:". If you do not correct such broken references before you attempt to compile the converted database, strange and inaccurate error messages can be generated during compilation. The most common of these spurious messages is "Can't find project or library" with the cursor inaccurately positioned to a string function like Chr or Left.

Converting Replicated Databases

An Access 95 replica database is a dependent of its Design Master database, and thus cannot be converted to Access 97 directly. Instead, you must follow these steps:

  1. Deploy Access 97 on all workstations that are going to run the converted replica databases.
  2. Make a copy of the Access 95 Design Master database in a directory different from its standard location. You do not want the master to synchronize with the replicas while conversion is taking place. Discontinue use of the original Design Master and any members of its replica set.
  3. Open the copied Design Master in Access 95 and convert it to a primary design master by choosing Tools, Replication, Recover Design Master....
  4. Create a test replica based on this new Design Master.
  5. Close Access 95 and open Access 97. Select Tools, Database Utilities, Convert Database... and convert the new Design Master.
  6. Open the converted Design Master and make any desired changes to the application to take advantage of new Access 97 features. Keep a log of the changed objects.
  7. Synchronize the converted Design Master with its test replica. Ensure that the process worked effectively.
  8. Discontinue use of the converted Design Master and, if necessary, return to the machine hosting the original Design Master. Open Access 97, select Tools, Database Utilities, Convert Database..., and convert the original Design Master.
  9. Delete any objects changed during the enhancement of the initially converted Design Master (according to the log kept in step 6), and import the enhanced objects from that database into the current Designing Master.
  10. Synchronize the Design Master with its replica set.
  11. Open each replica in Access 97 from its own workstation and ensure that conversion was completed successfully.

Converting Secured Databases

Access 97 setup creates a new workgroup information file (WIF) called SYSTEM.MDW in the Sytem directory. You cannot convert a secured database while a member of this workgroup. To convert a secured application, you must log in to the WIF appropriate for that application, and you must have the following permissions on the application:

Users should not, and usually can not, convert a secured database. Conversion is done by the database owner, who has the required passwords to perform conversion. If you are the owner/administrator of a secured Access 95 database, follow these steps to convert it:

  1. With Access closed, use the Workgroup Administrator application to join the WIF (unconverted) for the secured database. The administrator application is named WRKGADM.EXE and is stored in the System directory.
  2. Start Access 97 (you do not need to open a database file) and select Tools, Database Utilities, Convert Database.... This action converts the secured database file.
  3. Choose Tools, Database Utilities, Compact Database and compact the newly converted database.
  4. Close Access and use the Workgroup Administrator to rejoin the default workgroup created during Access 97 setup.
  5. Open Access 97 and choose Tools, Database Utilities, Compact Database. Compact the workgroup information file for the secured database. This enables the file for Access 97; you cannot actually convert the workgroup file.
  6. Deploy Access 97 and the converted application database to your users. Ensure that each of them uses the Workgroup Administrator to join the WIF for the application. Alternately, install a shortcut on each workstation using the application and use the shortcut to launch Access 97 with the /wrkgrp command-line argument to point to the WIF.

The flow for converting a secured Access 1 or 2 database is different from that just described, because the WIF for the application must be converted manually. Follow the first three steps listed above, then continue with these steps:

  1. Open the Workgroup Administrator on your Access 97 workstation and choose Create.... Build a new workgroup information file using the exact same information (Name, Organization, and Workgroup ID) used in the Access 1 or 2 workgroup file. Close the program.
  2. 5 Open Access 97; you do not need to open a database. From the Tools menu choose Security, User and Group Accounts- and re-create all of the group accounts used in the converted application. Next, re-create all of the user accounts, names, and Personal IDs used in the converted application.

Now resume the prior list at step 6.

Examining Access' Conversion Processes

For simple applications, there may not be much work required after you tell Access to convert the database file. If an application is uncomplicated, and you do not have the time, energy, or money to enhance it, running conversion from the menu system and then redeploying the application to users may take only a few minutes.

Database Conversion Processes

Access 97 creates a new database file in Jet 3.5 format and copies in the objects from the source application, changing some syntax and attributes along the way.

Conversion also creates a Visual Basic for Applications project, which is a set of code and objects that are maintained by the new VBA editor and browser, but stored by Access. Each VBA project must have a project name, which is reflected in the References dialog and Object Browser. During conversion, Access uses these rules to create a project name:

The implementation of the last item on the list is confusing at best. The online documentation states that, during conversion, Access will fix proposed project names if they are Access reserved words like "application, form, and report." The supposed name fixup involves Access assigning an underscore to the reserved word, thus substituting a project name like "application_" in place of the proposed "application."

In reality, the name fixup does not work as designed. I converted seven Access 2 applications with reserved words as names: APPLICATION.MDB, DIM.MDB, FORM.MDB, INTEGER.MDB, QUERYDEF.MDB, REPORT.MDB, and TABLEDEF.MDB. Here are the results:

The key consideration here is that, if your Access 2 database file name is an Access reserved word, you should: rename the database to a non-reserved word, recompile, and re-compact the database before conversion; or, rename the database during conversion to a non-reserved word. Your conversion efforts will be more successful.


The project name fixup issue described here only applies to Access 1.x and 2.x databases; the filename extension MDB on project names prevents the confusion with converted Access 95 files.

Menu Conversion Processes

During conversion, custom toolbars are converted to the new command bar structure. However, custom menu bars displayed via macros are not converted automatically, they are interpreted into command bars at run time. Menu macros can be converted manually; see the "Macro Conversion Tasks" section later in this appendix for more details.

Table Conversion Processes

Access converts the previous Jet structure to the current structure, with no perceptible change to the developer or user.

Conversion adds a new table MSysModules2 for storage of the VBA project information. A converted database also includes the prior MSysModules table, but conversion removes all permissions from it, thus it cannot be interrogated or viewed by any means.

If the source database employs custom toolbars, conversion adds a hidden system table MSysCmdbars that holds the command bar definitions of the toolbars. The previous table for toolbars-MSysToolbars-is deleted from the converted database, so you must remove or modify any code or objects that refer to the table.

Some Access 1 validation rules cannot be converted. If Access finds a rule it cannot convert, it stores the failure information in a table called ConvertErrors. Always look for this table after a conversion. For more information on this issue, see "Validation Rules" in the section "Table Conversion Tasks" later in this appendix.

Query Conversion Processes

Access converts queries to the new Jet format and recompiles them. No significant alterations are made along the way.

Form Conversion Processes

Converted forms are saved in a new, faster format. If Access finds no code behind a form, it changes it to a lightweight form during conversion. A lightweight form has a HasModule property value of False, does not invoke VBA when loading, and thus loads faster than a similar form with a class module.

Access also converts form properties internally from Variant values to specific data types. This can effect your code, as described in the "Data Types" topic of the "Module Conversion Tasks" section that follows.

Finally, the property stream for an ActiveX control located on a form is converted with the form, and the related reference is ported as well.

Report Conversion Processes

Reports undergo a conversion process almost identical to that of forms; refer to the form conversion discussion in the previous section. As with forms, property values are stored in strongly-typed format and lightweight reports are created where possible.

Macro Conversion Processes

During conversion, each DoMenuItem action in a macro is converted to the appropriate RunCommand action. The DoMenuItem method statements in VBA code, however, are not converted but will run unmodified.

Macros are not converted to code automatically during conversion. However, macros can be converted manually; see "Converting to VBA" in the section "Macro Conversion Tasks" that follows for more details.

Module Conversion Processes

The conversion process alters very little of your code during conversion. By and large, few syntax changes were introduced with Access 97, although many syntax additions were made. See the section "Module Conversion Tasks" for several topics on syntax additions.

DoCmd Actions

DoCmd actions from prior Access versions are now methods of the DoCmd object. During conversion, Access 97 removes the space between the DoCmd and the method and inserts a dot (period), as in the following:

DoCmd.Hourglass True


You should not have to perform any of these conversions manually except where DoCmd strings are being created in code in add-ins or utilities. See the topic "DoCmd Actions" in the section "Module Conversion Tasks" for more information.

Print Action

When converting Access 1 and 2 applications, the action keyword Print is automatically changed to the new PrintOut method, as in this example:

DoCmd Print  ' Before conversion

DoCmd.PrintOut  ' After conversion


Applying Manual Conversion Processes

Once Access' conversion routine is complete, you will want to open the converted application, inspect the objects, and compile all modules in order to gauge the health of the system. You may want to do some preliminary testing as well.

When you have ascertained that conversion was successful, you must then determine which manual conversion processes are valuable enough to apply to the application. The remainder of this appendix lists the manual conversion tasks that you may want to consider applying to your system. The tasks are grouped according to the primary database objects or elements involved, then listed by feature, class, command, or similar keyword. In general, I've listed the conversion tasks by the older approach or keyword rather than the new one, under the assumption that you will be more familiar with the syntax in the converted application than the syntax item to replace it with.

If a particular task has been adequately documented in Access' on-line Help system, I provide the name of the Help file topic as a reference rather than repeating the information here. To find a specific named help topic, use the Find tab in the Help system.

Setup Conversion Tasks

The following three sections describe conversion tasks related to the configuration of the workstation developing or running a converted application.

Add-Ins

If your application relies on the presence of a library database for add-ins, builders, shared code, or wizards, the method for installing and referencing such add-ins has changed.

First, library databases must be referenced from the calling application. If you install applications into a uniform environment where each workstation has similar directory path setups, you can ship an application with pre-installed references. Alternately, you can use Registry keys to help Access find missing references (see Chapter 20, "Applying Development Shortcuts," for more information). You can also write a setup routine in Access that helps the user rebuild references by providing code to manipulate the References object.

For menu add-ins, builders, and wizards, references are not required but the mode of invoking these add-ins has changed from pointers in an INI file to pointers in the Registry. Proper Registry entries must be written during application setup in order to support these types of tools. See Chapter 20 for details on add-in Registry keys.


The Setup Wizard in the Office Developer Edition has an improved screen for helping you configure your custom setup to create Registry keys.

INI Files

Configuring the Access runtime environment for an application was handled by settings in an INI file in Access 1 (MSACCESS.INI) and 2 (MSACC20.INI). The following table shows the INI file settings used in the [Run-Time Options] section in these prior files and their replacements in the Startup dialog. You have to open the dialog (Tools, Startup...) in the converted application and set the properties shown in Table D.1.

Table D.1 Converting Run-time Settings From an INI File to 97 Startup Properties

Ini File

97 Property

Icon

Application Icon

TitleBar

Application Title

StartupScreen

Display Form

To replace the HelpFile setting in the Access 1 or 2 INI file, enter a HelpFile property value for the converted forms and reports.

Profiles

You can tailor how Access or Jet behaves on a per-person basis by creating a user profile, which is a set of Registry keys that uniquely configures Access for one user. With multiple defined-user profiles on a workstation, each Access session or application session can be tailored with respect to the Access title bar string and icon, default Help file, available add-ins, and so forth.

You can create desktop shortcuts that launch Access based upon a named profile by including the /profile profilename string in the shortcut's command line.

See the Access 97 Help topic "Customize your application's environment with user profiles" for more information.

Database Conversion Tasks

The next eleven sections describe database conversion issues related to the database file, its high-level objects, or their properties.

Break Mode

Access 97 now has three available settings on the Advanced tab of the Options dialog that determine general error handling behavior. Access 95 only had two settings and Access 1 and 2 had none. When debugging your converted application, you will want to be aware of the new settings.When dealing with errors, Access differentiates between a handled error (one that occurs while an error trap is in effect) and an unhandled error (no On Error Goto... is in effect).

Here are the available settings:

These error handling settings are part of the user's profile for the Access environment and are not saved in the converted database file.

Database Properties

Database properties were added to Access in version 95, and they are a surrogate for the ActiveX Document summary information found in other Office documents. Access does not use the standard storage metaphor employed by other Office documents to place the summary information at a specified location in its file; thus, the database properties of an Access file cannot be divined with tools that read such properties from other Office documents.

Database properties come in two "flavors." First, items available in the Startup Properties dialog box are available in a database's Properties collection. Chapter 17, "Bulletproofing Your Application Interface," provides an example of code that sets a database property into this collection using the CreateProperty method.

Other database properties-those resembling traditional Office document summary information like Subject and Author-are accessible from Access' File, Database Properties menu option. They can also be modified from code that addresses these Jet documents in the Documents collection that contains the properties: SummaryInfo and UserDefined.

See the online Help topic "Database Properties" for more information regarding the manipulation of database properties in your converted application.

Long File Names

If you attempt to run conversion on a Windows NT Workstation machine and you installed Access 97 into a directory with nine or more characters in its name, conversion will fail. Convert your applications on a different machine.

Module Count

Access 97 allows 1,024 modules in a single database file. Each of these counts as one module: a standard module, and a form or report with a module (one that will have the HasModule property set to True on conversion). If your application has more than this maximum number of modules, you should combine code into fewer standard modules or re-architect the application to use a library database and retest it before attempting conversion. If you exceed this module limit during conversion, you will usually see the alert "Out of memory" and the process will terminate.

Object Names

As a general rules, a object name in a VBA project must be unique within the project, or complications can arise. Such complications are detailed for you here.


Object name conflicts are generally impossible to generate in applications that use good object naming conventions as described in Chapter 5, "Creating Naming Conventions."

Object names that begin with "Form_" or "Report_" should be changed to remove these prefixes. VBA now uses these prefixes to describe open instances of objects, and a name contention could arise between an internal VBA object placeholder and an identical application object name. For example, an open instance of a form named Widget is identified by VBA as Form_Widget, which would generate a conflict if the database contained a form object already named Form_Widget.

If your converted application has a module and procedure with the same name, you must change your code to qualify the procedure name by placing the module name in front of it when calling it (modulename.procedurename). As a matter of good coding style, consider changing the module name in the converted application to a unique name. This will reconcile the situation without the need for qualifying the procedure name in code or changing any existing references to it.

Similarly, if a procedure name is being called from code within a form, and the form has a control with the same name as the procedure being called, an error ensues. You must change such code in the converted application to place the module name in front of the procedure name being called (modulename.procedurename), or alternately change the control name.

Make every effort to find and change the use of Access reserved words for object and code element names in your application prior to conversion. Your conversion effort will fail, or the first attempt to compile or run the converted application will fail, if the application violates any of these naming rules that follow:


Unfortunately, finding offending object names can be a trial and error process. There is no comprehensive list of Access reserved words, and the list of taboo object names exceeds the list of classes and members shown in the object browser. For example, new elements of VBA that are not even implemented in Access yet and do not show in the Object Browser are reserved words in Access 97: AddRef, AddressOf, Assert, Decimal, DefDec, Enum, Event, Friend, GetIDsOfNames, GetTypeInfo, GetTypeInfoCount, Implements, Invoke, QueryInterface, and Release.

Your converted application cannot have two controls on the same form object or report object that differ by only a space or a symbol character (such as - or _). For example, if your application form has controls Part_Num and Part-Num, rename one of them.

A table field in your application should not use these reserved DAO method names: AddNew, Cancel, CancelUpdate, Clone, Close, CopyQueryDef, Delete, Edit, FillCache, FindFirst, FindLast, FindNext, FindPrevious, GetRows, Move, MoveFirst, MoveLast, MoveNext, MovePrevious, NextRecordset, OpenRecordset, Requery, Seek, and Update. If your converted application contains one of the offending names, either change the name or make certain that any references to the field in code use the bang (!) operator as opposed to the dot, as shown here:

Set rst = dbs.OpenRecordset("SELECT Move FROM Ops")

str = rst.Move  ' Bad syntax

str = rst("Move")  ' Better syntax

str = rst!Move  ' Best syntax


Access 97 will not be able to convert an application that contains a module with the same name as an active (referenced) type library. You must rename an object in the earlier version of Access and retry conversion. For example, if you attempt to convert an application with a module named "Access," this error is generated: "Name conflicts with existing module, project, or object library."

In Access 1 applications, you could place a backquote (`) character in object names. Such names don't convert to Access 97. You must change these object names and all references to them to remove the offending character before converting the application.

Object Properties

Beginning with Access 95, each database object has a Properties collection that contains these properties: Attributes, Created, Description, Modified, Owner, Name, and Type. The properties can be set from View, Properties on the menu system or from code. You should review your converted application to determine if there is value in adding property information to the database objects.

See Chapter 12, "Understanding Form Structures and Coding," for a code listing that shows how to enumerate the Documents collection to list objects and their properties.

Project Names

During conversion, Access takes its best guess at an appropriate project name for the VBA project stored in the database (see the preceding section "Database Conversion Processes" for more details). After conversion, check that the project name assigned on the Advanced tab of the Options dialog is adequate for the application. The project name is displayed in the References dialog when a database is used as library, and in the Object Browser dialog as well, so you will want the project name to be meaningful. If your application generates this error during compilation-"Expected user-defined type, not project"-then your project name matches a list of Access reserved words that cannot serve as project names and you must change it.

Additionally, the project name can be used in the Run command syntax, so you may need to change references in converted Access 95 VBA code that use the previous project name if you change the name during conversion. See the topic "Run Method" under "Module Conversion Tasks" later in this appendix for more information.

Property Additions-Access

The following new properties were added in Access 97. You should review their functionality using the Access documentation and trickle relevant new features into your converted applications.


The Help topic "New Properties" is not accurate. Instead of Help, use the properties in this and the next section as your guide to learning new Access features. Flaws in "New Properties" include:

The listing that follows here shows properties that were added in Access 95. If you did not incorporate them into your Access 95 application prior to conversion to 97, or you are converting directly from Access 1 or 2, review the capabilities of each new property and determine whether they are appropriate for your application.

Property Additions-DAO

The following new DAO properties were added in Access 97. You should review their functionality using the Access documentation and again trickle relevant new features into your converted applications.

New DAO properties were also added in Access 95. If your Access 95 application did not take advantage of these prior to conversion, or you are converting a pre-95 application, review the following list along with the Access documentation and trickle relevant new features into your converted applications.

RunTime Argument

The /RunTime command-line argument that forces Access to emulate the runtime mode is not available in the standard retail versions of Access 97. To use this option you must purchase the Office Developer Edition version of Access. See Chapter 17, "Bulletproofing Your Application Interface," for more information on this option and the runtime version of Access.

Securing Objects

If you have been using the Access security model to protect code or other objects from modification by users, consider changing and using MDE files instead. Creating an MDE database from your application file saves its code and objects in a compiled and non-editable state. The MDE file has no source code, and no way for the user to decompile the object code, thus providing an easy way to distribute "locked" applications without using a security layer.

Menu Conversion Tasks

The next three tasks describe conversion issues related to menus and toolbars.

DoMenuItem Method

Although the layout of menus has changed in 97, your macros and code that use DoMenuItem will still run adequately. During conversion, Access 97 changes DoMenuItem calls in the application's macros to their new synonymous RunCommand actions. Review the converted macros to see these changes.

Menus and Toolbars

Custom toolbars in prior versions are converted automatically to the new command bar structure. However, custom menu bars displayed via macros are not converted, they are interpreted into command bars at run time. You can convert these manually to the new object model using the Tools, Macro menu drop-down. Select a macro in the Database window and choose Create Menu from Macro; Create Toolbar from Macro; and Create Shortcut Menu from Macro from this menu as appropriate to perform the conversion. Then delete the macro.


If you've used LNC tags or some other naming strategy in your previous application, you won't even need to open a macro to determine what it should be converted to. With good conventions, you should be able to tell a menu bar macro from a submenu macro from a shortcut macro.

The Next Procedure and Previous Procedure operations, available on the View menu and module design toolbar through Access 2, have been removed from the menu and toolbar system. If your converted application used these commands on a custom toolbar, the converted buttons will be inert in Access 97 and should be deleted.


When a toolbar button that has no replacement action in Access 97 is converted from Access 1 or 2, as in the current example, the button name of the converted button is "Happy Face!" While this is entertaining, the button is actually dead and thus the name is rather inappropriate. "Sad Face!" or "Deadhead!" might have been more accurate.

If the next and previous procedure actions were specified in a DoMenuItem action in a macro in the source application, the converted application shows RunCommand actions in their places with an empty string in the Command argument, leaving the macros inert as well.

If your application uses Windows API calls to manipulate Access menus, for example to disable a menu option, such functions can usually be changed in the converted code to use the new SetMenuItem method instead of API functions.

SendKeys

If your code used the SendKeys statement to invoke actions on the menu bar, many of these statements won't work correctly in the converted application due to changes in the menu structure. In general, you should search the application for macros and code that use SendKeys and replace the statement with RunCommand or another, more reliable alternative, as shown in the following:

SendKeys "%VO%C{Down}{Tab 4}Next Record{Enter}", True  ' The old way

Application.SetOption "Move After Enter", 2  ' The new way


Table Conversion Tasks

The following six topics describe conversion issues related to upgrading database tables.

AllowZeroLength Property

If you are converting from Access 1, which did not have the AllowZeroLength property for Text and Memo table fields, you will want to review the converted tables and determine if this property should be changed from the default value of "No."


If you have to change this property in many tables, write a small piece of code that loops through the TableDefs collection of the application and that makes the property changes automatically for each field.

Data Sources

Access 97 only works with 32-bit ODBC data sources. If your application called a 16-bit data source before conversion, you will receive a connection failure error when you try to perform an ODBC-related operation.

You must create a 32-bit data source on each workstation that uses the converted application. The data source must have the same name and properties as its 16-bit predecessor.

Indexes

Access 97 allows 32 indexes per table, but during conversion it creates indexes on the foreign key side of a relationship if such indexes do not exist, adding new indexes to a table and possibly reaching the index limit. In such a case, the conversion will fail.

You can review the structure of your application's tables and relationships prior to conversion and estimate if this problem will arise. The solution is to remove enough indexes from a table to allow it to be converted.

Internet Data Publishing

Access 97 includes a host of features for reading and writing data with the Internet or a corporate intranet in mind. Review the Help topic "What's new about working on the Internet?" for a peek at some of these enhancements.

For tables, Access supports a new Hyperlink data field type, which can contain jumps to other Office documents, Access objects, or Web sites. Access can also import, link, or export from and to HTML-based tables.

Replication

Database replication was added in Access 95 and enhanced in 97 to allow partial replication. If you are converting an older application that uses a programmatic or manual scheme to copy or synchronize data records or application objects, consider enabling replication in the application after conversion to 97. Replication enhances the following application processes:

If you are converting an Access 95 database that already uses replication, consider applying the new partial replication features only to replicate selected rows (partial replication by fields is not supported). See the Access 97 Help topic "Create Partial Replicas" for more information.

You should also consider if your users can benefit from employing Access' new Internet replication capabilities. See the "Building Applications with Microsoft Access 97" manual for complete information on advanced replication subjects.

Validation Rules

If Access finds an Access 1 validation rule it cannot convert, it stores the failure information in a table called ConvertErrors, which contains a description of the error and notes the offending table, field, and property. Always look for this table after a conversion and correct any listed problems.

An Access 1 validation rule doesn't convert if it references the following:

In Access 1-and only in that version-you could create a rule in the current database that applied to a linked table (in subsequent versions, you create the rule in the back-end database as opposed to the front-end). Such rules are dropped during conversion to 97 and must be manually re-created in the back-end database post-conversion.

Query Conversion Tasks

Jet's implementation of the SQL dialect has changed very little across Access versions, requiring little conversion effort on the part of Access or developers. The following two sections itemize the issues to be aware of when moving queries to 97.

Time Values

Jet performs time-value comparisons differently in Access 97 than in Access 1 or 2. When converting from these versions, review your application to see if any queries or SQL statements utilize time-value (not date or date/time value) comparisons against a Date/Time field in the WHERE clause. If found, test the converted queries to make certain that the values returned match the user's expectation.

Updating Multiple Tables

Multi-table queries in Access 1 were more restrictive than subsequent versions with respect to the ability to edit fields in the "one" table in a one-to-many join. If you are converting from Access 1 directly to 97, review your application to make sure any query datasheets exposed to users do not accrue problems as a result of this difference.

Form Conversion Tasks

There are several form review steps that should take place after conversion of an older application, especially based on Access 1 or 2.

ActiveX Controls

Access 2 supported only 16-bit ActiveX controls, while subsequent versions support only 32-bit controls. Before converting an application utilizing a third-party ActiveX control, contact the vendor and secure a 32-bit update to the control. Install the new version of the control on the conversion workstation and convert the application to Access 97. In most cases, the property information from the 16-bit control will convert into the 32-bit control's property stream when you first open the containing form if the updated control is registered on the machine.

If the control does not properly self-upgrade, you will need to delete the 16-bit version from the form and install the 32-bit version manually. You may also have to re-enter some property settings.

Many 32-bit ActiveX control events now require that arguments in their event code be typed as ByVal. If compilation errors arise on the event procedure declaration line of a converted ActiveX control, add the ByVal keyword before each argument in the event procedure as shown here:

Private Sub ocxChk_DblClick(ByVal Cancel As Object)



Access 97 adds this keyword as needed when new event procedures are created for 32-bit ActiveX controls.

Control Expressions

Assume that your converted application has a form control or code that contains an expression referring to another form control. If the form is opened in read-only mode and the form's recordset contains no records, Access 97 will generate an error. In previous versions of Access, a Null was returned in this instance.

Review your converted applications to see if they contain forms that can be opened as read-only with no data, and add code to prevent the empty form from opening.

Command Buttons

If your converted application contains code written by the Command Button Wizard in a prior version to launch another application, review the code to see if it needs to be modernized. You can edit the code or simply delete the button and re-create it with the Access 97 button wizard.

For example, the first two lines of code shown were generated by the Access 95 wizard, while the remainder of the listing shows the code generated by the Access 97 version. The 97 code is more flexible and powerful and is preferred, as seen in this example:

' Code written by the Access 95 Command Button Wizard is tied to a library

Const MSTB_MSEXCEL = 310&

Application.Run "utility.util_StartMSToolbarApp", MSTB_MSEXCEL

' Code written by the Access 97 wizard uses Automation

Dim oApp As Object

Set oApp = CreateObject("Excel.Application")

oApp.Visible = True

On Error Resume Next

oApp.UserControl = True


Class Modules

During conversion, if Access finds no code behind a form, it changes it to a lightweight form. A lightweight form has no code module and a HasModule property value of False. Such a form cannot be activated with the New keyword or viewed in the Object Browser. If the converted form needs to be instantiated from code or browsed, change HasModule to True, or add code to the object and Access will do this for you.

Filters

Access 97 introduced two new form events, ApplyFilter and Filter, that allow your code to intercept a user's intent or attempt to apply a filter to the form's recordset. You may want to review your converted applications to determine how users create filtered data sets, and determine if these new events might provide a better metaphor.

Format Property

Access versions 2 and newer apply the Format property of a table field to its data when displayed in form combo or list boxes. Access 1 databases did not provide this capability. When converting Access 1 applications, review the combo and list boxes in use to determine if they might benefit from the addition of a formatting property value.

Another change in the behavior of this property arises with respect to the handling of Null values and empty strings between the versions. In Access 2, the Format property had three sections, one each for the following:

Beginning with Access 95, the latter two sections of this property string were combined, which means that you cannot use the property (or the corresponding Format function) directly to display one value for a zero-length string and another value for a Null. When converting applications from Access 2, you must review your Format properties and functions and create an alternative approach to differentiating Null and empty string values. Such an alternative approach usually involves the IIf function, as shown:

IIf(IsNull(Me!txtName),"Null", Format(, "@;Empty"))


Form Toolbar

Access 97 command bars have superior capabilities to toolbars in prior versions, especially with respect to programmability and code events. Review a converted application's forms to find out if captive command buttons or embedded pseudo-toolbars on forms should now be moved to command bars.

LimitToList Property

Beginning with Access 95, when you have a form combo box's LimitToList property set to True, you can clear the combo box value (erase it) via the keyboard and exit the control without generating an error that the selected value is not on the list. When converting from Access 1 or 2, review your application to make sure that this new behavior does not allow users to enter Null values in the underlying table unexpectedly. Add validation code or set the underlying field's Required property to recoup the original behavior.

Name Property

You could place a bracket (]) character into a form control's Name property in Access 1. Such control names will convert to Access 97 and continue to function normally, but you should change them before or after conversion to remove the bracket. The character is no longer allowed in new control names and support for it will probably be abandoned in a future Access release.

Tab Control

Over the years, developers have created various workarounds to Access' lack of a tab control. If you have implemented any such workarounds, including tab-like bitmaps, toggle or option buttons, or ActiveX controls, you will be pleased with the new built-in tab control in Access. After converting your application, review its forms to determine how they might benefit from the new control.

Visible Property

In Access 1 applications, you set a control's Visible property to False to hide the control's column in a form datasheet. In Access 97, the Visible property setting does not affect datasheet view; you will want to change code in converted applications to alter the ColumnHidden property of a control instead of the Visible property when altering a datasheet's layout.

Report Conversion Tasks

Forms and reports have many structural similarities. Thus these topics in the preceding section "Form Conversion Tasks" are also appropriate to reports:

Additionally, the following two sections provide information on report conversion.

Events

Access 97 introduced two new report events, NoData and Page. You will most likely decide to use NoData in your converted applications to replace code in the Open event of reports. Many Access application developers create code that attempts to manually determine if a report contains no records and cancel the report-NoData now provides such control easily.

The new Page event fires when a report page is formatted but before it is printed. You can add code to this event to embellish or alter a report's visual attributes.

Margins

Access 97 does not allow a converted report to contain margin settings that are invalid for the default printer at the time of conversion. If you convert an Access report with one or more margins of 0, the margin values will most likely get modified during conversion to values that are allowed by the printer. Check that converted reports still display and print correctly in light of this information.

Macro Conversion Tasks

The primary conversion decision related to macros is simply whether or not to abandon them in favor of VBA code. The following section discusses this issue, and the section after it describes the conversion of macro conditions.

Converting to VBA

Macros are still supported in Access 97 and do not undergo any conversion process. However, you can convert macros to VBA code with Access' assistance, and this release may be a good time to starting thinking about converting and abandoning your macros.

To convert a form or report's macros, open the object in design view and choose Tools, Macro, Convert Form's Macros to Visual Basic. To convert a standard macro, select the macro in the database window and choose File, Save As/Export..., Save as Visual Basic Module.

Conditions

If your converted application contains a macro condition that compares two values, and each of the values returns Null, Access 1 and 2 would have returned True or False depending on the context (for example, True if the two values are compared with =, False if compared with <>). Beginning with Access 95, such comparisons return Null and must be tested with IsNull rather than comparison operators:

' Old syntax

Forms!frmInvc!txtTotal=Forms!frmInvc!txtLimit

' Two new syntax options

IsNull(Forms!frmInvc!txtTotal=Forms!frmInvc!txtLimit)

IsNull(Forms!frmInvc!txtTotal) And IsNull(Forms!frmInvc!txtLimit)


This change simply brings macro expression evaluation on par with comparisons done in code. Access 1 and 2 required the Null test, as opposed to the comparison, in Basic code.

Module Conversion Tasks

Beginning with Access 95, Visual Basic for Applications replaced Access' Embedded Basic. In the process, the metaphor for writing, compiling, and storing code changed. The impact on solution developers is mostly positive, as VBA brings with it a host of new features and syntax elements. See Chapter 11, "Expert Approaches to VBA," for details on some of the enhancements to VBA in Access 97.

AllowEditing Property

The AllowEditing property of a form was changed between Access 1/2 and 95 to these new properties: AllowAdditions, AllowDeletions, AllowEdits, and DataEntry. While the old property name is still supported, you should update your code to reflect this change.

AllowUpdating Property

The AllowUpdating property of TableDef and QueryDef objects was superseded in Access 95 by the RecordsetType property. While the old property name is still supported, you should update your code to reflect this change.

API Calls

If your converted application includes API calls to 16-bit Windows DLL files, these calls must be changed to their corresponding 32-bit versions. In many cases, the 32-bit DLL has been named the same as its 16-bit sibling, but with the character "32" added. Thus, API calls to USER.DLL now must refer to USER32.DLL.

Additionally, some API functions have new parameters or data type changes. For example, most Integer arguments to API calls were changed to expect Long values instead as part of the transition to 32-bits. You have to change both the API declaration in your code, and any variables passed to the re-typed arguments.

Some API functions now come in two matched varieties to accommodate the different string handling mechanisms of ANSI and Unicode conventions. Thus, your API DLL may now have a version with "a" at the end of its name, and a version ending with "w" as well. Change your code to use the "a" routine where possible.


The Office Developer Edition installation places files that will help you with API conversion in the ODETools folder on your system.


Automation

Two important developments in Automation can affect your converted application. The first development is that Access is exposed to other applications (and other instances of Access) as an Automation server. Thus, if your converted application uses DDE to communicate between Access and Access or from another application to Access, such code can probably be changed to use Access' Application object as the entry point for Automation services. See the examples in the section "Using the Application Object Model Enhancements" in Chapter 11, "Expert Approaches to VBA."

The other important change in Automation is the addition of references. Beginning with Access 95, your project can establish a reference to another Automation server and write code against that server that will run much quicker than in prior implementations. The performance benefit is achieved by allowing Access to communicate with the type library of the server, and thus understand its object structures.

In Access 97, the storage of references was exposed via the References collection, allowing your code to add, check, and remove references programmatically, without user interaction.

BorderLineStyle Property

The BorderLineStyle property for form controls was changed between Access 1/2 and 95 to the BackStyle property. While the old property name is still supported, you should update your code to reflect this change.

Class Modules

During conversion, if Access finds no code behind a form or report, it changes it to a lightweight form or lightweight report. A lightweight object has no code module and a HasModule property value of False, and thus is no longer a class module or class object that you can activate with the New keyword or view in the Object Browser. If the converted form or report-or a new one that you create in the application-needs to be instantiated from code or browsed, change HasModule to True or add code to the object and Access will do it for you.

The reverse is also true. If you have a converted form or report that has a small amount of code and you do not want it cluttering up the Object Browser, you can move all of its code to a standard class module or standard module. Just change HasModule to False, and call the previous code using an expression in the relevant event property of the form or report.

A third opportunity arises when your application has converted Access 95 forms without user interfaces that were being used as proto-class modules. You can move the code in such a form to a new standard class module in the Modules tab, where such objects really belong, and delete the form.

The section "Using Class Modules" in Chapter 12, "Understanding Form Structures and Coding," discusses these new objects in greater detail.

Conditional Compilation

Many developers of Access 1 and 2 applications created coding techniques to assist in debugging and performance enhancement. Some of these techniques can now be better enabled using the conditional compilation keywords #Const and #If...Then...#Else. Conditional compilation allows you to establish a flagging system that creates different personalities of code within the same procedure, each personality being enabled by a different setting of the conditional compilation constant.

See the section "Applying Conditional Compilation" in Chapter 11, "Expert Approaches to VBA," for specific examples.

Constants

Access 95 introduced a new style for naming intrinsic (built-in) constants. Constants now use a mix of upper and lower case letters and are more descriptive than their predecessors, as seen here:

A_DIALOG  ' Old style

acDialog  ' New style


Old constants are currently still supported and are listed in the Access type library under the OldConstants object. However, you should consider changing you code to reflect the new constant names to ensure that it has longevity after the older constants are abandoned in a future version of Access.

CVDate Function

Beginning in Access 95, the CVDate function was replaced with the CDate function. Although CVDate is still supported in Access 97, it is now old syntax and you should convert all references to CVDate in your converted code to CDate.

CDate returns a value of the new Date data type, whereas CVDate returned a date-type Variant. Variables in your code that are assigned the value returned by CDate should be changed to the Date data type.

DAO Changes

Several new syntax elements were added to the DAO in Access 95 and 97. Refer to the topics "Property Additions-DAO" and "Object Additions-DAO" in this section for specific details.

Many of the new syntax elements replace older elements. If your converted application continues to use the older elements, your application will be required to maintain a reference to the DAO 2.5/3.5 Compatibility Library (DAO2535.TLB) that maps from Jet 3.5 backwards to Jet 2.5. If you convert your code to use the new syntax elements, you can abandon the compatibility layer reference and use the DAO 3.5 Object Library DAO350.DLL, hence simplifying and speeding up the application.

Table D.2 below lists the syntax elements (objects, properties, and methods) that are abandoned in Jet 3.5, and shows the replacement syntax you should use when converting your code.

Table D.2 Old DAO Elements and Their Access 97 Replacement

DAO 2.5

DAO 3.5 Replacements

BeginTrans

workspace.BeginTrans

CommitTrans

workspace.CommitTrans

object.CreateDynaset

object.OpenRecordset

object.CreateSnapshot

object.OpenRecordset

database.DeleteQueryDef

querydef.Delete

Dynaset object

Dynaset-type Recordset

database.ExecuteSQL

object.Execute

DBEngine.Idle dbFreeLocks

(Remove, not needed in 97)

object.ListFields

object.Fields

table.ListIndexes

tabledef.Indexes

querydef.ListParameters

querydef.Parameters

database.ListTables

database.TableDefs

database.OpenQueryDef

database.QueryDefs

database.OpenTable

object.OpenRecordset

Rollback

workspace.Rollback

SetDefaultWorkspace

DefaultUser, DefaultPassword

SetDataAccessOption

IniPath

Snapshot object

Snapshot-type Recordset

Table object

Table-type Recordset

There are several good examples of DAO code conversion in the Help topic "Examples of Converting DAO Code."

Data Types

Access inherited the following new data types from VBA, beginning with Access 95:

Access 97 also changes the data types of certain application elements during conversion, and enforces new rules on your development with respect to data types. Until now, object properties in Access, as well as certain program structures, were "weakly typed"-stored as Variants. Now, properties for objects are "strongly typed"-assigned a specific data type-and thus require a value of the appropriate type that is non-Null. Specifically, you must:

Listing D.1 Optional Syntax Elements Shown in Brackets Here Are Now Strongly
Typed Instead of Variant Types

control.Column(column, row)

CreateControl(formname, controltype[, section[, parent _

  [, columnname[, left[, top[, width[, height]]]]]]])

CreateForm([database[, formtemplate]])

CreateReport([database[, reporttemplate]])

CreateReportControl(reportname, controltype[, section _

  [, parent[, columnname[, left[, top[, width[, height]]]]]]])

DAvg(expr, domain[, criteria])

DCount(expr, domain[, criteria])

DLookup(expr, domain[, criteria])

DMin(expr, domain[, criteria])

DMax(expr, domain[, criteria])

DStDev(expr, domain[, criteria])

DStDevP(expr, domain[, criteria])

DSum(expr, domain[, criteria])

DVar(expr, domain[, criteria])

DVarP(expr, domain[, criteria])

DoCmd.Echo echoon [, statusbartext]

form.GoToPage pagenumber[, right, down]

application.OpenCurrentDatabase dbname[, exclusive]

Application.Quit [option]
Dim varStat As Variant

varStat = Null

Application.Echo EchoOn:=True, StatusBarText:=varStat  ' Will fail

varStat = ""

Application.Echo EchoOn:=True, StatusBarText:=varStat  ' Will run


DBEngine Object

In Access 2 applications, the syntax DBEngine(0)(0) was recommended over CurrentDb to create a database variable pointing to the current database. CurrentDb has now returned as the more efficient syntax internally, because it creates another database instance for your code to work with rather than another variable pointing to the original instance. You should change the DBEngine(0)(0) references in your converted code to CurrentDb.

DefaultEditing Property

The DefaultEditing property of a form was changed between Access 1/2 and 95 to these new properties: AllowAdditions, AllowDeletions, AllowEdits, and DataEntry. While the old property name is still supported, you should update your code to reflect this change.

DoCmd Actions

DoCmd action statements from Access versions 1 and 2 are now methods of the DoCmd object. During conversion, Access 97 removes the space between the DoCmd and the action/method and inserts a dot (period), as in:

DoCmd Hourglass True  ' Before

DoCmd.Hourglass True  ' After


You should not have to perform any of these conversions manually. You will, however, need to review your code to see if any DoCmd strings are being assembled in string variables in code, and revise the code to the newer syntax.

DoMenuItem Method

During conversion, Access 97 has changed DoMenuItem actions in your application's macros to their new synonymous RunCommand methods. Code, however, has not been converted. You may want to convert DoMenuItem methods in the application to their RunCommand equivalent, as shown in the following example:

DoCmd.DoMenuItem acFormBar, acFile, acSaveForm, , acMenuVer70  ' Before

Application.RunCommand acCmdSave  ' After


Dynaset Property

This form property is still supported to refer to a form record source's Recordset object, but has been superseded by the RecordSetClone property. You should change your code to reflect the newer syntax.

Errors

Error handling code in your converted application that tests for specific error numbers should be reviewed and possibly revised:

Form/Report Section Names

In Access1/2, a number was added to each new form/report section when it was created, such as Detail0, FormHeader1, etc. Beginning with Access 95, these once-only sections are named without a control number on the name: Detail, FormHeader. If you have code that creates or modifies form or report designs and add or expects the number in the name, you will have to modify the code.

Also beginning in Access 95, your code can now reference a form or report section by using its Name property instead of using the Section(n) syntax with the section number.

Function Additions

The only new function introduced in Access 97 is HyperlinkPart, which returns a selected segment from a hyperlink address. If you are adding hyperlink functionality in your converted application, this function can prove useful.

Access 95 added several new functions. If you did not incorporate them into your Access 95 application code prior to conversion to 97, or you are converting directly from Access 1 or 2, review the capabilities of each new function and determine whether they are appropriate for the code in your application.

GetOption Method

Beginning with Access 95, if you try to use the GetOption method to retrieve the setting for First Week or First Weekday, the command will fail. Remove such code from your converted application.

INI Files

Beginning with Access 95, the Access environment is configured via Windows Registry settings as opposed to an initialization (INI) file. If your converted Access 1 or 2 application uses code to manipulate settings in the Access initialization file, this code must be changed to use the new DeleteSetting, GetSetting, and SaveSetting statements.

InsertText Method

The InsertText method was previously a method of the Application object. In Access 97, the method has been moved to the Module object. Your converted code will still run with the older reference, but you should consider changing the code to create a Module object and apply InsertText to it instead. Alternately, you may want to change the code to take advantage of the new Module object methods AddFromString or InsertLines.

Line Numbers

Access 97 does not support a line number larger than 65,529 in VBA code. If your Access 1 or 2 application uses line numbers, change any numbers larger than 65,529 to smaller numbers.

MaxButton, MinButton Properties

The MaxButton and MinButton properties of forms were combined in Access 95 to the new MinMaxButtons property. While the old property names are still supported, you should update your code to reflect this change.

Method Additions for Access

The following new Access and VBA methods were added in Access 97. You should review their functionality using the Access documentation and trickle relevant new features into the VBA code of your converted applications.


The Help topic "New Methods" indicates that DefaultControl was added in 97; in fact it was introduced in Access 95.

The following listing shows methods that were added in Access 95. If you did not incorporate them into your Access 95 application code prior to conversion to 97, or you are converting directly from Access 1 or 2, review the capabilities of each new property and determine whether they are appropriate for the code in your application.

Method Additions-DAO

The following new DAO methods were added in Access 97. You should review their functionality using the Access documentation and trickle relevant new features into the code of your converted applications.


The Help topic "New Methods" indicates that the three methods listed previously were added in 95; in fact they were introduced in Access 97.

New DAO methods were also added in Access 95. If you did not incorporate them into your Access 95 application code prior to conversion to 97, or you are converting directly from Access 1 or 2, review the following list in conjunction with the Access documentation to determine which syntax changes to make to the converted application:

Method Procedures

In Access 1/2, your code could not call a procedure in a form or report's module from outside of the object. Beginning with Access 95, you can invoke a Public procedure in a form or report module from anywhere in the application. The provides you with the opportunity to create custom method procedures for forms and reports, using them as class objects. Review you application to see if its forms or reports would benefit from this new metaphor.

MsgBox Formatting

In Access 95, the MsgBox function inserted the word Solution in bold as a header line before the text in the third substring of a message string delimited with two @ characters. This Solution string has been removed from Access 97.

The phrase before the first @ is still shown as a bold header. The string between the @ characters is still shown as a non-bold string separated from the first string by a blank line. The final segment is now displayed after another blank line. You may want to edit your converted MsgBox strings to now include the word Solution, or you may choose to create a custom form for displaying messages instead.

Named Arguments

Beginning with Access 95, arguments passed to your procedures, as well as Access and VBA functions, can be identified with an argument name. This allows arguments to be passed in non-positionally, as in the following example:

' Partial syntax diagram for OpenForm

DoCmd.OpenForm formname [, view] [, filtername] [, wherecondition] _

  [, datamode]

' A standard converted OpenForm call

DoCmd.OpenForm "frmCust", , , , acAdd

' OpenForm with named arguments

DoCmd.OpenForm DataMode:=acAdd, FormName:="frmCust"


If you feel that your code would be more readable by converting it to show argument names, you can do so. Such changes are cosmetic and do not affect application performance.

Object Additions-Access

The following new objects and collections were added in Access 97. You should review their functionality using the Access documentation and trickle the new objects into your converted applications as needed.

These objects and collections were added to Access 95:

Object Additions-DAO

Two new DAO objects were introduced since Access 2. The Errors collection was added to Access 95, while the Connection object appeared in Access 97. If your converted application does not already take advantage of them, review the capabilities of each one in the Access documentation to determine how to apply the new objects to your converted application.

Object Additions-VBA

Each of the following new VBA objects was introduced in Access 95. If your converted application does not already take advantage of them, review the capabilities of each one in the Access documentation to determine how to apply the new objects to your converted application.

OpenForm Method

Beginning with Access 95, you can open forms via a VBA code reference by creating a New object variable for the form. This allows your code to create more than one active instance of a single form. Review the OpenForm method calls in your converted application's code to see if they would benefit from a conversion to the newer syntax as follows:

' The converted code

Dim frmInst As Form

DoCmd.OpenForm "frmInst"

frmInst = Forms!frmInst

' The newer approach

Dim frmInst As New Form_frmInst

frmInst.Visible = True


See Chapter 12, "Understanding Form Structures and Coding," for examples of class forms and multi-instance forms.

Optional Arguments

Beginning with Access 95, arguments to procedures can be declared as Optional. Such a parameter does not need to be passed to the destination function. You can review your converted Access 1 and 2 code to see if it can be "cleaned up" to take advantage of this feature. Using optional arguments means that procedures with infrequently used arguments do not need to be passed a stub value in those argument positions.

Optional arguments were further enhanced in Access 97 to allow for data types other than Variant, and to allow the declaration to specify a default value. The optional argument assumes the default value if none is passed in.

Function InvcSum(rintInvcNum As Integer, Optional rcurLimit _

  As Currency = 10000) As Currency


Having optional arguments with default values and explicit data types makes your code both cleaner and faster. Use of this feature should be trickled into your converted Access 1 through 95 code.

ParamArray Arguments

Beginning in Access 95, the ParamArray keyword allowed you to pass a variable number of arguments to a procedure. Check your converted Access 1/2 code to see if this syntax can replace the use of some user-defined types or multi-argument procedure declarations.

Reserved Words

Access 97 VBA has several new reserved words that cannot appear as identifiers in your code. They are as follows:

If your application contains these or other disallowed reserved words as object or variable names, they will be identified as compile errors when you compile the new application, and you will need to change the offending names.

Run Method

During conversion, Access creates a ProjectName property for the VBA project stored in the database. This name must be used in Run method statements that previously prefixed a procedure name with its host database name:

Application.Run(databasename.function)  ' Old syntax

Application.Run(projectname.function)  ' New syntax


You must change these references manually in your converted code. If your converted database began in Access 95, its 97 project name is very likely the same as its 95 filename, and no code changes will be required. For converted Access 1/2 applications, the 97 project name by default will not match the original filename and the conversion noted here may be required.

Server Data

If your application performs programmatic operations against server tables that are linked into the application database, consider upgrading the application to take advantage of Access 97's new ODBCDirect technology. With ODBCDirect, you can send commands directly to the server through ODBC but without any assistance or interference from Jet. You may want to recode specific query and recordset operations to take advantage of ODBCDirect; read Chapter 19, "Exploring Client/Server Issues" to decide.

SetOption Method

Beginning with Access 95, if you try to use the SetOption method to update the setting for First Week or First Weekday, the command will fail. Remove such code from your converted application.

ShowGrid Property

The ShowGrid property of TableDef and QueryDef objects has been superseded by the DatasheetGridlinesBehavior property. While the old property name is still supported, you should update your code to reflect this change.

ShowToolbar Method

In past versions of Access, if a menu or toolbar was invoked from a library database routine with ShowToolbar and the named toolbar was not found in the library, Access also looked in the current database for the object. In Access 97, this functionality is broken; a library database cannot do a ShowToolbar method on a command bar outside of a library database. You may need to code a workaround into your application.

Statement Additions

Each of the following new statements was introduced in Access 95. If your converted application does not already take advantage of them, review the capabilities of each one in the Access documentation to determine if the application should be modified.

TransferSpreadsheet Method

Some older versions of Excel and Lotus 1-2-3 spreadsheets cannot be imported into Access 97. Check your converted application for a TransferSpreadsheet method with a SpreadsheetType argument value of 1 and update the argument setting and the target import files to a newer version of the creating product.

Access 97 does not support an SQL statement in the TableName argument of this method. If your converted code contains such a string, save the SQL statement into a select query and use the query name in the TableName argument.

TransferText Method

Access 97 does not support an SQL statement in the TableName argument of this method. If your converted code contains such a string, save the SQL statement into a select query and use the query name in the TableName argument.


1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.