The Microsoft Data Engine | Programming Microsoft Access 2000 (Microsoft Programming Series)

You can use MSDE to build solutions on single-processor personal computers running Microsoft Windows 9x operating systems or Microsoft Windows NT systems. Solutions run on MSDE are appropriate for small workgroups, but you can readily transfer MSDE solutions to Microsoft SQL Server 7 so that your solutions can scale to multiprocessor clusters running the most advanced Windows operating systems. These advanced systems include Windows NT Server Enterprise Edition and higher-end Windows 2000 operating systems.

MSDE solutions are completely compatible with those developed for SQL Server 7. You can prototype a solution with MSDE and .adp files on your local computer, and then later you can transfer the database tables, views, and stored procedures to SQL Server for testing, refinement, and rollout. You can distribute to testers and users the original .adp files with their data link changed to point at the SQL Server database instead of your prototype MSDE-based system. Such applications are suited to serving enterprise, as opposed to departmental, needs.

MSDE vs. Jet

Jet-based Access solutions are basically easier to manage, more compatible with solutions built with prior Access versions, and consume fewer resources. MSDE-based Access solutions require that you have some familiarity with new data types, different rules for developing queries, and database administration techniques. In return, MSDE offers superior data recovery, built-in transaction logging, integrated Windows NT security, and the potential for huge database capacity (when you migrate to SQL Server 7). You can use the Access user interface to build forms and reports for solutions with either database engine. Because of the inherent client/server design of MSDE solutions, you must follow certain rules for using MSDE and the user interface. You can also program solutions with VBA and ADO for either database engine.

Both MSDE and Jet support declarative referential integrity, so you can graphically declare relations between tables. However, while Jet supports cascading updates and deletes at the engine level, with MSDE you must implement these features programmatically in SQL using triggers. You open a trigger template for a table by right-clicking on the table in the Database window and choosing Triggers. You then click New to open the template. (SQL Server Books Online includes syntax rules, samples, and general background on developing triggers.)

If you need only a full restore from a backup file, you choose Tools-Database Utilities-Restore in an Access Project. This is similar to maintaining backup copies of Jet database files and restoring them from copies. Only MSDE supports recovery to a point in time from a log file. You must program this type of restore with Transact-SQL, which Access 2000 online help supports. Online help includes sample scripts and syntax for recovering data in a table to a specific point in time from its log file.

NOTE
If you find yourself managing a collection of tasks that require Transact-SQL, you might want to obtain a full copy of SQL Server 7 (instead of the free MSDE package with the same core database engine code). The SQL Server Enterprise Manager provides a large collection of wizards and a graphical user interface that eliminates the need for Transact-SQL code for tasks such as backing up and restoring.

Jet database files are typically smaller than comparable MSDE-based solutions. The Northwind.mdb file ships at about 2 MB. This includes all the database tables, other database objects, and code. The MSDE-based version of Northwind includes a database (.mdf) file and an Access Project (.adp) file. These files total about 3.7 MB. MSDE solutions automatically maintain log files that help in the event of a recovery. However, Jet databases must pass their databases across a network in multi-user solutions. MSDE-based solutions perform all database processing at the server and pass their return sets across a network. This can reduce network traffic and speed performance. Both Jet and MSDE permit databases as large as 2 GB, but MSDE solutions can migrate easily to SQL Server 7 for support of databases in excess of 1 million terabytes (TB). SQL Server 7 scales up its performance as you run on computers with more processors, but Jet does not substantially escalate performance as you add processors.

NOTE
MSDE, unlike its older sibling SQL Server 7, is tuned for working with small workgroups.

Both Jet and MSDE have their place. MSDE is the ideal tool if you envision using SQL Server for future versions of your application. When MSDE runs on Windows NT systems, it can offer integrated operating system and database security, which can reduce your security administration burden. MSDE also offers point-in-time recovery. Jet is more appropriate for non-mission critical applications for which ease of development is an overriding consideration. Jet solutions have smaller footprints and can be a better choice if you have memory or disk constraints. Because only a marginally updated Jet engine ships with Access 2000, it offers the highest compatibility with Access 97 and earlier versions.

Installing and Configuring MSDE

MSDE is an optional database engine for Access developers and users. You can install it by running SetupSQL.exe from your Office 2000 CDs. No special operating system preparation is necessary for installation on a Windows 9x system. Users of Windows NT 4 must perform two steps to prepare for MSDE installation. First, they must install Service Pack 3, and then they must run hotfix.exe from the Office 2000 CDs. Windows NT 4 with Service Pack 4 requires no special setup requirements.

After installing MSDE, you must start the engine before you can use it. For Windows 9x computers, you can perform this with the following steps:

You can select the Auto-Start Service check box if you want MSDE to start automatically when the operating system starts. If you do not select this option, you must repeat the above steps whenever you use MSDE.

Windows NT users can launch MSDE just like any other Windows NT service. On the Control Panel, you double-click the Services icon and then choose MSDE Service. Click the Start button in the Services dialog box to launch the service. Then click Startup and select a Startup Type (Automatic, Manual, or Disabled).

After installation and startup, an icon representing SQL Server Service Manager appears on your Windows task bar. You can use this icon to start, pause, or stop MSDE. You can also use it to open Service Manager so you can perform the same functions for the Microsoft Distributed Transaction Coordinator and Microsoft SQL Server Agent.

It is normal to require a user to enter a user ID and a password before opening a file for a client/server database. MSDE lets you manage login security with individual and group accounts for specific databases or with integrated, pass-through security from Windows NT. The MSDE defaults are sa for the user ID and blank for the password. You can choose Tools-Security-Database Security to establish security accounts for individual databases. Windows 9x users can use MSDE security accounts only for individual databases. MSDE database roles are comparable to groups in Access. An individual can belong to two or more different roles, and individuals inherit the most permissive security of any role to which they belong.

When you run MSDE from Windows 9x, it supports TCP/IP and Multi-Protocol, but not Named Pipes. However, most clients attempt to connect to an MSDE server computer via Named Pipes by default. If you want a client computer to establish an alias for the MSDE server that connects via either TCP/IP or Multi-Protocol, choose the MSDE Client Network Utility command from the Msde menu off the Start button.