This topic describes how to successfully plan for and deploy Microsoft Access 2002 throughout your corporation. It discusses five areas of deployment:

• Strategies for opening, converting, splitting, or enabling Access databases (.mdb files).
• Using the ANSI-89 and ANSI-92 SQL query modes.
• Installing SQL Server 2000 Desktop Engine and upgrading from Microsoft Data Engine 1.0 (MSDE).
• Using data access pages and Office Web Components.
• Changes to the Visual Basic object model.

References and Web links to additional information, such as white papers, readme files, Microsoft Knowledge Base articles, and Access 2002 Help topics are also provided. For more information about Access 2002, see the Access 2002 Help topic "What's New in Access."

Information about Access 2002

Deploying Microsoft Access 2002 databases (.mdb)

Access 2002 databases (based on the Microsoft Jet database engine) can work with two database file formats — Access 2000 and Access 2002 file formats. No immediate conversion is necessary for databases created in Access 2000. This section discusses the traditional options of converting, enabling, or splitting Access databases into front-end/back-end applications.

Using Access 2002 with Access 2000 databases in a mixed file format environment

In Access 2002, you can modify data and make design changes to an Access 2000 database. You can use a mixed file format environment where users can easily share databases, whether they are using Access 2000 or Access 2002.

During your Access 2002 rollout, Microsoft recommends using the Access 2000 file format. In this mixed file format environment, both Access 2000 and 2002 users have a default file format of 2000. New files will be created in Access 2000 as long as the default file format is Access 2000. The database file format is displayed on the title bar of the database window.

You can use an Access 2000 database in Access 2002 by opening the database. No conversion is necessary. Both Access 2000 users and Access 2002 users can share any Access 2000 database.

Features that are new in Access 2002 are available when you use an Access 2000 file in Access 2002, but are not available when you use the same file in Access 2000. When you open the file in Access 2000, any functionality specific to Access 2002 is ignored. However, you can make design changes that take advantage of features specific to Access 2002. In most cases, those design changes remain intact when you open the file in Access 2002.

For more information on using Access databases in a mixed file format environment, see the Access 2002 Help articles "About using an Access file with multiple versions of Access" and "Use an Access file with multiple versions of Access."

Developing an application for use in both Access 2000 and Access 2002

In a mixed file format environment, Microsoft strongly recommends that you design and update all databases using Access 2000. If you design with Access 2002 using the Access 2002 file format, your database cannot be opened by users with Access 2000.

If you develop an application for use in both Access 2000 and Access 2002, compiling the application in Access 2000 before distributing it has two advantages:

• Better performance in Access 2000.

  When you open an Access 2000 file for the first time in Access 2002 and save changes to any module — including a module containing the code behind a form or report — Access increments the version of the Visual Basic project. This prevents Access 2000 from loading the compiled version of the Visual Basic project. As a result, the application might run more slowly in Access 2000. To ensure optimal performance of an Access 2000 file that you will use in both Access 2000 and Access 2002, compile and save the application in Access 2000.

• Preventing errors caused by differences between versions.

  The object libraries that ship with Access 2002 contain some new objects, methods, properties, and events that are not available in Access 2000. Using one of these new objects, methods, properties, or events in Access 2000 will result in a compile error. If you want to use an Access file in both Access 2000 and Access 2002, compile and save the application in Access 2000 to ensure that the objects, methods, properties, and events in your code are available in both versions.

For more information on developing applications for use in both Access 2000 and Access 2002, see the topic "Access 2002 features that are not available in Access 2000" in Access 2002 Help.

When to convert an Access 2000 file to the Access 2002 file format

You should convert to the Access 2002 file format only if all users have upgraded to Access 2002, and if you have been successfully using the file in the Access 2000 file format. If all users have upgraded to Access 2002, you can develop an application in the Access 2002 file format without having to make sure that any object, method, property or function you use is also available in Access 2000.

The Access 2002 file format offers the following advantages:

• Provision for unknown properties and objects that might exist in future versions of Access
• Ability to save an Access file as an MDE or ADE while in Access 2002
• Improved storage format

After all users have upgraded to Access 2002, you can change the default file format for users and databases in the following ways:

• Using the user interface (point to Options on the Tools menu, click the Advanced tab, and then select Access 2002 in the Default File Format list box).
• Setting the registry key for the default file format:

  HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Access\Settings\
  Default File Format

  Valid values are:

  9 (Access 2000 file format)

  10 (Access 2002 file format)

---

 Note    You can convert an Access 2002 database or Access 2002 project back to the Access 2000 file format. When you use an Access 2000 file in Access 2002, new Access 2002 features are still available. However, those features are not available when you use the same file in Access 2000. If the file contains Visual Basic procedures that use objects, functions, properties, methods, or arguments available only in Access 2002, you will receive a compile error when you open the Access file in Access 2000.

---

For more information on converting Access databases, see the topics "About converting an Access file" and "Change the default file format" in Access 2002 Help.

When to enable a previous-version Access file without converting it

You may want to enable an Access database in a multiuser environment where all users cannot upgrade from Access 97 or earlier to Access 2002 at the same time. In this situation, a database must be used simultaneously with different versions of Access, and users who have upgraded to Access 2002 can enable a database in a previous-version format.

When you open an Access 2.0 database, an Access 95 database, or an Access 97 database in Access 2002, the Convert/Open Database dialog box asks you to choose between converting and opening (enabling) the Access database. When an Access 2002 user enables the database, Access maintains the original format so that users of previous versions of Access can continue to use the database. For example, if a shared database is created in Access version 2.0, it can be used with Access version 2.0 and enabled in Access 2002.

When you are using Access 2002 to work with an enabled previous-version Access database, you can use objects in the database to view and modify data. However, you cannot save changes to database objects. To modify the design of an object or create a new object in the Access database, you must either open it in the previous version or convert the database to the Access 2000 or Access 2002 file format.

For more information about enabling Access databases, see the topics "When should I convert or enable an Access file?" in "About converting an Access file"; and "Use an Access file with multiple versions of Access" in Access 2002 Help.

When to convert from Access 97 or earlier to the Access 2000 or Access 2002 file format

In most cases, you will want to convert a previous-version Access database to the Access 2000 file format if all users have upgraded to Access 2000 or later.

Once you have converted an Access database to the Access 2000 or Access 2002 file format, you cannot open it in the previous version of Access. You can convert an Access 2000 database back to Access 97. However, you cannot convert an Access 2000 database back to Access version 2.0 or 95.

For more information on converting Access databases, see the topics "About converting an Access file" or "Convert an Access file" in Access 2002 Help.

Splitting a database into a front-end/back-end application

If your Access database is a multiuser (shared) database, and all users cannot upgrade to Access 2000 or later at the same time, you can split the database so that it is a front-end/back-end application. You can then have different versions of the front end connected to the back end, which remains unaltered. Users of Access 2000 or later can use a converted version of the front end, where they can take advantage of new features.

For more information on splitting Access databases into front-end/back-end applications, see the topic "Use an Access file with multiple versions of Access" in Access 2002 Help.

Troubleshoot conversion

If Access encounters errors while converting an Access file, you can view a summary of these errors by opening the Conversion Errors table in the new Access file. The Conversion Errors table contains the following columns:

• Object Type

  The type of database object in which Access encountered an error, or "Database" if Access encountered an error that is not specific to a particular type of object.

• Object Name

  The name of the object in which Access encountered an error. If Access encounters a compilation error during conversion, however, the name of the module that contains the error is not specified.

• Error Description

  If necessary, you can press SHIFT+F2 to view the entire description of the error.

For more information on troubleshooting the conversion of Access databases, see the topics "Troubleshoot converting an Access file" or "Troubleshoot using an Access 2000 file with multiple versions of Access" in Access 2002 Help.

Access 2002 deployment decision diagram

Use the following flowchart to help you make decisions for your enterprise.

Using ANSI SQL Query Mode

You can create SQL queries in one of two ANSI SQL query modes, ANSI-89 or ANSI-92. To avoid problems, you should avoid mixing queries created under two different query modes.

For more information on ANSI SQL Query Mode, see the topics "About ANSI SQL query mode" and "Set ANSI SQL query mode" in Access 2002 Help.

What is ANSI SQL Query Mode?

Access 2002 now lets you use two types of SQL syntax to create queries called ANSI SQL Query Modes: ANSI-89 SQL (also called Microsoft Jet SQL and ANSI SQL), which is the traditional Jet SQL syntax; and ANSI-92 SQL, which has new and different reserved words, syntax rules, and wildcard characters.

ANSI-89 query mode

ANSI-89 describes the traditional Jet SQL syntax. This query mode conforms closely to the ANSI-89 Level 1 specification but is not ANSI-89 Level 1 compliant. Certain ANSI-89 SQL features are not implemented, and the wildcard characters conform to the Visual Basic® for Applications (VBA) specification, not SQL.

---

 Note   ANSI-89 is the default setting for the Access 2000 file format. Furthermore, you cannot set the SQL query mode new database default to ANSI-92 in 2000 file format because the option is disabled; ANSI-89 is the only query mode setting available for a database in the Access 2000 file format.

---

ANSI-92 query mode

ANSI-92 provides new reserved words, syntax rules, and wildcard characters that enhance your ability to create queries, filters, and SQL statements. This mode conforms closely to the ANSI-92 Level 1 specification but is not ANSI-92 Level 1 compliant. This query mode has more of the ANSI syntax, and the wildcard characters conform to the SQL specification.

In Access 2000 using ActiveX Data Objects (ADOX), you could programmatically create queries that used ANSI-92 SQL syntax. However, any queries you created were not visible in the Database window because there was no option to set this mode in the user interface. Now in Access 2002, you can set the ANSI SQL query mode through the user interface for the current database, and as the default setting for new databases.

---

 Note   ANSI-92 is the default setting for the Access 2002 file format.

---

Why you should avoid mixing queries created under different ANSI SQL query modes

The two ANSI SQL query modes, ANSI-89 and ANSI-92, are not compatible. When you create an Access database, you need to decide which query mode you are going to use, because mixing queries created in both query modes could produce runtime errors or unexpected results. The range of data types, reserved words, and wildcard characters are different in each query mode.

In general, avoid doing the following to prevent problems caused by mixing queries created under different ANSI SQL query modes:

• Converting an Access database set to ANSI-92 SQL query mode from the 2002 file format to the 2000 or 97 file format.

  ANSI-92 SQL query mode is only supported in the 2002 file format.

• Changing the ANSI SQL query mode for the current database after you have created one or more queries.

  If you must do this, retest the existing queries to ensure that they still run or produce expected results, and rewrite the queries if necessary.

• Importing queries created under one ANSI SQL query mode into an Access database set to another mode, or exporting queries created under one ANSI SQL query mode to an Access database set to another mode.
• Changing the ANSI SQL query mode in an existing application.

  Doing this could break your application and require rewriting your application.

• Allowing for accidental or intentional changes to the ANSI SQL query mode of your application.

  In Access 2000, you can only programmatically change the ANSI SQL query mode and any queries created under ANSI-92 mode were hidden in the Database window. In Access 2002, you or a user can change ANSI SQL query mode through the user interface and ANSI-92 queries are no longer hidden in the Database window, so you should prevent accidental or intentional changes to the ANSI SQL query mode of your application by protecting your code and preventing the changing of the query mode through the application's user interface.

• Using ambiguous aliases and column names.

  To avoid confusion, ensure that aliases and column names are always unique in an SQL statement.

Deploying SQL Server 2000 Desktop Engine

This section discusses the following subjects:

• Installing and customizing the installation of SQL Server 2000 Desktop Engine.
• Coexisting with or converting Microsoft SQL Server Data Engine (MSDE) 1.0.

Information about Access projects and SQL Server 2000 Desktop Engine

What is SQL Server 2000 Desktop Engine?

Microsoft SQL Server 2000 Desktop Engine is a technology that provides local data storage in a format compatible with Microsoft SQL Server 2000. You can also use SQL Server 2000 Desktop Engine as a remote data storage solution. Think of SQL Server 2000 Desktop Engine as a client/server data engine alternative to the file server Microsoft Jet database engine. SQL Server 2000 Desktop Engine runs under Microsoft Windows 2000 or later and Windows 98 or later. It is designed and optimized for use on smaller computer systems, such as a single-user computer or small workgroup server.

Because SQL Server 2000 Desktop Engine is based on the same data engine as SQL Server, most Access projects or client/server applications run on either version. However unlike SQL Server, SQL Server 2000 Desktop Engine has a 2 GB database size limit, does not support Symmetrical Multiprocessing (SMP) on Windows 98 or later, and when using transactional replication, cannot be a replication publisher (although it can act as a replication subscriber).

---

 Note   Do not confuse SQL Server 2000 Desktop Engine with SQL Server 2000 Personal Edition. SQL Server 2000 Personal Edition includes a full set of management tools and most of the functionality of SQL Server Standard Edition, but is optimized for personal use and is a separate product.

---

Converting from MSDE 1.0 to SQL Server 2000 Desktop Engine

There are several approaches you can take to installing SQL Server 2000 Desktop Engine as discussed below.

---

 Note   Always ensure the SQL Server Service Manager is stopped before installing SQL Server 2000 Desktop Engine.

---

---

 Caution   Once you install SQL Server 2000 Desktop Engine, Microsoft strongly recommends you change your password (point to Security on the Tools menu and click Set Login Password) if you use the System Administrator (SA) password to log on, especially on a Windows 98 operating system.

---

Installing SQL Server 2000 Desktop Engine when there is no current MSDE 1.0 installation

• Do a default installation of SQL Server 2000 Desktop Engine on your computer.

Installing SQL Server 2000 Desktop Engine when MSDE 1.0 is installed but you do not need it or any databases created with it

1. Uninstall MSDE 1.0.
2. Install SQL Server 2000 Desktop Engine on your computer.

Installing SQL Server 2000 Desktop Engine to coexist with MSDE 1.0 (with no logins and permissions)

1. Install SQL Server 2000 Desktop Engine on your computer using a named instance.
2. Transfer the MSDE 1.0 database to SQL Server 2000 Desktop Engine.

Installing in this manner is useful for parallel testing of databases in both versions.

Alternatively, you can detach the MSDE 1.0 database, and then attach it to SQL Server 2000 Desktop Engine. Use this approach if you want to save disk space.

---

 Note   Once you transfer or detach/attach a database to SQL Server 2000 Desktop Engine, you cannot revert back to MSDE 1.0.

---

Installing SQL Server Engine to coexist with MSDE 1.0 (with logins and permissions)

1. Install SQL Server 2000 Desktop Engine on your computer using a named instance.
2. Use the Copy Database wizard in SQL Server Enterprise Manager 2000 to transfer the database.

---

 Note   If you are installing on Windows 98 or Windows Millennium Edition (Windows Me), make sure the database file and database log files are located in a shared folder.

---

Customizing the SQL Server 2000 Desktop Engine Installation

You can do a custom installation of SQL Server 2000 Desktop Engine by using the following configuration options either in the Microsoft Windows Setup.ini file, or as command-line switches when you run Setup.exe.

---

 Note    All configuration options must be in upper-case.

---

The following is an example of a custom installation using command-line switches:

Setup.exe \\MySWdisk\SQLSW\ TARGETDIR="C:\Program Files\SQL8\" 
DATADIR="C:\Program Files\SQLDATA\" 
COLLATION="SQL_Latin1_General_CP1257_CS_AS"
INSTANCENAME=myinstance

Installing SQL Server Desktop Engine on Windows 98 or Windows Me

If you need to install SQL Server 2000 Desktop Engine on computers with the Windows 98 or Windows Me operating system, be aware of the following additional requirements and limitations:

• Although you can use SQL Server Enterprise Manager to administer the database, certain administrative tasks require that the database file and database log files are located in a shared folder.
• Some SQL Server security features are not available because there is no Windows NT security.
• You need an additional 32 MB of RAM in addition to the 64 MB RAM minimum requirement.
• After installing SQL Server 2000 Desktop Engine and before rebooting your computer, you may need to modify the system path in the autoexec.bat file. For example:

  SET PATH = %PATH%;"c:\Program FilesMicrosoft SQL Server\80\tools\binn\"
  

Data Access Pages and Microsoft Office XP Web Components

This section discusses the following:

• When to convert data access pages
• Using and installing Microsoft Office XP Web Components and related licensing issues

Information about data access pages and Office XP Web Components

Converting data access pages

If you are using an Access database in the Access 2000 file format, you do not have to convert your existing data access pages. You do need to convert data access pages under the following circumstances:

• You convert your Access 2000 database to the Access 2002 file format and you want to modify the data access page.
• You are using an Access database in the Access 2002 file format and you open a data access page created in Access 2000, then modify it.

In either case, Access 2002 automatically converts the data access page for you when you open the page in Design view. Access 2002 creates a backup copy of the page (pagefilename.bak.htm) at the same location as the original data access page file.

Once you convert a data access page to 2002 format, you cannot revert the converted page back to the 2000 format. If you do want to return to the Access 2000 version of the page, delete the converted file, rename the backup copy, and then connect the page to the database.

If you create a data access page in Access 2002, you cannot open it in Design view of Access 2000. However, you will be able to open the page in Page view if you have Office XP Web Components installed.

Using Office XP Web Components

Office XP Web Components are a set of ActiveX® controls installed with Microsoft Office XP. With Office Web Components installed on your computer, you can interactively use a spreadsheet, a PivotTable® list, and a chart on a Web page in Microsoft Internet Explorer 4.01 or later. If you want to use the components on a data access page in Access, you should have Internet Explorer 5 or later.

If Office Web Components are not installed on your computer but you have an Office XP site license that permits intranet distribution, you can configure the components so that users will be prompted to download Office Web Components from your corporate intranet. This allows users at your site to use a data access page without having Office XP software installed on their computers.

Installing the Office Web Components

Office Web Components are installed with Office XP and Office applications, or they can be installed separately from an installation point provided by the Web page designer.

When you distribute a Web page that uses components, users who do not have the components installed will be prompted to install the components, provided the Web page designer configures the components to do so. The Web page designer must also provide an installation point for the components and a pointer to that location on the component installation page.

Once the Office Web Components are installed, users who have access to an Office XP license will be able to interact with and make changes to the components. Users without licenses will be able to view and print the components and the data in them, but they cannot interact with or make changes to them.

You cannot freely distribute Office XP Web components. However, you can redirect users to an alternate download site instead of the Microsoft Office Online Web site to download and run a self-extracting package. You may need to do this for the following reasons:

• Users are trying to access your application external to your intranet and your company firewall helps prevent it.
• Your data access page application uses Remote Data Services (RDS) and a three-tier architecture.

---

 Note   You can also install Office XP Web Components using the Setup.exe located in the \FILES\OWC folder on the Office XP CD.

---

If users accessing a data access page do not have the Office Web components on their computer, by default a Web page with the title "Data Access Page Notification" is displayed. The Web page instructs users they are missing Office XP Web Components on their computer and tells the users where to download the files. Using the Microsoft Script Editor or Microsoft Notepad, you can revise the data access page by changing the validateOWC function, which dynamically creates and displays the Data Access Page Notification page. Replace the destination URL in the HREF Attribute of the Anchor tag with a URL that contains the text string "Click here to install the Office Web Components." For example:

<a href='http://officeupdate.microsoft.com/office/redirect/
10/owcsetup.asp?HelpLCID=1033'>Click here to install 
the Office Web Components</a>

The value 1033 is the English (U.S.) locale identification number, also called an LCID. To use another language, type the LCID for that language. For a complete list of LCID numbers, see the Access Help topic "Locale identification numbers for language-specific files."

When users see this revised page and click on the link, they will see an Office Update download Web page that explains how to install the Office Web XP Components and that contains a link to start the download operation.

Details on licensing and functionality

A data access page displayed in Internet Explorer 5.0 or later that is just using the Microsoft Office XP Data Source Control (MSODSC) to connect to a data source always has full functionality, whether or not users have a valid Office XP license.

The following table summarizes different licensing scenarios and what functionality users have with Office XP Web Components.

Changes to the Access Object Model and Programmability

Access 2002 has many new objects, properties, methods, and events. Developers in your corporation should study these properties to see how they can take advantage of the many new features in Access 2002 to enhance existing and new applications.

Before you convert applications written in Access 2.0, you should read the Microsoft Access Visual Basic Reference Help to see what possible behavior changes might occur in your tables, forms, and reports.

Access includes ActiveX Data Objects (ADO) 2.5 as the default data access library. Although Data Access Objects (DAO) 3.6 is included, it is not referenced by default. Versions of the DAO library prior to 3.6 are no longer provided or supported in Access 2002.

Information about programming Access 2002