MS Access 97 Conversion White Paper
| Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. |
On This Page
Overview
16- and 32-bit ActiveX Controls (OLE Custom Controls)
Replicated Databases
Secured Databases
16- and 32-Bit API Calls
Microsoft Access Library Database References
Object or Type Library References
Microsoft Access Intrinsic Constants
Microsoft Office for Windows 95 Automation Code
Wizard-Generated Code
DoCmd Statement
SendKeys Statement and Macro Action
ObjectName.FieldName Legacy Syntax
Conflicts with Reserved Words
Standard Module/Class Module Name Conflicts
Module Name/Procedure Name Conflicts
New Error Codes in Microsoft Access 97
Code with Line-Continuation Characters
Macro-Defined System Menus
Conversion Fails Without Default Printer
Report Margins Adjust to Printable Region
Forms or Reports with Large Numbers of Controls
Databases with Large Numbers of Modules
Converting Add-In Databases
Tables with Large Numbers of Indexes or Relationships
Converted Toolbars Lose Button Images
Database with Large Number of Objects Increases in Size
dbFailOnError Behavior in Microsoft Access 97
INFPath Registry Setting for Xbase ISAM Drivers
MaxLocksPerFile Setting Restricts Transaction Size
ForceNewPage Property in Group Section
Queries with Date Criteria Between 1900 and 1929
16-Bit ODBC Data Sources
Overview
When you open a database in Microsoft Access 97 that you created in Microsoft Access 1.x, 2.0, or 7.0, in most cases, you want to convert that database to Microsoft Access 97. Although you can enable a database from an earlier version without converting it, you cannot use Microsoft Access 97 to change the design of any objects in that database, nor can you take advantage of the many new features in Microsoft Access 97 until you convert the database.
Note: Once you convert a database to Microsoft Access 97, you cannot open that database in earlier versions of Microsoft Access, and you cannot convert it back. You must carefully consider when to convert your database, especially if you share it with multiple users who are not all able to upgrade to Microsoft Access 97 at the same time. Always make a backup of your database before you convert it.
Most earlier-version Microsoft Access databases convert to Microsoft Access 97 with no difficulty. In some rare cases, however, new features can conflict with existing objects and code in the converted database. This white paper summarizes issues you may encounter when you convert your database. For supporting information about database conversion issues, search the Microsoft Access 97 Help Index for "converting databases," or ask the Microsoft Access 97 Office Assistant. You can also find information about conversion issues in the Acread80.wri file in the Microsoft Access program folder on your computer.
16- and 32-bit ActiveX Controls (OLE Custom Controls)
When you convert a database that uses 16-bit ActiveX controls designed for Microsoft Access 2.0, Microsoft Access 97 does not convert those controls to their new 32-bit equivalents. You must manually insert an equivalent 32-bit ActiveX control and recreate its event procedures after you convert the database.
When you convert a database that contains 32-bit ActiveX controls designed for Microsoft Access 7.0, those controls convert to Microsoft Access 97 on a computer that has the Microsoft Access 7.0 controls properly registered. For example, the Microsoft Access 7.0 Calendar control on a form is replaced by the Microsoft Access 97 Calendar control during conversion.
The 32-bit Data Outline control included with Microsoft Access Developer's Toolkit for Windows 95 is not updated to a new version when you convert your database. A Microsoft Access 97 version of the Data Outline control will be available for download from Microsoft's Internet site after Microsoft Office 97 Developer Edition is released.
Replicated Databases
When you convert a Microsoft Access 7.0 Design Master database, it triggers conversion of the other databases in the replica set. When you synchronize a replica with the converted Design Master, the replica is automatically converted to Microsoft Access 97.
You cannot mix database versions in a set of replicated databases, so be sure all replica users upgrade to Microsoft Access 97 before you convert the Design Master. It is possible to enable a replicated Microsoft Access 7.0 database to use in Microsoft Access 97; however, the replica set remains unconverted, and you must use Microsoft Access 7.0 to synchronize members of the replica set.
Microsoft Access 97 Help documents a method to convert a replica set from Microsoft Access 7.0 and describes a procedure to test a temporary replica set before you convert the original.
Secured Databases
Converting a secured Microsoft Access 2.0 or 7.0 database in Microsoft Access 97 requires additional steps and consideration. Microsoft Access 97 Help provides detailed information on this topic.
Multiple versions of Microsoft Access can share the same workgroup information file (System.mda or System.mdw) as long as the workgroup information database remains in the lowest version format.
Converting a Microsoft Access 7.0 database that only uses a database password is a simple process. When you convert the database, you must type the database password twice. After you convert the database, the database password works exactly as it did in Microsoft Access 7.0.
16- and 32-Bit API Calls
If your Microsoft Access 1.x or 2.0 database uses Windows application programming interface (API) procedures, you must manually update them to their 32-bit API equivalents after you convert the database.
You can find references to the correct syntax for most 32-bit API calls in the Win32api.txt file or the Windows API Viewer included with Microsoft Access Developer's Toolkit for Windows 95 and Microsoft Office 97, Developer Edition.
Converting a Microsoft Access 7.0 database that already uses 32-bit API calls should not present any conversion issues in Microsoft Access 97.
Microsoft Access Library Database References
If your database uses add-ins or library databases created in an earlier version of Microsoft Access, you must convert them in order to use them.
In Microsoft Access 1.x and 2.0, you can create circular library references. For example, you can create a reference from Library A to Library B, and a reference from Library B to Library A. Circular references are not allowed in Microsoft Access 7.0 and 97.
Object or Type Library References
Converting a database with references to object libraries or type libraries retains those references as long as the referenced libraries exist on the computer when the database is converted.
Microsoft Access default references -- Visual Basic® for Applications, Microsoft Access for Windows 95, Microsoft Jet SQL Help Topics, and Microsoft DAO 2.5/3.0 or Microsoft DAO 3.0 Library -- are automatically converted to their Microsoft Access 97 equivalents when you convert a database, as are most references to Microsoft Office 95 component libraries. For example, a reference to the Microsoft Graph 5.0 Object Library, Gren50.olb, is automatically updated to the Microsoft Graph 97 Object Library, Graph8.olb, when you convert your database.
The Microsoft Jet SQL Topics reference created when you install the Microsoft Access Developer's Toolkit for Windows 95 is automatically removed during conversion if the library is not found on your hard drive. If any other referenced library file (.olb or .tlb file) is not on your computer hard drive when you convert a database, compilation errors may occur during conversion because of the missing references.
Microsoft Access Intrinsic Constants
Intrinsic constants in Microsoft Access 7.0 and 97 are a mix of lowercase and uppercase letters, and parts of the constant are concatenated rather than separated by underscore characters. For example, the constant A_NORMAL in Microsoft Access 2.0 is acNormal in Microsoft Access 7.0 and 97.
Intrinsic constants in databases created with earlier versions of Microsoft Access will not automatically be converted to the new constant format, but old constants will continue to work without errors. However, it is recommended that you use the new format whenever you write new code.
Microsoft Office for Windows 95 Automation Code
Your Microsoft Office for Windows 95 OLE Automation code, now called simply Automation code, may require modification after you convert your database if you use code that references a specific product version. For example, the following line of code refers to Microsoft Excel version 5.0
Set xlObject = CreateObject("Excel.Application.5")
whereas the following is a generic reference to Microsoft Excel:
Set xlObject = CreateObject("Excel.Application")
Most of the Automation code samples for Microsoft Access in the Microsoft Knowledge Base use generic object references, as does all of the code in "Your Un-Official Guide to using OLE Automation with Microsoft Office and Microsoft BackOffice," a booklet included with Microsoft Office Professional for Windows 95.
Because Microsoft Word 97 uses the Visual Basic for Applications object model instead of the WordBasic command model used in earlier versions, you may want to modify Microsoft Word Automation code to take advantage of Visual Basic for Applications features. Microsoft Word 97 still provides WordBasic compatibility, so Automation code written for earlier versions of Microsoft Word will work properly, but converting Automation code to use the new Visual Basic for Applications model is recommended.
Wizard-Generated Code
Access Basic or Visual Basic code in your database that was generated by a Microsoft Access Wizard may need to be modified when you convert your database. Some of the code created by wizards in Microsoft Access 2.0 and 7.0 references specific functions in the Utility.mda or Wzmain70.mda library databases. For example, if you created a database in Microsoft Access 7.0 using the Database Wizard and one of the database templates, the wizard generated the following code to invoke the SwitchBoard Manager from the SwitchBoard form:
Application.Run "WZMAIN70.sbm_Entry"
This code fails in Microsoft Access 97 because the new wizard file name is WZMain80.mde. In Microsoft Access 97 the code is:
Application.Run "WZMAIN80.sbm_Entry"
The Command Button Wizard in Microsoft Access 2.0 and 7.0 generates some code that does not work in Microsoft Access 97, specifically any command buttons that shell to Microsoft Office applications. Remove these buttons in your converted database and recreate them using the Microsoft Access 97 Command Button Wizard.
DoCmd Statement
The DoCmd statement in Microsoft Access 2.0 becomes the DoCmd object in Microsoft Access 7.0 and 97, which requires slightly different syntax. The following Access Basic code in Microsoft Access 2.0
DoCmd OpenForm "MyForm"
becomes the following Visual Basic method of the DoCmd object in Microsoft Access 7.0 and 97:
DoCmd.OpenForm "MyForm"
In some cases, Microsoft Access 97 may fail to convert Microsoft Access 2.0 DoCmd<space>Action to the proper DoCmd<dot>Method syntax used in Microsoft Access 7.0 and 97. When that happens, you receive code compilation error messages in the converted database, which are easily corrected by removing the space and adding the dot (.) in the DoCmd portion of your code.
Note DoCmd DoMenuItem (or DoCmd.DoMenuItem in version 7.0) actions in code are not automatically converted to the Microsoft Access 97 RunCommand syntax, but the DoMenuItem syntax works correctly in Microsoft Access 97.
However, DoMenuItem macro actions are automatically converted to their equivalent RunCommand actions when you open a macro in Design view. A few DoMenuItem macro actions do not have equivalent RunCommand actions in Microsoft Access 97. Those actions are documented in Microsoft Access 97 Help.
SendKeys Statement and Macro Action
Because some menu items and the structure of the menu tree is different in Microsoft Access than in earlier versions, your SendKeys statements in code or SendKeys actions in a macro may fail or may not produce the desired action. Locate the new menu items in Microsoft Access 97 and modify your SendKeys code or macros to send the correct keys. New features in Microsoft Access 97 may make some of your old SendKeys actions obsolete or unnecessary.
ObjectName.FieldName Legacy Syntax
If you convert a Microsoft Access 2.0 database that uses ObjectName.FieldName data access objects (DAO) syntax in Access Basic, the database converts properly. However, in order to maintain backward compatibility with your DAO syntax, the converted database contains a reference to the Microsoft DAO 2.5/3.5 Compatibility Library instead of the new Microsoft DAO 3.5 Object Library.
For example, the following code is acceptable in Microsoft Access 2.0:
Dim db As Database
Dim rs As Recordset
Set db = CurrentDB()
Set rs = db.OpenRecordset("Customers")
Debug.Print rs.[Customer ID]
The same code in Microsoft Access 7.0 and 97 requires an exclamation point (!) in front of the [Customer ID] field:
Dim db As Database
Dim rs As Recordset
Set db = CurrentDB()
Set rs = db.OpenRecordset("Customers")
Debug.Print rs![CustomerID]
If you change the reference in your database from Microsoft DAO 2.5/3.5 Compatibility Library to the Microsoft DAO 3.5 Object Library, you must also update any ObjectName.FieldName syntax to ObjectName!FieldName syntax in your code or compilation errors will occur.
Conflicts with Reserved Words
In Microsoft Access 97, module names cannot be any of the following reserved words:
Application
Assistant
CommandBars
DoCmd
Forms
Modules
References
Reports
Screen
If you attempt to convert a Microsoft Access 2.0 or 7.0 database containing a module that has one of these names, the conversion fails with a message indicating that the module name is invalid. To correct the error, rename the module and convert the database again.
Standard Module/Class Module Name Conflicts
In Microsoft Access 7.0 and 97, form and report class modules share the same name space with standard code modules. For example, if you have a form named Form1, Visual Basic for Applications creates a class module named Form_Form1, which means you cannot have a standard code module named Form_Form1.
If you attempt to convert a Microsoft Access 2.0 database with a standard code module name that conflicts with a class module name, the conversion fails with a message indicating that the module name is invalid. To correct the error, rename the standard module and convert the database again.
Module Name/Procedure Name Conflicts
If your Microsoft Access 2.0 database contains a procedure and a module with the same name, the procedure fails in Microsoft Access 7.0 and 97 with the following error:
Expected variable or procedure, not module
You can resolve this error in one of two ways. You can change the name of the module so it does not conflict with any procedure names, or you can make changes in your converted database to call the procedure using ModuleName.ProcedureName syntax.
New Error Codes in Microsoft Access 97
Some error codes have changed in Microsoft Access 97, and this may have an impact on a converted database that uses extensive error trapping. For example, when you reference the PaletteSource property of a text box that does not exist, Microsoft Access 7.0 returns the error message
2455
Application-defined or object-defined error
whereas Microsoft Access 97 returns the error message:
438
Object doesn't support this property or method.
Therefore, error trapping routines that check for specific error codes must be tested in converted databases to ensure that the error codes have not changed.
Also, certain actions in Microsoft Access 7.0 code do not generate a run-time error, but they do in Microsoft Access 97. For example, if your code sets the value of a form control's Tag property to Null, no error occurs in Microsoft Access 7.0, but Microsoft Access 97 generates the following error message because of more stringent control property type checking:
Run-time error 13
Type Mismatch
This issue is documented in the Acreadme80.wri file in your Microsoft Access 97 program folder.
Following are some examples of error code changes:
Process: Attempt to Read Non-Existent Control Property
Code Sample: Debug.Print Forms!Form1!Text0.Bogus
Microsoft Access 7.0 error:
2455
Application-defined or object-defined error
Microsoft Access 97 error:
438
Object doesn't support this property or method
Process: Attempt to Set Control Long Property to Null Value
Code Sample: Forms!Form1!Text0.BorderColor = Null
Microsoft Access 7.0 error:
(No error occurs)
Microsoft Access 97 error:
13
Type mismatch
Process: Attempt to Set Control Text Property to Null Value
Code Sample: Forms!Form1!Text0.Tag = Null
Microsoft Access 7.0 error:
(No error occurs)
Microsoft Access 97 error:
13
Type mismatch
Process: Attempt to Set Value to Non-Existent Control Property
Code Sample: Forms!Form1!Text0.Bogus = "Hello"
Microsoft Access 7.0 error:
2455
Application-defined or object-defined error
Microsoft Access 97 error:
438
Object doesn't support this property or method
Process: Attempt to Set Control's Left Property to OverFlow Value in Form View
Code Sample: Forms!Form1!Text0.Left = 1239992139
Microsoft Access 7.0 error:
2113
The value you entered isn't valid for the DataType or FieldSizeU
Microsoft Access 97 error:
6
Overflow
Code with Line-Continuation Characters
In rare cases, Microsoft Access 7.0 Visual Basic code that uses an underline (_) as a line-continuation character may not compile properly after it is converted. This is because of changes in the module window line-continuation algorithm in Microsoft Access 97.
Any code that does not convert properly because of misplaced line-continuation characters causes a compilation error. The error is easily corrected by removing or relocating the line-continuation character in your code*.* This information is documented in the Acread80.wri file in your Microsoft Access 97 program folder.
Macro-Defined System Menus
In Microsoft Access 2.0 and 7.0, all user-defined menus are created using macros to define the menu bars and menu items. In Microsoft Access 97, menu bars, toolbars, and shortcut menus are all part of the CommandBars collection of objects in Visual Basic for Applications.
When you convert a database that contains macro-defined custom menus, the menus work as they did before, but the conversion process does not automatically convert your macro-defined menus to the CommandBar object model. To convert a macro to a command bar after you convert your database, select the macro in the Database window, and then click Macro on the Tools menu. Then click Create Menu from Macro, Create Toolbar from Macro, or Create Shortcut Menu from Macro.
Conversion Fails Without Default Printer
If you attempt to convert a Microsoft Access 2.0 database that contains form or report objects, and your computer does not have a default printer installed, you receive the following error message, and the database fails to convert:
You must set a default printer before you design, print, or preview.
Note that this is not an issue when you convert a Microsoft Access 7.0 database. To resolve the error, set up a default printer on your computer and convert the database again.
Report Margins Adjust to Printable Region
When you convert a Microsoft Access 2.0 database to Microsoft Access 7.0 or 97, all print margins are modified to remain within the default unprintable region defined by the printer driver on your computer.
If you have a report in a Microsoft Access 2.0 database that has its top, bottom, left, or right margins set to zero inches, after you convert the database, you may see those margins changed to the minimum default margin defined by your printer driver.
Forms or Reports with Large Numbers of Controls
When you convert a Microsoft Access 2.0 or 7.0 database that contains a form or report with a large number of controls, the conversion may fail with the following error message:
The form or report 'ObjectName' has too many controls.
You must reduce the number of controls on the form or report and then convert the database again. The limit on the number of controls is imposed by Visual Basic for Applications v-table binding, which limits the total number of controls plus fields plus sections on a form or report to 754. This limit does not exist in Microsoft Access 2.0 or 7.0.
Databases with Large Numbers of Modules
When you convert a Microsoft Access 2.0 database with a large number of forms, reports, and modules, conversion may fail with one of the following error messages:
can't create any more class modules
-or-
Out of Memory
Both Microsoft Access 7.0 and 97 have a limit of 1024 Visual Basic for Applications modules. The use of lightweight forms and reports in Microsoft Access 97 greatly reduces the impact of this limitation.
If your database fails to convert because it has too many objects, reduce the number of objects in your database. Consider dividing your application into multiple databases or using library databases to store your code.
Converting Add-In Databases
When a Microsoft Access 2.0 Add-In database is converted to Microsoft Access 7.0 or 97, and then you attempt to activate the add-in using the Add-In Manager, you may receive the following error message:
The add-in could not be installed because it is missing a USysRegInfo table.
For information about creating a USysRegInfo table, see Chapter 17, "Creating Wizards, Builders, and Menu Add-ins," in the "Building Applications with Microsoft Access 97" user guide, or see article 153858 in the Microsoft Knowledge Base.
The Internet Assistant 95 Add-In will not install as an add-in if you convert it to Microsoft Access 97; however, in Microsoft Access 97, most of the functionality of the Internet Assistant is built into the product.
Tables with Large Numbers of Indexes or Relationships
In Microsoft Access 7.0 and 97, a single table cannot have more than 32 indexes and direct relationships combined. In Microsoft Access 2.0, a table can have up to 32 indexes and a virtually unlimited number of relationships.
When you convert a Microsoft Access 2.0 database containing a table with more than 32 combined indexes and relationships, conversion fails with a message indicating that the limit was exceeded. You must reduce the number of indexes or relationships and then convert the database again.
Converted Toolbars Lose Button Images
After you convert a database in Microsoft Access 97, some custom toolbar buttons may appear blank because there is no equivalent image in the new version. The Exit door in Microsoft Access 7.0 is one example of an image that has changed. You can replace the missing image with one that is available in Microsoft Access 97, or you can use an icon editor to recreate the old button image to use in your database.
On a form, command button images do not change because they are stored in the Picture property of the control.
Database with Large Number of Objects Increases in Size
Microsoft Access 7.0 and 97 databases store form, report, and standard module objects differently than earlier versions. When you convert a Microsoft Access 2.0 database with a large number of forms, reports, or modules, the Microsoft Access 97 database may increase dramatically in size.
Microsoft Access 97 introduces lightweight objects, which are forms or reports that do not have class modules associated with them. If a form or report in your database does not contain any code, Microsoft Access 97 changes it to a lightweight object when you convert the database. This helps to minimize the increase in size of a converted database.
You can also reduce the size of your database by converting it to an MDE file, which is a database from which all source code has been removed, and only compiled executable code remains.
dbFailOnError Behavior in Microsoft Access 97
Using the dbFailOnError option in Microsoft Access 7.0 (or DB_FAILONERROR in version 2.0) with the Execute method of a QueryDef object automatically places an action query inside a transaction. This causes the entire operation to rollback if any errors occur during the processing of the query.
In Microsoft Access 97, the dbFailOnError option does not automatically place the action query in a transaction, so all changes up to the failed record will not rollback automatically if the query fails. You must review all code that uses the Execute method with the dbFailOnError option in Microsoft Access 97, and use explicit transactions if necessary. More information is available in the Acread80.wri file in your Microsoft Access 97 program folder.
INFPath Registry Setting for Xbase ISAM Drivers
When you link (attach) to dBASE® or Microsoft FoxPro® tables, Microsoft Access looks for, and if necessary creates, an INF file in the same folder as the linked table. If the table is located on read-only media, then Microsoft Access uses the alternate path specified in the INFPath registry setting as the location to create the INF file.
Microsoft Access 7.0 and 97 do not use the INFPath setting as they should. You can work around this problem by including the proper INF file when you distribute your database on read-only media so Microsoft Access does not have to create one.
MaxLocksPerFile Setting Restricts Transaction Size
In Microsoft Access 97, action queries can be restricted by a MaxLocksPerFile setting in the Windows registry, which limits the number of page locks allowed in a transaction. If the number of page locks in a transaction attempts to exceed the MaxLocksPerFile value, then the action is split into two or more transactions, and the completed portion of the query is committed.
This setting was added to Microsoft Access 97 to prevent Novell® NetWare® 3.1 server crashes when the specified NetWare lock limit is exceeded, and to improve performance with both Novell NetWare and Microsoft Windows NT® networks. The default MaxLocksPerFile setting is 9500, but you can dynamically configure this setting in code using the DBEngine.SetOption method, so you are not forced to change registry settings in order to run large action queries within transactions.
If you exceed the MaxLocksPerFile setting when you are running an action query, you may receive the following error message:
There isn't enough disk space or memory to undo the data changes this action query is about to take.
The message gives you the ability to continue with the action query, but you will not be able to undo the action.
ForceNewPage Property in Group Section
In Microsoft Access 2.0 and 7.0, if you set the ForceNewPage property to Before Section in a Group Header section on a report, the setting does not force a page break before the first group section.
In Microsoft Access 97, setting the ForceNewPage property to Before Section forces a page break before every section, including the first group section encountered in the report.
Queries with Date Criteria Between 1900 and 1929
If you use a literal date specifying the years 1900 through 1929 as part of your criteria in a Microsoft Access 2.0 or 7.0 query, the query may return different results after you convert the database to Microsoft Access 97. This happens because new interpretation of year dates ending in the digits 00 through 29 places them in the next century.
For example, a literal date criteria of #01/01/15# in Microsoft Access 2.0 and 7.0 represents January 1, 1915; the same date criteria in Microsoft Access 97 represents January 1, 2015. To work around this difference, modify the data in your criteria to specify the century, for example #01/01/1915#.
16-Bit ODBC Data Sources
If a table in your Microsoft Access 1.x or 2.0 database uses a 16-bit driver to link to an ODBC data source, you may receive the following error message when you open the table, or when you open a form or report based on that table:
ODBC - connection to <DataSourceName> failed
To resolve this error, create an identically named data source using a 32-bit version of the ODBC driver.