MicrosoftÒ Access Version 2000
Microsoft Access 2000 Conversion
Microsoft Product Support Services White Paper
Written by Darla Hogenson
Published on June 1999
Abstract
Most Microsoft Access databases will convert to Microsoft Access 2000 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 Office Assistant for "converting databases."
© 1999 Microsoft Corporation. All rights reserved.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.
Microsoft is a registered trademark of Microsoft Corporation.
Other product or company names mentioned herein may be the trademarks of their respective owners.
Microsoft Corporation • One Microsoft Way • Redmond, WA 98052-6399 • USA
0X97 Part no. 098-XXXXX
Contents
Microsoft Access 2000 Conversion 1
Microsoft Product Support Services White Paper 1
Contents 3
Overview 1
Supported Access File Formats 1
Enabling Prior Versions of a Database 1
Front-End/Back-End applications 2
Converting a Database 3
Permissions Needed to Convert a Secured Database 3
.MDB and .ADP Files 3
Converting Microsoft Access 97 Databases 4
DAO 2.5/3.5-Compatibility Library 4
DAO vs. ADO 5
Converting Databases with Large Numbers of Code and/or Class Modules 5
Converting Databases from Previous Localized Versions of Access 5
Name AutoCorrect Doesn't Work with Converted Databases 7
Converting Secured Databases 7
Converting Add-ins and Library Databases 8
Converting Replica Sets 8
“The Visual Basic for Applications project in the database is corrupt”–Error Message 9
.MDE Files 9
Intrinsic Constants 9
Converting Source Control Databases 9
SendKeys Statement and Macro Action 10
Converting Microsoft Access 95 Databases 11
DAO 2.5/3.0-Compatibility Library 11
DAO vs. ADO 12
Converting Databases with Large Numbers of Code and/or Class Modules 12
Converting Databases from Previous Localized Versions of Access 13
Name AutoCorrect Doesn't Work with Converted Databases 14
Access 95 and Y2K 14
Converting Forms and Reports with Large Numbers of Controls 14
Converting Secured Databases 15
Converting Add-ins and Library Databases 15
Converting Replica Sets 15
DoCmd Statement 16
SendKeys Statement and Macro Action 16
Intrinsic Constants 16
Conflicts with Reserved Words 16
Converting Microsoft Access 2.0 Databases 18
DAO 2.5/3.x-Compatibility Library 18
DAO vs. ADO 19
Converting Databases with Large Numbers of Code and/or Class Modules 19
Converting Databases from Previous Localized Versions of Access 19
Name AutoCorrect Doesn't Work with Converted Databases 20
Access 2.0 and Y2K 21
Conversion of a 2.0 Database Adjusts Margins of Reports to Fit Default Unprintable Region 21
Converting Forms and Reports with Large Numbers of Controls 21
Converting Secured Databases 21
Converting Add-ins and Library Databases 22
16- and 32-Bit ActiveX Controls (OLE Custom Controls) 22
16- and 32-Bit API Calls 23
16-Bit ODBC Data Sources 23
Tables with more than 32 Indexes 23
DoCmd Statement 23
SendKeys Statement and Macro Action 24
TransferSpreadsheet Action 24
The TransferText Actions 24
Comparisons Involving Null Values 24
Intrinsic Constants 24
Conflicts with Reserved Words 25
Standard Module/Class Module Name Conflicts 25
Module Name/Procedure Name Conflicts 25
Issues Encountered when Saving as a Prior Version 26
User Defined Properties Are Not Saved When Converted to Previous Version of Access 26
Import Specifications Not Saved When Converted DB Converted to Previous Version 26
Save as 97 Creates an Unsecured 97 Database 26
Missing References After Converting an Access 2000 Database to Access 97 26
Memo Field Indexing 26
Replicated Databases 26
Access 2000 System.mdw Cannot Be Saved as a Previous Version of Microsoft Access. 27
Overview
When you open a database in Microsoft Access 2000 that you created in Microsoft Access 1.x, 2.0, 95 (7.0), or 97, in most cases, you want to convert that database to the current version. Although you can enable a database from an earlier version without converting it, you cannot use Microsoft Access 2000 to change the design of any objects in that database, nor can you take advantage of the many new features in Microsoft Access 2000 until you convert the database.
Caution Once you convert a database to Microsoft Access 2000, you cannot open that database in earlier versions of Microsoft Access. The only exception is that Microsoft Access 2000 does allow you to convert a 2000 database back to Microsoft Access 97 format. If you plan to share your database with multiple users who are not all able to upgrade to Microsoft Access 2000 at the same time, you cannot convert the database; you will have to enable it. Always make a backup of your database before you convert it.
Most Microsoft Access databases will convert to Microsoft Access 2000 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 Office Assistant for "converting databases."
Supported Access File Formats
Access 2000 can convert or enable databases originally created with the following versions of Access:
· Microsoft Access 2.0
· Microsoft Access 95
· Microsoft Access 97
Note Files created with Access 1.0 or 1.1 are not supported. You will have to convert these databases into one of the supported formats.
Enabling Prior Versions of a Database
In a multi-user environment where not all users can upgrade to Microsoft Access 2000, you may want to enable a database rather than convert the database to the new database format. When a database is enabled, Microsoft Access maintains the original formatting of the database. All database objects can be viewed in Microsoft Access 2000. However, none of the objects can be modified, nor can additional objects be added to the database. The data stored in the tables can be modified.
If you want to modify or add additional objects to the database, you must open the database with the version of Microsoft Access used to create it.
To open a prior-version Access database without converting it, follow these steps:
1. Make a backup copy of your database
2. On the File menu, click Open.
3. Click the previous-version Access database that you want to enable and click Open.
4. In the Convert/Open Database dialog box, click Open Database.
After you enable a database, you may encounter the following issues. New style of toolbars and menu bars
Access 2000 supports a new style of toolbars and menu bars. When you enable a database, custom toolbars are converted to the new style, but the converted toolbars and menus are not saved. Custom menu bars are interpreted as the new style menu bar, but the menu bar macros are not converted and continue to be supported.
Enabling a secured Access database
If your Access database is secured, you should use the Workgroup Administrator to join the previous-version workgroup database information file for the secured database. Access 2000 can use a workgroup information file that was created in a previous version; however, a previous version cannot use an Access 2000 workgroup information file.
Size of an enabled Access database
Your Access database may increase in size when you enable it because the Visual Basic® for Applications project must store information in the format of each version. In other words, if you multi-enable a database, the Visual Basic for Applications project is stored multiple times. This will cause the database to increase in size.
Front-End/Back-End applications
Besides enabling an older version of a database, you may opt to create a front-end/back-end application. By maintaining the data–or back-end database–in the oldest version of Microsoft Access, you can share data between multiple versions. Each version of Microsoft Access can have its own front-end application where objects can be changed and updated.
If you have a front-end/back-end database already created, all you have to convert is the front-end database. You will want to leave the back-end in its native format.
To convert a Front-end/Back-end database
1. Leave the back-end database alone.
2. Convert the front-end database.
3. Run the Linked Table Manager to reestablish the links to the back-end database. You can use this tool by clicking the Tools menu, pointing to Database Utilities, and then clicking Linked Table Manager.
Converting a Database
Permissions Needed to Convert a Secured Database
The user account you use to log on during conversion MUST have the following permissions:
· Open/Run and Open Exclusive permissions for the database itself.
· Modify Design or Administer permissions for all tables in the database, or you must be the owner of all tables in the database.
· Read Design permissions for all objects in the database.
For supporting information about converting a secured database, ask the Microsoft Access 2000 Office Assistant for “About converting or enabling a secured database from a previous version of Microsoft Access”
.MDB and .ADP Files
You cannot convert an Access 2000 mdb file into an adp file and vice versa using the Convert Database command. You can use the Upsizing Wizard (by clicking the Tools menu and pointing at Database Utilities) if you want to change an mdb file into an Access project (adp) file. If you want to change an adp file to an mdb file, you can create a new mdb file and import the adp objects into it.
Converting Microsoft Access 97 Databases
The following section describes issues that you may encounter when you convert a Microsoft Access 97 database.
DAO 2.5/3.5-Compatibility Library
If you attempt to enable an Access database containing a reference to the DAO 2.5/3.5-Compatibility Library, you will receive a message that there were compilation errors during the enabling or conversion of the database. Before you enable or convert the Access database, you will have to update the code so that it does not depend on the DAO 2.5/3.5 Compatibility Library.
The only DAO Library you can use with Access 2000 is the DAO 3.6 Library. Older versions of the DAO Library–specifically, DAO 2.5/3.5 Compatibility Library and DAO 3.5 Library–are not supported in Access 2000.
Examples of syntax that is obsolete in DAO 3.6
Commands that are not available in DAO 3.6 / Recommended Replacement Commands for DAO 3.6 /FreeLocks / Idle method
All CreateDynaset methods / OpenRecordset method
All CreateSnapshot methods / OpenRecordset method
All ListFields methods / Fields collection
All ListIndexes methods / Indexes collection
CompactDatabase statement / DBEngine.CompactDatabase method
CreateDatabase statement / DBEngine.CreateDatabase method
DBEngine.FreeLocks method / DBEngine.Idle method
DBEngine.SetDefaultWorkspace method / DBEngine.DefaultUser and
DBEngine.Password properties
DBEngine.SetDataAccessOption method / DBEngine.IniPath property
Database.BeginTrans method / Workspace.BeginTrans method
Database.CommitTrans method / Workspace.CommitTrans method
Database.Rollback method / Workspace.Rollback method
Database.DeleteQuerydef method / Delete method
Database.ExecuteSQL method / Execute method
Database.ListTables method / Tabledefs collection
Database.OpenQuerydef method / Querydefs collection
Database.OpenTable method / OpenRecordset method
FieldSize method / FieldSize property
Index.Fields property / Index.Fields collection
OpenDatabase statement / DBEngine.OpenDatabase method
Querydef.ListParameters method / Parameters collection
Snapshot object / Recordset object
Dynaset object / Recordset object
Table object / Recordset object
For more information about commands that are not available and the suggested replacement for the commands, please ask the Answer Wizard, “DAO Object Library Compatibility.”
Backward Compatibility
For backward compatibility, the conversion process sets a reference to the DAO 3.6 Library in any databases that contain DAO code. If the DAO code uses syntax supported only by the 2.5/3.5 Compatibility Library, then you will have to update that code manually. When the conversion process encounters DAO code unsupported by the DAO 3.6 Library, it presents the alert message. You must manually fix the errors in the converted database.
For more information, see the following Microsoft Knowledge Base article:
ACC2000: Compile Error After Converting Database with Old DAO Code
http://support.microsoft.com/support/kb/articles/q199/0/64.asp
DAO vs. ADO
Access 2000 uses ActiveX Data Objects (ADO) as the default object model in the Visual Basic Environment unlike older versions of Access, which use Data Access Objects (DAO). All new Access 2000 databases have a reference to the ActiveX Data Objects 2.1 Library (msado15.dll) instead of the DAO Library.
Because ADO is the new standard object model in Access 2000, you should consider updating your DAO code to use ADO. Unfortunately, there is no conversion utility that can do it automatically for you. However, there are several Help topics that can provide information and conversion tips. These reference materials are listed below.
Though ADO is the default object model, there may be cases where the DAO object model addresses your specific task better. There is no harm done if you change the reference from ADO to the DAO 3.6 Object Library. You will want to use the object model that best fits the task that you want to accomplish.
For examples of how to modify your code, see "Examples of Converting DAO Code" in Access 2000 Help.
For a map of DAO DLL objects to ADOX objects, see "Converting DAO DDL elements to ADOX" in Access 2000 Help.
For a map of DAO Replication objects to JRO objects, see "Converting DAO Replication to JRO" in Access 2000 Help.
For more information about migrating from DAO to ADO and the differences between DAO and ADO, see the white paper “Migrating from DAO to ADO” located at:
http://www.microsoft.com/data/ado/adotechinfo/dao2ado.htm
Converting Databases with Large Numbers of Code and/or Class Modules
When converting a Microsoft Access database with a large number of forms, reports, and modules, the conversion process fails with a “Can’t create any more class modules” or “Out of Memory” error. Microsoft Access 2000 has a limit of 1000 Visual Basic for Application modules, whereas in Microsoft Access 97, you were allowed to have 1024. The use of lightweight forms (or forms with the HasModule property set to No) and reports in Access 2000 will greatly reduce the impact of this limitation.