| C H A P T E R 11 | Part 3 Upgrading to Microsoft Office | Microsoft Office Resource Kit |
| Upgrading from Previous Versions of Microsoft Access |
|
|
|
If you are upgrading from any of the following versions of Access, this chapter answers these questions for you:
|
– or –
Open the form or report in Design view, rightclick the control, point to Change To, and then click Image.
Converting a Database Secured with a Database Password
If a database has been secured by using a database password in Access 95, you must supply the password before you can convert the database. If this is the only form of security used with the database, then it is the only requirement to convert the database.
Converting a Database Secured with Userlevel Security
The workgroup information file is a database that stores the user names, passwords, and group accounts for a workgroup. In Access 1.x and 2.0, this file is called a workgroup or system database. Users of Access 97 can use workgroup information files and secure databases from previous versions of Access. However, users of a previous version of Access cannot use workgroup information files or databases in Access 97 format.
If a member of your workgroup who is not upgrading to Access 97 shares databases secured with userlevel security, then have all Access 97 users in the workgroup join the workgroup information file that was created in the older version. Do not convert any databases shared by all users of the workgroup. For information about how you can share databases across versions, see "Sharing Databases with Microsoft Access 1.x, 2.0, or 95" later in this chapter.
If all members of a secure workgroup defined in a previous version of Access are upgrading to Access 97, convert all databases used by that workgroup. To convert a database that has been secured in a previous version of Access with userlevel security, you must do the following:
If you are upgrading from Access 1.x or 2.0, after you convert a database secured with userlevel security, recreate the workgroup information file used with it. For more information, see "Recreating Microsoft Access 1.x and 2.0 Workgroup Information Files" later in this section.
Note If you are upgrading from Access 95, use the workgroup information file from Access 95, but compact it with Access 97 before using it. For information about compacting a workgroup information file, see Access online Help.
Joining the Original Workgroup Information File
The Office Setup program creates a new workgroup information file named System.mdw in the System folder (Windows 95) or the System32 folder (Windows NT Workstation 3.51 or later), and specifies that file as your current workgroup information file. If, before upgrading, you are a member of a secure workgroup, you must join your original workgroup information file after upgrading to open or convert secured databases.
To
join a workgroup information file from a previous version of Access
In Windows 95 and Windows NT Workstation version 4.0, you can also click Run on the Windows Start menu, and type msaccess.exe /wrkgrp file name to join the workgroup information file.
Note You can automatically join a workgroup information file every time you start Access 97, using the /wrkgrp commandline option. For information about using commandline options, see Access online Help.
For more information about the userlevel security model, see Chapter 29, "Workgroup Features in Microsoft Access."
Recreating Microsoft Access 1.x and 2.0 Workgroup Information Files
If all members of a secure workgroup from Access 1.x or 2.0 are upgrading to Access 97, recreate their workgroup information file in Access 97 format before they need to use it. Although Access 97 can use workgroup information files from a previous version, additional memory is required to do so. Additionally, when users upgrade to the new workgroup information file, they can all view their group memberships.
The following procedures show how to recreate a workgroup information file in Access 97 format. To complete this task, you must know the exact, casesensitive user names, group names, and personal IDs used to create the accounts in the workgroup. You must also know the exact, casesensitive name, company name, and workgroup ID used to create the original file.
Note If you do not have the information to recreate the workgroup information file, continue to use the previous version; however, only members of the Admins group can view group memberships.
To
create the new workgroup information file in Access 97
If you fail to enter the exact entries used to create the original file, an invalid Admins group is created.
After you have created a new workgroup information file with the original Admins group, you must recreate any user and group accounts.
To
recreate the user and group accounts for a workgroup information
file
When you finish these procedures, the user and group accounts are recreated. The only passwords defined so far are the ones you defined in Steps 5 and 7. You may want to define passwords for other accounts, or users can log on and define their own passwords. As long as you entered the exact, casesensitive name, company name, and workgroup ID when creating the new file, and the exact, casesensitive names and personal IDs when setting up the new accounts, all user and group accounts have the same permissions as the accounts in the workgroup information file from the previous version of Access.
Note In Access 95 and 97, new workgroup information files have an .mdw extension by default. In previous versions, workgroup information files have an .mda extension. The .mdw extension uniquely identifies workgroup information files and prevents them from appearing in dialog boxes for library database (MDA) files. However, you can use the Workgroup Administrator to join or create workgroup information files with .mda extensions.
Converting a Replicated Database
You cannot convert a replicated database from Access directly. Instead, synchronize it with a converted Design Master. A Design Master is the first replica in a replica set to which system tables, system fields, and replication properties have been added. You can make changes to the database structure through the Design Master only. In a replica set, any replica can be the Design Master, but only one at a time.
For the conversion to work, all members of the replica set must be accessed from computers with Access 97 installed. Also, the Design Master must have been opened in Access 95 at least once after it was created before you can convert it. If the Design Master was not opened after it is created, and you try to convert it in Access 97, a message displays. In this case, you must open the Design Master in Access 95 before you can continue. If you want to use a single computer for the conversion, it must be running both Access 95 and 97. For information about running these two versions of Access on the same computer, see "Running Multiple Versions of Microsoft Access on a Single Computer" in Chapter 22, "Supporting Multiple Versions of Microsoft Office."
Note You can allow an Access 95 replica to run in Access 97 without conversion through a process called enabling. However, if you open an enabled Access 95 replica in Access 97 and the replica has not yet been synchronized, Access warns you that there is a synchronization pending that cannot occur until the replica is opened and synchronized in Access 95. For more information about enabling databases, see "Enabling a Database" later in this chapter.
It is best to take a conservative approach when converting a replica set. The following procedure allows you to test a temporary second replica set before committing your original set to the conversion.
To
convert a replica set
If you want to change some objects, you can either make the changes again in the original Design Master after it is converted, or keep the copy and import the changed objects from it.
Important You cannot open synchronized replicas again in Access 95. Once you open the replicas in Access 97, they are irrevocably converted.
Working with Microsoft Access 1.x and 2.0 Databases with Attached ODBC Tables
Access 97 can convert or enable Access 1.x and 2.0 databases that have attached (linked) tables. However, Access 97 cannot open linked tables that refer to Open Database Connectivity (ODBC) sources which use a 16bit ODBC driver manager and driver. For example, if an Access 1.x or 2.0 database refers to an ODBC data source, such as an SQL Server table, the linked table's data source name (DSN) uses the 16bit version of the ODBC driver manager (Odbc.dll) and the corresponding driver, such as the 16bit Microsoft SQL Server ODBC driver (Sqlsrvr.dll).
Access 97 can open only linked ODBC data sources that use the 32bit versions of the ODBC driver manager (Odbc32.dll) and the appropriate ODBC driver, such as the 32bit version of Microsoft SQL Server ODBC driver (Sqlsrv32.dll).
When Access 97 converts or enables an Access 1.x or 2.0 database with linked ODBC tables, it cannot automatically create new DSNs that use the 32bit versions of the ODBC driver manager and ODBC driver. When you convert or enable such a database, if you try to open the table or a form that uses the table for its record source, Access displays the message, "ODBC connection to datasourcename failed." When you close this message, Access 97 displays another message indicating that it cannot open the table or form.
To correct this problem, you must first make sure that the 32bit versions of the ODBC driver manager and the appropriate driver are installed, and then create a new, identically named DSN for each ODBC data source that is linked to the original database.
ODBC drivers are not installed when you choose a Typical installation during Setup. To install the 32bit version of the Microsoft SQL Server ODBC Driver, rerun the Setup and click Add/Remove. Under the Microsoft Access option, select Data Access, select Database Drivers, and then select the Microsoft SQL Server Driver option.
Note If the original database does not use the Microsoft SQL Server ODBC Driver, you must contact the vendor of the driver to obtain a 32bit version of the driver.
After the appropriate driver is installed, create a new, identically named DSN for each ODBC data source that is linked to the original database.
Note In the following procedure, if the 32bit ODBC icon is not available in Control Panel, rerun Setup and install the Microsoft SQL Server ODBC Driver as described earlier. Installing the Microsoft SQL Server ODBC Driver also installs the 32bit version of the ODBC driver manager and other ODBC support files.
To
create a new, identically named 32bit DSN
For information about using the User DSN, System DSN, and File DSN tabs, click Help.
For example, for Microsoft SQL Server you must define the Data Source Name and Server. You may also need to click Options and define additional values, such as the Database Name.
If you do not know the original name of the DSN, open the original database in the version of Access in which it was created, open the linked table in Design view, and then display the Table Properties sheet. The Description property contains the definition for the ODBC connection string. The parameter following DSN= in the connection string is the name of the DSN. To view the rest of the DSN definition, open Control Panel and doubleclick the ODBC icon (not the 32bit ODBC icon). In the Data Source (Driver) box, click the name of the DSN, and then click Setup to view the definition of the DSN.
For more information about enabling a database, see "Enabling a Database" later in this chapter.
|
Note When you export or import Microsoft 97 tables into Access 95, fields with the Hyperlink data type are converted to fields with the Memo data type and their hyperlink functionality is lost.
The format of database objects other than tables is different in Access 97. You cannot import, link, export, cut, copy, or paste these Access 97 database objects to Access 95.
|
Using the Same Table in Microsoft Access 95 and 97
The fact that tables share the same format in Access 95
and 97 is useful if you have frontend and backend
databases that must be shared by users of both versions. Users
of both Access 97 and Access 95
can open tables in the backend database that are in either
version. For more information about frontend and backend
databases, see "Creating
a Front-end Database Linked to Table Data in a Previous Version
Back-end Database" later in this chapter.
|
If your workgroup is upgrading gradually to Access 97, Access 97 users may have to share databases with Access 95 users. Access supports several strategies for sharing databases between different versions. For more information about changes in file format and strategies for sharing databases, see "Sharing Databases with Microsoft Access 1.x, 2.0, or 95" later in this chapter.
The Access 97 Report Wizard, Form Wizard, and the AutoFormat command (Format menu) provide formatting features that are similar to Microsoft Word and Microsoft Excel AutoFormats and templates. The Access 97 Database Wizard is similar to a template in that it provides predefined choices that are used to create new databases. Access 97 allows users to select from a number of other addins and wizards, which also function like templates by providing predefined choices when creating a new table, query, and some properties and controls.
Previous versions of Access allow users to customize wizards; however, these customized wizards cannot be converted to Access 97 format. Any customization of Access 97 wizards must be performed by using the commands available in the Access 97 wizards.
To create a form or report without using a wizard, click Form or Report (Insert menu), and then click Design New. The template used when you create forms or reports this way is set by using the Forms/Reports tab in the Options dialog box (Tools menu). In order to use a template from previous versions of Access, you must convert it to Access 97 format.
In most cases, macros defined in Access 95 run in Access 97 for both converted and enabled databases. However, changes to menus, commands, and other user interface elements may mean that some of your macros must be rewritten, particularly those that include the SendKeys or the DoMenuItem action.
SendKeys Action
A macro from a previous version of Access that uses the SendKeys action does not function properly if the arguments refer to menu commands or dialog box options that have changed. For alternatives to using the SendKeys action, see "Using the SendKeys Statement or Action" later in this chapter.
DoMenuItem Action
In Access 97, the RunCommand action replaces the DoMenuItem action, which is included in Access 97 only for compatibility with previous versions. When you open and save a macro from a previous version of Access that contains a DoMenuItem action, the action and its arguments are automatically converted to the equivalent RunCommand action. The DoMenuItem action no longer appears in the list of actions in the Macro window in Access 97.
When you convert a database from a previous version of Access, some commands may no longer be available. The command may have been renamed, moved to a different menu, or removed entirely. The DoMenuItem actions for such commands are converted to RunCommand actions with a blank Command argument. You must edit the macro and enter a valid RunCommand action, or delete the action.
When you enable a database from a previous version of Access, the DoMenuItem action continues to work properly. DoMenuItem methods used in Visual Basic procedures are unchanged.
Access 97 supports some new Visual Basic keywords, so you can no longer use these keywords as identifiers. These keywords are:
For information about how these new keywords can affect converted or enabled databases, see "Identifiers with the Same Names as Visual Basic Keywords" later in this chapter.
New Toolbars, Menus, and Shortcut Menus
Access 97 supports a new style of toolbars, menus, and shortcut menus. When you convert a database from a previous version of Access to Access 97, any custom toolbars are automatically converted to the new style.
Custom menus and shortcut menus created in a previous version of Access are handled differently. Previous versions of Access use the Menu Builder to create custom menus and shortcut menus. The Menu Builder generates a macro group containing macros that use the AddMenu and DoMenuItem actions. In previous versions of Access, you can also create custom menus and shortcut menus manually (without the Menu Builder), using these macro actions. Custom menu and shortcut menu macros created using either method still run Access 97, but they are not converted to the new style, nor are they available from the new Customize dialog box (View menu, Toolbars submenu).
To save a menu or shortcut menu from a previous version of Access in Access 97 format, select the macro group used to create the menu or shortcut menu in the Database window, and then click Create Menu from Macro or Create Shortcut Menu from Macro (Tools menu, Macro submenu). Unlike new custom menus or shortcut menus created with the Customize dialog box, these menus and shortcut menus depend on the macro group from the previous version of Access. If you delete this macro group or any macros it refers to, the menu or shortcut menu no longer works. Alternatively, you can use the Customize dialog box (View menu, Toolbars submenu) to recreate custom toolbars, menu bars, and shortcut menus.
|
Note When you export tables to Access 2.0, fields with the Hyperlink data type are converted to fields with the Memo data type and hyperlink functionality is lost. Functionality of fields with the following property settings is lost:
If your workgroup is upgrading gradually to Access 97, Access 97 users may have to share databases with Access 2.0 users. Access supports several strategies for sharing databases between different versions. For more information about changes in file format and strategies for sharing databases, see "Sharing Databases with Microsoft Access 1.x, 2.0, or 95" later in this chapter.
The Access 97 Report Wizard, Form Wizard, and the AutoFormat command (Format menu) provide formatting features that are similar to Microsoft Word and Excel AutoFormats and templates. The Access 97 Database Wizard is similar to a template in that it provides predefined choices that are used to create new databases. Access 97 allows users to select from a number of other addins and wizards, which also function like templates by providing predefined choices when creating a new table, query, and some properties and controls.
Note Previous versions of Access allow users to customize wizards; however, these customized wizards cannot be converted to Access 97 format.
To create a form or report without using a wizard, click Form or Report (Insert menu), and then click Design New. The template used when you create forms or reports this way is set by clicking the Forms/Reports tab in the Options dialog box (Tools menu). In order to use a template from previous versions of Access, you must convert it to Access 97 format.
In most cases, macros defined in Access 2.0 run in Access 97 for both converted and enabled databases. However, changes to menus, commands, and other user interface elements may mean that some of your macros must be rewritten, particularly those that include the SendKeys action. In addition, some macro actions work differently in Access 97 than in Access 1.x and 2.0. This section explains these differences.
Using the SendKeys Statement or Action
If you are converting an Access 1.x or 2.0 database to Access 97, changes in some dialog boxes and menus may require you to recode the SendKeys statement or SendKeys action. For example, the Addins submenu has been moved from the File menu to the Tools menu, and the previous Import and Attach commands on the File menu have moved to the Get External Data submenu. Because changes like this are likely to occur for each new version of Access, avoid using the SendKeys statement or action to carry out commands or fill in dialog boxes wherever possible.
If you must use the SendKeys statement or action, consider the following guidelines:
Most commands that are commonly carried out by a menu command or option have an equivalent action or method. For example, you can now use the Dropdown method of a combo box rather than the code SendKeys "{F4}".
New versions of Access are likely to have new and changed options, so the code in your SendKeys statement or action could easily break. Instead, use the GetOption and SetOption methods.
Defining your keystrokes as constants makes updating your code easier in the future.
In Access 2.0, you can enclose the Keystrokes argument of the SendKeys action in quotation marks, but it is not required. If you enclose this argument in quotation marks in subsequent versions of Access, however, an error occurs. To use quotation marks in the Keystrokes argument, you must type two sets of quotation marks, as in Michael ""Mick"" Suyama.
Using the DoCmd Object
To carry out macro actions from code in Access 97, use the DoCmd object and its methods. This object replaces the DoCmd statement that you used in Access 1.x and 2.0 to carry out a macro action.
When you convert a database, Access automatically converts any DoCmd statements and the actions that they carry out in your Access Basic code to methods of the DoCmd object by replacing the space with the dot ( . ) operator.
Using the TransferSpreadsheet and TransferText Actions
Access 97 cannot import Excel version 2.0 spreadsheets or Lotus 123 release 1.0 spreadsheets. If your converted database contains a macro that provides this functionality by using the TransferSpreadsheet action in Access 1.x or 2.0, converting the database changes the Spreadsheet Type argument to Excel version 3.0 (if you originally specified Excel version 2.0) or causes an error if you originally specified Lotus 123 1.0 format.
To work around this problem, convert the spreadsheets to a later version of Excel or Lotus 123 before importing them into Access.
Also, in Access 97, you cannot use an SQL statement to specify data to export when you are using the TransferText action or the TransferSpreadsheet action. Instead of using an SQL statement, you must first create a query and then specify the name of the query in the Table Name argument.
In Access 97, Visual Basic replaces Access Basic. In most respects, Visual Basic is identical to Access Basic, and Access automatically makes most of the necessary conversions to your code when you convert your database.
However, the conversion process makes some changes to your code that you need to be aware of, and there are some additional changes that you must make yourself in order for your application to run successfully in Access 97.
16bit DLLs
If your database application calls procedures in other 16bit DLLs, you must create or obtain 32bit versions of those DLLs and make any necessary modifications to your code when you convert your database to Access 97.
If you cannot obtain a 32bit version of a DLL, use an intermediary DLL that can convert 32bit calls to 16bit calls. For more information, see "Windows Application Programming Interface" later in this chapter.
ActiveX Controls
If your application contains ActiveX controls (formerly OLE control or custom controls) that were set up in Access 2.0, you may need to insert the ByVal keyword in front of arguments that are passed to event procedures called from ActiveX control events, as in the following example:
Sub ChangeMonth_Click(ByVal intCurrentYear As Integer)
In order to determine whether an argument needs to be passed by value, click Compile All Modules (Debug menu) in module Design view. If you receive the following message, you need to insert the ByVal keyword in front of the argument: "Event procedure declaration doesn't match description of event having the same name."
Because type checking of arguments is improved in Access 97, new event procedures created for ActiveX controls automatically have the ByVal keyword inserted when it is needed.
ActiveX Controls on Forms and Reports
When you convert an Access 2.0 database for use in Access 97, ActiveX controls on forms and reports may not be converted automatically. Access 2.0 supports 16bit OLE controls, while Access 97 supports only 32bit ActiveX controls.
If you are converting a database that contains a form or report that has a 16bit version of an ActiveX control, and the 32bit version does not yet exist on your system, Access generates an error message. You must obtain a 32bit version of each ActiveX control that you wish to update and then enter it in the Windows registry. After you register the new ActiveX control, save the form or report in the converted database and then close and reopen the database.
Automation Errors
In Access 1.x and 2.0, ActiveX components that support Automation (formerly OLE Automation) return a generic error. However, Visual Basic now allows an ActiveX component to return error information specific to the error that has occurred. If your existing databases include code to handle a generic Automation error, you may have to update that code to handle the more specific errors that are now returned.
Category Property
You cannot use an object variable in your code to refer to a Category property. The Category property is no longer supported for Form, Report, and Control objects.
CurDir Function
The CurDir function behaves differently in Access 97 than it does in Access 1.x or 2.0 due to the way that applications interact with Windows 95. Since each application has its own current folder, setting the current folder in Windows 95 by doubleclicking an icon does not affect the current folder in Access. The CurDir function in Access 97 always returns the current path.
CurrentDb Function Compared to DBEngine(0)(0)
Use the CurrentDb function instead of DBEngine(0)(0) to return a Database object variable that points to the current database. The CurrentDb function creates another instance of the current database, while DBEngine(0)(0) refers to the open copy of the current database. If you use DBEngine(0)(0), it limits your ability to use more than one variable of type Database that refers to the current database.
The DBEngine(0)(0) syntax is still supported in Access 97, so your code does not change during the conversion process. However, it is recommended that you consider making this modification to your code in order to avoid possible conflicts in a multiuser environment.
DAO Object Libraries
Access 97 includes Data Access Objects (DAO) objects, methods, and properties that replace those in Access 1.x, 2.0, and 95. While the code in Access 97 is compatible with all previous DAO code, future versions of Access may not provide support for some older objects, methods, and properties. The following sections explain how to take advantage of the backwardcompatibility features in Access 97, as well as how to create new applications and modify current applications to prepare for converting to future versions of Access.
Taking Advantage of BackwardCompatibility Features
If you want to continue to use the older versions of DAO objects, methods, and properties in your application, you must first establish a reference to the DAO version 2.5/3.5 compatibility library.
To
establish a reference to the DAO version 2.5/3.5 compatibility
library
The Microsoft DAO 2.5/3.5 compatibility library provides complete backward compatibility with Access 1.x, 2.0, and 95. A reference to this version of the library is added to Access 1.x, 2.0, and 95 applications that you convert to Access 97.
The Microsoft DAO 3.5 object library, which does not include the older objects, methods, and properties, is selected by default whenever you create a new database. All new applications created in Access 97 should reference only the Microsoft DAO 3.5 object library to ensure the application is not using old methods. Also, if your application references only the Microsoft DAO 3.5 object library, you do not have to distribute the Microsoft DAO 2.5/3.5 compatibility library when you distribute your application to other users.
Tip To verify that your application only uses the objects, methods, and properties in the Microsoft DAO 3.5 object library, clear the Microsoft DAO 2.5/3.5 Compatibility Library check box in the References dialog box (Tools menu). Make sure that Microsoft DAO 3.5 Object Library is selected, and then recompile your application by clicking Compile All Modules (Debug menu) in module Design view. If your application recompiles without errors, you no longer need to maintain the reference to the Microsoft DAO 2.5/3.5 compatibility library, and your application will work with the next version of DAO.
Preparing for Conversion to Future Versions
The following table lists the objects, methods, and properties that are not included in the Microsoft DAO 3.5 object library, as well as the features that have been provided to replace them. You can use the new items in the second column to modify code written in previous versions of Access, so that your application is prepared for conversion to future versions of Access, when the items in the first column are no longer available.
| Functionality not present in DAO 3.5 | Recommended DAO 3.5 replacements |
| FreeLocks | Idle method of the DBEngine object (not needed for Access 95 and 97 databases) |
| SetDefaultWorkspace | DefaultUser/DefaultPassword properties of the DBEngine object |
| SetDataAccessOption | IniPath property of the DBEngine object |
| Database.BeginTrans | Workspace.BeginTrans |
| Database.CommitTrans | Workspace.CommitTrans |
| Database.CreateDynaset | Database.OpenRecordset of type dbOpenDynaset |
| Database.CreateSnapshot | Database.OpenRecordset of type dbOpenSnapshot |
| Database.DeleteQueryDef | Delete method of the QueryDefs collection |
| Database.ExecuteSQL | Database.Execute method and Database.RecordsAffected property |
| Database.ListTables | Database.TableDefs collection |
| Database.OpenQueryDef | Database.QueryDefs collection |
| Database.OpenTable | Database.OpenRecordset of type dbOpenTable |
| Database.Rollback | Workspace.Rollback |
| ListFields method of the Table, Dynaset, and Snapshot objects | Recordset.Fields collection |
| Table.ListIndexes | TableDef.Indexes collection |
| QueryDef.CreateDynaset | QueryDef.OpenRecordset |
| QueryDef.CreateSnapshot | QueryDef.OpenRecordset |
| QueryDef.ListParameters | QueryDef.Parameters collection |
| Dynaset object | Dynasettype Recordset object |
| Snapshot object | Snapshottype Recordset object |
| Table object | Tabletype Recordset object |
| CreateDynaset method of the Dynaset and QueryDef objects | Recordset.OpenRecordset with dbOpenDynaset parameter |
| CreateSnapshot method of the Dynaset and QueryDef objects | Recordset.OpenRecordset with dbOpenSnapshot parameter |
For examples of how to modify your code, see the Access online Help.
Database Renaming
When you rename a database, compiled code in the database is decompiled. To recompile code and save all modules in a compiled state, open the database, open a module in Design view, and then click Compile and Save All Modules (Debug menu).
DDE Channels Declared as Variant or Long
If you use the DDEInitiate function to open a dynamic data exchange (DDE) channel, you can declare the variable that stores the channel number, which is a Long value, as either a Variant or a Long value. In Access 1.x and 2.0, the channel number is an Integer value, so you must modify any Declaration statements in your code that create variables of type Integer to store the channel number.
Error Information
In Access 95 and 97, you cannot use the Error function to return a description of Access errors. For example, the following reference does not work:
Error(2450)
In Access 97, use the properties of the Err
object to obtain information about errors.
When an Access error occurs, you can get the error number
and the error description by using the Number and
Description
properties of the Err object , as in the following example:
Debug.Print Err.Number, Err.Description
To return information about Access, Visual Basic, or DAO errors, use the AccessError method. With the AccessError method, you can obtain error information regardless of whether the error has actually occurred.
Exclamation Point Compared to Dot Operator
If you used the dot ( . ) operator syntax when referring to a Field object of a Recordset object in applications created in Access 1.x or 2.0, you must modify those references to use the exclamation point ( ! ) operator syntax. Or, if you want to continue using the dot ( . ) operator syntax, you must establish a reference to the DAO 2.5/3.5 compatibility library in the References dialog box (Tools menu) while in module Design view.
Functions Not Supported in Expressions
The following Visual Basic functions cannot be used in expressions outside a userdefined Sub or Function procedure:
If you need to use one of these functions in an expression outside a procedure, call the function from within a userdefined function that you call from the expression.
hWnd Property
If you use the hWnd property in your code to pass a window handle of a form or a report to a Windows routine, you can pass the value directly to the routine. You do not need to assign the value of this property to a variable. For example:
If Not IsZoomed(Screen.ActiveForm.hWnd) Then
DoCmd.Maximize
EndIf
In Access 1.x and 2.0, the hWnd
property of a form or report is an Integer value. In Access 97,
the hWnd property is a Long value;
you must change your code to accept it.
Line Numbers in Visual Basic Procedures
You cannot assign line numbers greater than 65,529 to statements in your Visual Basic procedures. If your converted Access 1.x or 2.0 application contains line numbers greater than 65,529, you must modify them to fall within the acceptable range.
Code in your Access 1.x or 2.0 application may call procedures that are located in Access wizards. If this is the case, you must, after you convert the application, establish a reference from the application to the wizard database that contains the procedures that you call. For more information about establishing references, see Access online Help.
In Access 2.0, there is no distinction between wizards and libraries, so their public code is always available to the current database. In Access 97, wizards and other addins are no longer treated as libraries. In addition, because wizards may change a great deal from one version of Access to the next, you may need to rewrite some of your code to adapt to the changes after upgrading to a new version of Access.
For code that is no longer provided in the Access wizards, such as the AutoDialer, functionality has been added to Utility.mda, a special library database that is provided with Access. A reference to this library is automatically added when you convert a database to Access 97.
Module Changes
In Access 1.x and 2.0, modules containing procedures that are not specific to any form or report module are called global modules. In Access 97, these are called standard modules.
In Access 97, form and report modules are now called class modules, which means that they can act as templates for a userdefined object. Any public procedures in a form or report module become methods and properties of the new form or report when you create a new instance of it. For more information about class modules, see the Access online Help.
Next Procedure and Previous Procedure Buttons
The Next Procedure and Previous Procedure buttons on the Module toolbar in Access 1.x and 2.0 are not available in Access 97. If you convert an Access 1.x or 2.0 database with a custom toolbar that contains one of these buttons, you do not receive an error, but the buttons have no effect when clicked.
Null Values and ZeroLength Strings
In Access 2.0, you can use the Format function to return one value for a zerolength string and another for a Null value, and you can similarly use the Format property to automatically format fields in table Datasheet view or controls on a form or report. For example, you can use a format expression such as the following with the Format function to return the appropriate string value from code:
Dim var As Variant, strX As String
' Assign some value to strX and pass to Format function.
var = Format(strX, "@;ZLS;Null")
In Access 97, you must test separately for the Null value case and then return the appropriate value based on the result. For example, you could use the IIf function in an expression with the Format function, such as the following:
var = IIf(IsNull(strX),"Null", Format(strX, "@;ZLS"))
This change applies only when you use the Format function to format a value depending on whether it is a zerolength string or a Null value. Other format expressions used with the Format function continue to work as they do in previous versions of Access.
If you convert a database from Access 2.0 to Access 97, you must change any code or property settings to use these methods. You cannot use the Format property in table Datasheet view to distinguish between Null values and zerolength strings.
Objects in the Debug Window
When you are testing and debugging code, you must fully qualify all references to objects that you use in the Debug window, unless you have suspended execution in a form or report module. This means that in the Immediate pane of the Debug window, you must use the code Forms!Categories!CategoryID to refer to the CategoryID control on the Categories form in Form view, instead of just CategoryID, even when the Categories form is the current form.
Also, you cannot use the Me keyword in the Debug window to refer to an object on a form or report when that form or report is in Design view unless you have suspended the execution of code in the form or report.
OLE Objects Assigned to a Variable
If you manipulate OLE objects or other binary data in your code, use an array of bytes to store binary data. In Access 1.x and 2.0, you assign OLE objects, or other binary data less than 64 KB in size, to string variables when you need to manipulate the objects or data in code. You also assign the data returned by the GetChunk method to string variables. However, Visual Basic supplies a Byte data type and Byte functions such as LeftB and RightB. In Access 97, store binary data in an array of bytes instead of a string variable, and use the Byte functions to manipulate that data.
Parent Property
In Access 97, if you use the Parent property of a control in code or in an expression on a form or report, it typically returns the Form or Report object that contains the control. For example, if CategoryID is a text box on the Categories form, the code Forms!Categories!CategoryID.Parent returns a reference to the Categories form.
There are two exceptions:
Percent Sign (%) in Strings
You cannot assign a string containing a percent sign (%) to a variable or a field that has a numeric data type, as in the following example:
Dim intX As Double
intX = "10" ' This works.
intX = "10%" ' This returns an error.
Procedures in Form and Report Modules
In Access 1.x and 2.0, you cannot call a procedure defined in a form or report from anywhere but within that form or report module. In Access 97, you can call a public procedure in a form or report module from any procedure in the current database. You must qualify the procedure with the class name of the form or report module. For example, to call a procedure named DisplayMessage that is defined in the module of the Orders form, use the following syntax:
Form_Orders.DisplayMessage
It is better to place procedures that you call from outside a form or report in a standard module if possible, rather than in a form or report module.
Reference to a Microsoft Access Database
You cannot set a reference to a database created with a previous version of Access from Access 97. You must convert the database to an Access 97 database in order to set a reference to it. For information about converting databases, see "Converting Databases" earlier in this chapter.
Time Values in Query Criteria
When you convert an Access 1.x or 2.0 database to Access 97, queries that contain criteria based on specific time values in Date/Time fields may return different results than they do in previous versions. This behavior may also occur if you link tables from an Access 1.x or 2.0 database to an Access 97 database. Only the time portion of Date/Time fields is affected.
Visual Basic Scoping and Object Naming
The following Visual Basic scoping rules affect the names you choose for your objects, modules, and procedures.
Modules and Other Objects with the Same Name
When you name a module, avoid prefacing module names with "Form_" or "Report_". Naming a module in this way could conflict with existing code you have written behind forms and reports.
If you have a module in an application from Access 1.x or 2.0 that does not follow these naming rules, Access 97 generates an error when you try to convert the application. For example, a module named Form_Orders in an Access 1.x or 2.0 database generates an error, and you are asked to rename the module before attempting to convert it.
Modules and Procedures with the Same Name
A procedure can have the same name as a module. However, to call that procedure from an expression anywhere in your application, you must use a fully qualified name for the procedure, including both the module name and the procedure name, as in the following example:
IsLoaded.IsLoaded("Orders")
Procedures and Controls with the Same Name
If you call a procedure from a form, and that procedure has the same name as a control on the form, you must fully qualify the procedure call with the name of the module in which it resides. For example, if you want to call a procedure named PrintInvoice that resides in a standard module named Utilities, and there is also a button on the same form named PrintInvoice, use the fully qualified name — Utilities.PrintInvoice — when you call the procedure from your form or form module.
Controls with Similar Names
A control's name must not differ from an existing control's name by only a space or a symbol. For example, if you have a control named [Last_Name], you cannot have a control named [Last Name] or [Last+Name].
Modules with the Same Names as Type Libraries
You cannot save a module with the same name as a type library. If you try to save a module with the name DAO, Access, or VBA, a message is generated, stating that the name conflicts with an existing module, project, or object library. Similarly, if you have set a reference to another type library, such as the Excel type library, you cannot save a module with the name Excel.
Fields with the Same Names as Methods
If a field in the table has the same name as a DAO method on a Recordset object, you cannot refer to the corresponding field in the recordset with the dot ( . ) operator syntax. You must use the exclamation point ( ! ) operator syntax, or Access generates an error. The following example shows how to refer to a field called AddNew in a recordset opened on a table called Contacts:
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Contacts")
Debug.Print rst!AddNew
Modules with the Same Names as Visual Basic Functions
If you save a module with the same name as an intrinsic Visual Basic function, Access generates an error when you try to run that function. For example, if you save a module named MsgBox, and then try to run a procedure that calls the MsgBox function, Access generates the error "Expected variable or procedure, not module."
Modules with the Same Names as Objects
If a database created in a previous version of Access includes a module that has the same name as an Access object or a DAO object, you may encounter compilation errors when you convert your database to Access 97. For example, a module named Form or Database may generate a compilation error. To avoid these errors, rename the module.
Fields Used in Expressions or Bound to Controls on Forms and Reports
When you create a field in a table that is bound to a control on a report or used in an expression in the ControlSource property of a control or a report, avoid assigning the field a name that is the same as a method of the Application object. To see a list of methods of the Application object, click Object Browser (View menu) while in module Design view. Click Access in the Project/Library box, click Application in the Classes box, and then view the methods of the Application object in the Members Of box.
When you create a field in a table that is bound to a control on a form or report, avoid assigning the field any of the following names:
These are the names of methods used internally by Access to work with forms and reports. If you use them as names for fields, they could cause conflicts or unexpected behavior when referenced in expressions or Visual Basic code.
Identifiers with the Same Names as Visual Basic Keywords
The version of Visual Basic that is used by Access 97 contains some new Visual Basic keywords, so you can no longer use these keywords as identifiers:
When you convert or enable a database from a previous version of Access, existing identifiers that are the same as a new Visual Basic keyword cause Access 97 to display the following message: "There were compilation errors during the conversion or enabling of this database." This message is displayed, for example, if you try to enable or convert the Northwind Traders sample database from Access 2.0. This is because the ShowEvent Sub procedure in the Utility Functions module uses Event as a string variable.
To correct this problem, open the module that contains the code that uses one or more of the new keywords as identifiers and rename them. For example, after converting the Northwind Traders database, open the Utility Functions module, and change the Event string variable name throughout the procedure to another name, such as strEvent.
Windows Application Programming Interface
If your existing Access 1.x or 2.0 Access Basic code makes calls to the Windows application programming interface (API), you must modify these calls when you convert your database to Access 97. Access 1.x and 2.0 are 16bit applications and run on 16bit versions of Windows. Access 97 is a 32bit application and runs on the 32bit Windows 95 and Windows NT operating systems.
The Windows API consists of a set of DLLs containing systemrelated procedures that include functions, messages, data structures, data types, and statements you can use in creating applications that run under Windows 95 or Windows NT operating system. To call these procedures from Visual Basic, you must first declare them with a Declare statement. You can then call them as you would any other procedure.
The following list provides some tips for converting your code:
| 16bit Windows DLL | 32bit Windows DLL |
| User.dll | User32.dll |
| Kernel.dll | Kernel32.dll |
| GDI.dll | GDI32.dll |
The names of some functions in the 32bit Windows API have changed. Additionally, functions in the 32bit Windows API are casesensitive.
Some functions have new parameter data types in the 32bit Windows API.
If a 16bit version of a DLL with the same name as a 32bit DLL exists on your computer, Access may try to call a function in that DLL if your path lists its folder before the folder that contains the new DLL.
Some 32bit DLLs contain functions with slightly different versions to accommodate both Unicode and ANSI strings. An a at the end of the function name specifies the ANSI version. A w at the end of the function name specifies the Unicode version.
For more information about using 32bit Windows API procedures and about porting your existing database applications to 32bit Access 97, consult the following resources:
 |
World Wide Web For the latest information about modifying 16bit calls for 32bit Office in Access, connect to the Access Developer Web site at: |
|
|
The following table summarizes the advantages and disadvantages of each strategy.
| Strategy | Advantages | Disadvantages |
| Enable previous version databases | All Access users can open databases and add, edit, or delete data. | Database file size can increase substantially. Access 97 users cannot modify or add new objects, or take advantage of many features unique to Access 97. |
| Create a frontend database in Access 97 format linked to table data in a backend database from a previous version of Access | All Access users can open databases and add, edit, or delete data. Access 97 users can modify or add new objects, (except tables) and can take advantage of features unique to Access 97. | Additional development of the frontend databases must be synchronized. Changes made to the frontend database in Access 97 must be repeated in the backend database. Features unique to Access 97 are not supported in the older version backend database. |
Enabling a database keeps the format intact so it can be shared by users of different versions of Access. When an Access 97 user enables an Access 1.x, 2.0, or 95 database, other users can browse the database and add, delete, or modify records; but they cannot switch to Design view on any objects. To modify the design of existing objects or to add new objects, the database must be opened in the version of Access used to create it.
Note Before you enable a database, make sure it is not open in a previous version of Access. If the database is located on a server or shared folder, make sure no one else has it open. Finally, if you have a database open in Access 97, close it.
To
enable an Access 1.x, 2.0, or 95 database
The first time a database from a previous version of Access is opened, Access 97 displays the Convert/Open Database dialog box.
If the database contains forms, reports, and modules, Access 97 creates separate copies of these objects and their Access Basic (Access 1.x or 2.0) or Visual Basic (Access 95) code so that they can run under Access 97. This information is stored in a hidden table named MSysModules2. Depending on the size of the forms, reports, and modules in the database, the addition of the MSysModules2 table can increase the file size by as much as double. If an Access 1.x or 2.0 database has been enabled previously by Access 95, it has a similar table named MSysModules, which further increases the size of the database.
Access 97 does not display the Convert/Open Database dialog box the next time the database is opened unless a change is made to code in modules, forms, or reports in the previous version of Access. If a change has been made to the code, you must enable the database again.
You can speed up the process of enabling a large database created with Access 1.x or 2.0 by increasing the maximum buffer size beyond the default. To change this setting, modify the Windows registry by setting the MaxBufferSize value to 4096, decimal base in the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Jet 2.x. For more information about editing the Windows registry, see Appendix C, "Registry Keys and Values."
Creating a Frontend Database Linked to Table Data in a Previous Version Backend Database
Enabling a database as described in the previous section places limitations on Access 97 users, increases the size of the database, and requires additional memory, particularly when running code. As an alternative, you can split the database into an Access 97 frontend database that contains all objects other than tables, and then link this database to a shared backend database in the older version format. The backend database contains the tables.
In general, it is good practice for developers to keep application code and objects in a separate database from the tables. This allows administrators to convert a copy of the frontend database to Access 97 format while leaving the backend database containing the tables in the previous version format until all users have upgraded to Access 97. In this way, the converted copy of the frontend database gains all the features and functionality of Access 97, yet the backend database containing the tables is still available to all users.
Many developers who use previous versions of Access organize shared databases as frontend/backend databases. If this is the case for you, convert a copy of the frontend database and distribute it to all Access 97 users, and then use the Linked Table Manager (Tools menu, Addins submenu) to relink the tables in the backend database. If the current database has not been split in this fashion, you can do so in the previous version of Access, and then convert the frontend database.
You can also use Access 97 to split the database and link to the older version tables. In Access 97, convert the database, split it using the Database Splitter Wizard, and then use the Linked Table Manager (Tools menu, Addins submenu) to relink the original tables in the previous version database.
Note Before you create and link a frontend database, make sure the linked database is not open in a previous version of Access. If the database is located on a server or shared folder, make sure no one else has it open. Finally, if you have a database open in Access 97, close it.
To
create an Access 97 frontend database
– or –
Select a different location for the Access 97 database, and then click Save.
Access creates a converted copy of the database in Access 97 format without altering the original database. Then you can link the copy of the database to the original table data.
To
link a frontend database to table data in a backend
database from a previous version of Access
Tip You
can also open a new, blank database in Access 97.
To import all objects except for tables into the new database,
point to Get External Data on the File menu and
click Import. Then link the tables from the previous version
database by clicking Link Tables on the Get External
Data submenu (File menu).
|
