Using Microsoft Access 2002 with MSDE 2000

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Paul Cornell
Microsoft Corporation

September 2002

Applies to:
   Microsoft Access 2002
   Microsoft SQL Server 2000 Desktop Engine

Summary: The Microsoft SQL Server 2000 Desktop Engine (also known as MSDE 2000) is an alternative to Microsoft Jet for data storage and data management. In this article, you'll learn more about MSDE 2000; specifically, you will learn about how to use Microsoft Access 2002 to work with MSDE 2000 databases. You will also learn how to distribute Access 2002 solutions that are based on MSDE 2000 to end users.

Contents

Introduction
Comparing MSDE 2000 to Jet 4.0
Installing, Starting, and Stopping MSDE 2000
Securing MSDE 2000
Getting Help for MSDE 2000
Upsizing Microsoft Access 2002 Solutions to MSDE 2000
Using Microsoft Access 2002 to Create a New MSDE 2000 Database
Using Microsoft Access 2002 to Manage an Existing MSDE 2000 Database
Distributing Solutions Based on MSDE 2000
Sample Walkthrough: Creating and Deploying an Access 2002 Project and MSDE 2000 Database
Conclusion

Introduction

The Microsoft SQL Server 2000 Desktop Engine (also known as MSDE 2000) enables software developers to embed data storage within their custom applications. MSDE 2000 is included with Microsoft SQL Server 2000, Microsoft Access 2002, and any Microsoft Office XP edition that includes Access 2002. You can think of MSDE 2000 as a client/server alternative to the Microsoft Jet file server database engine in Microsoft Access 2002.

In this article, we'll take a look at:

  • When to use MSDE 2000 over Microsoft Jet.
  • How to install, start, and stop MSDE 2000.
  • How to "upsize" Access 2002 databases to MSDE 2000.
  • How to use Access 2002 to create and attach to MSDE 2000 databases.
  • How to distribute solutions based on MSDE 2000.
  • A sample walkthrough of using Access 2002 to create an MSDE 2000 database and distributing the solution to end users.

Comparing MSDE 2000 to Jet 4.0

Jet 4.0, the native database engine for Microsoft Access 2000 and 2002, should be used when:

  • You want the highest compatibility with Microsoft Access 97 or earlier databases.
  • Your environment has a small number of simultaneous users (20 or fewer).
  • You anticipate having very low resources, such as memory or disk space.
  • You are creating simple, "throw-away" prototypes with no future lifespan.

MSDE 2000 should be considered instead of Jet 4.0 when:

  • You want to develop from a single code base scalable from a single user to thousands of users.
  • You expect a future need for greater scalability to Microsoft SQL Server 2000.
  • You require support for Microsoft Windows NT® integrated security.
  • You require greater reliability, such as transaction logging.
  • You want to develop stored procedures or triggers.
  • You are using Access to design databases that will be accessed by multi-threaded or Web-based applications.

For more information, see Choosing between MSDE and Jet.

Installing, Starting, and Stopping MSDE 2000

To install MSDE 2000 on a development computer, open the SETUP.EXE file in the \MSDE2000 folder on the Access 2002 or Office XP edition with Access 2002 installation media and follow the directions. After MSDE 2000 has installed, restart the computer.

Note   Each computer that has MSDE 2000 installed must have a valid license for Access 2002 or an Office XP edition with Access 2002.

Starting (and Stopping) MSDE 2000

Unlike Access 2002, MSDE 2000 must be started before applications can begin using it. There is rarely a need to stop MSDE 2000 unless it is running on a computer with limited memory or you are performing some type of advanced debugging. MSDE 2000 can be started (and stopped) by using the SQL Server Service Manager or the Administrative Tools applet in Control Panel.

Note   If you already have SQL Server 2000 installed on a computer, you cannot use MSDE 2000 (for example, using the SQL Server Services Manager will start SQL Server 2000, not MSDE 2000).

To start (or stop) MSDE 2000 by using the SQL Server Services Manager

  1. Double-click the MSSQLServer icon in the task bar notification area. The SQL Server Service Manager window is displayed.

    Note   If you don't see the MSSQLServer icon in the task bar notification area, you can also try looking for the Service Manager icon in the Startup menu (Start menu, All Programs command).

  2. In the Services list, click SQL Server.

  3. Click Start/Continue (or Stop).

  4. In the Services list, click SQL Server Agent.

  5. Click Start/Continue (or Stop).

  6. If you want MSDE 2000 to automatically start each time you start your operating system, select the Auto-start service when OS starts check box for the SQL Server and SQL Server Agent services.

To start (or stop) MSDE by using the Administrative Tools applet in Control Panel

  1. Open the Administrative Tools applet in Control Panel. The Administrative Tools window appears.
  2. Open the Services applet. The Services window appears.
  3. In the Services (Local) pane, open the MSSQLSERVER service.
  4. On the General tab, click Start (or Stop).
  5. In the Services (Local) pane, open the SQLSERVERAGENT service.
  6. On the General tab, click Start (or Stop).
  7. If you want both services to automatically start each time you start Windows, on the General tab for each service, in the Startup type list, select Automatic.

For more information, see the "About SQL Server 2000 Desktop Engine" and "Install and configure SQL Server 2000 Desktop Engine" topics in Access 2002 Help.

Securing MSDE 2000

MSDE 2000 uses two security authentication modes: Windows Authentication Mode and Mixed Mode. In Windows Authentication Mode, a user can connect to MSDE 2000 by using a Microsoft Windows NT® 4.0, a Microsoft Windows® 2000, or a Microsoft Windows XP user account. In Mixed Mode, users can connect through either Windows Authentication Mode or SQL Server Authentication (a name and password valid only for connecting to MSDE 2000). Users who connect through a Windows NT 4.0, a Windows 2000, or a Windows XP user account can use trusted connections in either Windows Authentication Mode or Mixed Mode.

The default authentication mode for MSDE 2000 is Windows Authentication. MSDE 2000 installs with a built-in system administrator (SA) user account. However, because SQL Server Authentication is disabled by default, you cannot access the built-in SA account after a typical MSDE 2000 installation.

Under some very limited circumstances, you may need to enable SQL Server Authentication: for example, MSDE 2000 is running on Microsoft Windows 98, you need to connect to MSDE 2000 over a computer network that is not using Windows domains, or for some reason your Windows account cannot be added to the computer that is hosting MSDE 2000.

If you want to enable SQL Server Authentication, you must do so with a command parameter during installation of MSDE 2000, or you must modify the registry after installing MSDE 2000. For information on enabling SQL Server Authentication with MSDE 2000, see HOW TO: Connect to Microsoft Desktop Engine.

Caution   If you enable SQL Server Authentication with MSDE 2000, be aware that the SA user account's password is blank by default, which presents a possible security risk. For information on how to change the SA user account's password, see HOW TO: Verify and Change the System Administrator Password by Using MSDE.

Getting Help for MSDE 2000

Help for MSDE 2000 is not included during installation; however, you can download the MSDE 2000 Help as part of Microsoft SQL Server 2000 Books Online (Updated).

Upsizing Microsoft Access 2002 Solutions to MSDE 2000

You can move the data from your existing Jet 4.0 databases into MSDE 2000 by using the Upsizing Wizard. To use the Upsizing Wizard, click on the Tools menu in Access, point to Database Utilities, and then click on Upsizing Wizard. When the wizard launches, follow the on-screen directions.

Note   The Upsizing Wizard does not make any design or logic changes to your application. It may be necessary to change the design of your application to take advantage of the MSDE 2000 database and see the benefits of a client server application.

Using Microsoft Access 2002 to Create a New MSDE 2000 Database

You can use Access 2002 to create an Access project (.adp) file. This file contains information that is used to create and connect to an MSDE 2000 database.

To use Access 2002 to create a new MSDE 2000 database

  1. In Access 2002, click New on the File menu.

  2. In the New File task pane, click New Project (New Data). The File New Database dialog box appears.

  3. Locate a folder in which you would like to save the resulting Microsoft Access project (.adp) file.

  4. In the File name box, type the name of the .adp file, for example, SalesMSDE2000, and then click Create. The Microsoft SQL Server Database Wizard dialog box appears.

  5. Type (local) in the What SQL Server would you like to use for this database list.

    Note   This assumes that you are running this procedure on the computer on which MSDE 2000 is installed. If you want to connect to a remote MSDE 2000 database, replace (local) with the name of the database server.

  6. Check the Use Trusted Connection box.

  7. Type Sales in the What do you want to name your new SQL Server database box, click Next, and then click Finish.

  8. Create tables, functions, views, stored procedures, and so on for the new database. Use the features of Access 2002 to work with any new database objects you create.

Using Microsoft Access 2002 to Manage an Existing MSDE 2000 Database

You can also use Access 2002 to connect to an existing MSDE 2000 database.

To use Access 2002 to connect to an existing MSDE 2000 database

  1. In Access 2002, click New on the File menu.

  2. In the New File task pane, click New Project (Existing Data). The File New Database dialog box appears.

  3. Locate a folder in which you would like to save the resulting Microsoft Access project (.adp) file.

  4. In the File name box, type the name of the .adp file, for example, LocalMSDE2000, and then click Create. The Data Link Properties dialog box appears.

  5. On the Connection tab, type (local) in the Select or enter a server name box.

    Note   This assumes that you are running this procedure on the computer on which MSDE 2000 is installed. If you want to connect to a remote MSDE 2000 database, replace (local) with the name of the database server.

  6. Click the Use Windows NT Integrated Security option.

  7. Select a database in the Select the database on the server list.

    **Caution   **You should not select any of the four system databases master, model, msdb, or temp. These system databases are used primarily by MSDE 2000 for internal management tasks only. Manually modifying any of these four system databases could jeopardize the integrity of MSDE 2000.

  8. Click Test Connection.

  9. If the test connection succeeded, click OK and close the Data Link Properties dialog box.

  10. You can now work with the MSDE 2000 database by using the features of Access 2002. Additionally, on the Database Utilities submenu of the Tools menu, you now have management options to backup, restore, transfer, copy, or drop the database (assuming you have rights on the server to do so).

Distributing Solutions Based on MSDE 2000

The recommended approach to distributing Access 2002 solutions based on MSDE 2000 is to:

  1. Develop and test the Access 2002 and MSDE 2000 solution on a single development computer.

  2. Transfer the MSDE 2000 database to a central workgroup or network computer that has MSDE 2000 installed.

  3. Make sure that the client .adp file references the transferred MSDE 2000 database.

  4. Distribute a copy of the client .adp file to each client computer that needs to interact with the MSDE 2000 database.

    Note   Each client computer that has a client .adp file installed must have a valid license for Access 2002 or an Office XP edition with Access 2002.

You will discover how to do this in the following sample walkthrough.

Sample Walkthrough: Creating and Deploying an Access 2002 Project and MSDE 2000 Database

In this sample walkthrough, you will:

  1. Upsize the Access 2002 Northwind sample database to an Access 2002 project and MSDE 2000 database on the same computer.
  2. Transfer the MSDE 2000 database to a remote computer.
  3. Ensure that Access 2002 project is redirected to the MSDE 2000 database on the remote computer.
  4. Distribute the Access 2002 project file to users.

To complete this sample walkthrough, you must first install Access 2002 and MSDE 2000 on the same computer. A separate central workgroup or network computer must have only MSDE 2000 installed.

Open the Northwind Access 2002 database

Start by opening the Access 2002 Northwind sample database:

  1. Start Access 2002.

  2. On the Help menu, point to Sample Databases, and click Northwind Sample Database.

    **Note   **You may be prompted to install the Northwind database from your Access 2002 or Office XP installation media.

  3. The Northwind database opens. If the Welcome form appears, click OK to close the form.

  4. If the Main Switchboard form is displayed, click Display Database Window to close the form.

Upsize the sample Access 2002 database to an Access 2002 project and MSDE 2000 database

Caution   Always be sure to back up your database files before upsizing them.

  1. On the Tools menu, point to Database Utilities, and then click Upsizing Wizard. The Upsizing Wizard dialog box appears. Click Next to create a new database.

    Note   The existing Access .mdb database file is left intact.

  2. In the What SQL Server would you like to use for this database list, click (local).

  3. In the Please specify the login ID and password of an account with CREATE DATABASE privileges on this server area, accept the default setting of Use Trusted Connection.

  4. In the What do you want to name your new SQL Server database, accept the default name of NorthwindSQL, and then click Next.

  5. Move all of the tables from the Available Tables list to the Export to SQL Server list by clicking the double-arrow (>>) button, and then click Next.

  6. Accept the default table attribute and data options by clicking Next.

  7. Create a new Access project file by clicking Next.

  8. Open the new Access project file by clicking Finish.

  9. Wait while the Upsizing Wizard upsizes the database.

  10. After the Upsizing Wizard finishes, an Upsizing Report appears.

  11. Close the Upsizing Report to display the Access project.

Transfer the MSDE 2000 database to the separate central workgroup or network computer

Caution   Before completing the tasks in this section. Make sure that you have installed SQL Server 2000 Desktop Engine (MSDE) Service Pack 2 or later on all computers with MSDE 2000 installed. You can obtain this service pack from the Microsoft SQL Server Web site/. The transfer action may produce errors if you do not install the service pack.

  1. Ensure that both computers that are running MSDE 2000 have their Distributed Transaction Coordinator service running. To check this service's status, follow the directions in the "Starting (and Stopping) MSDE 2000" section above. The service is titled Distributed Transaction Coordinator in both Service Manager and the Administrative Tools Services console.
  2. With the NortwindSQL.adp file open in Access, on the Tools menu, point to Database Utilities, and click Transfer Database. The Transfer Database dialog box appears.
  3. Type the name of central workgroup or network computer that has MSDE 2000 installed in the What SQL Server would you like to use for this database list.
  4. If the MSDE 2000 installation accepts a trusted connection, select the Use Trusted Connection check box. Otherwise, clear the Use Trusted Connection check box and type the required login ID and password in the Login ID and Password boxes.
  5. Leave the default database name of NorthwindSQL in the What do you want to name your new SQL Server database box.
  6. Leave the Include Data check box selected. This ensures that the data will be transferred to the new database.
  7. Click Next.
  8. Click Finish. Wait while the database is transferred.

Ensure that the Access 2002 database project references the MSDE 2000 database on the remote computer

  1. With the NorthwindSQL.adp file still open in Access, on the File menu, click Connection. The Data Link Properties dialog box appears.
  2. On the Connection tab, make sure that the server name in the Select or enter a server name box, the database login parameters, and the database name in the Select the database on the server list are correct, and then click OK.
  3. Close the NorthwindSQL.adp file and quit Access.

Distribute the Access 2002 database project file to users

  1. Distribute the NorthwindSQL.adp file using media such as a network file share or a compact disc.
  2. Users can now copy the NorthwindSQL.adp file from the media to their local computers and open the Northwind.adp file using Access 2002.

Conclusion

In this article you learned:

  • When to use Microsoft Jet or MSDE 2000 for a specific Access 2002 solution.
  • How to install and run MSDE 2000.
  • How to upsize an Access 2002 database to an MSDE 2000 database.
  • How to use Access 2002 to create new MSDE 2000 databases and connect to existing MSDE 2000 databases.
  • How to distribute Access 2002 solutions based on MSDE 2000.
  • How to use Access 2002 to create an MSDE 2000 database and distribute the resulting solution to end users.