The Microsoft Data Engine (MSDE) is a free version of the Microsoft SQL Server. As of this writing, MSDE ships exclusively with Microsoft Office 2000 and Access 2000. In this article, we’ll compare MSDE with two alternative Microsoft database engines. We’ll also explain how to install MSDE and illustrate how to use it with the new .adp (Access data projects) file type for Access 2000. In addition, we’ll demonstrate ActiveX data object (ADO) techniques for accessing MSDE data sources from other Office components.

MSDE advantages
MSDE offers four main benefits. First, road warriors can easily use it with Office 2000 while they’re traveling. Second, departments within an enterprise can use MSDE as a database server on a LAN. (Microsoft claims MSDE performance degrades markedly after more than five or six concurrent users.) Third, corporate database developers can prototype systems on their workstation before deploying them to the main SQL Server service for a department or enterprise. Fourth, there is a redistributable, royalty-free version of MSDE. Since Access also has a royalty-free, redistributable version, developers can ship solutions using Access and MSDE that can run on workstations without either installed. Developers must own the Microsoft Office 2000 Developer Edition to obtain a license to these royalty-free versions.

How does MSDE compare to other Microsoft databases?
Since MSDE is a version of SQL Server, you can understand MSDE better by comparing it with the full SQL Server version. In addition, because MSDE ships with Office 2000, you can appreciate its relative benefits by comparing it with the Microsoft Jet Database Engine (Jet).

MSDE is a de-featured version of SQL Server that does not ship with the Enterprise Manager, Books Online, or English Query capabilities. The maximum size of an MSDE is 2 GB. However, the full SQL Server 7.0 version enables individual files to grow to 32 TB, and it permits up to 32,767 files per database. Beyond that, MSDE supports up to two symmetric processors on Windows NT systems (instead of the up to 32 multiple processors that SQL Server 7.0 Enterprise Edition can recognize).

MSDE has its own special attractions. High on the list is the ease with which you can attach databases initially built for MSDE to a full SQL Server service. There is no need to upsize the database or copy individual tables, indexes, and constraints in a database from MSDE to a full SQL Server. MSDE also has a smaller hard drive footprint than SQL Server. This makes it more suitable for environments where it isn’t cost effective to deploy vast computer resources, such as individual road warriors and developers as well as small workgroups within a large organization.

Compared with the Jet engine, MSDE has less backward compatibility. The new Jet 4.0 engine will run many legacy databases, but MSDE is a whole new engine for Microsoft Office applications. It will not run applications built for Jet 3.5x and earlier Jet versions without a significant redesign effort. In addition, the new .adp file type has a user interface that differs in some important areas from prior versions of Access (for example, there is no Queries collection of database objects—just views and stored procedures). The details of database security administration are also substantially different for MSDE versus Jet.

Installing MSDE
MSDE does not install with the standard Office 2000 setup program. To install MSDE, you have to run SetupSQL.exe from the \SQL\X86\Setup folder on the Office 2000 CD-ROM. Running the setup program successfully installs MSDE on any Windows 9x computer. You must upgrade a Windows NT computer with Service Pack 3 to Service Pack 4 before you attempt to run the install program.

Even after running the install program, you must still start the MSDE service. To do so, choose the Service Manager from the MSDE menu to open the SQL Server Service Manager dialog box. Then, select MSSQLServer and click Start/Continue. If a computer runs MSDE all or most of the time, you can configure MSDE to load automatically whenever the machine starts by selecting the Auto-start check box.

By default, the MSDE installation sets the Server Network Utility to work with Multiprotocol and TCP/IP on Windows 9x computers. However, other computers trying to connect to MSDE on a Windows 9x computer will use Named Pipes by default. Therefore, you need to change the network client settings for these computers from Named Pipes to Multiprotocol and TCP/IP. The default network client settings are acceptable when you install MSDE on a Windows NT computer.
After installing MSDE on a computer, you should be able to connect to an MSDE server from anywhere on your network—just like any SQL Server service. This means that any legacy application, such as Access 97, can link to MSDE with an ODBC connection. However, the connection won’t function unless you have a new SQL Server ODBC driver.In order for the ODBC link to work from a legacy application, you need version 3.7.0623 or higher of the SQL Server ODBC driver. Since Microsoft ships multiple versions of the driver with the same name (SQLsrv32.dll), determining whether you have the correct driver installed on a workstation can be confusing. The 3.7.0623 version has a date of 11/20/1998 and a size of 497 KB. You can get the correct driver by copying the .dll file from any other computer on your network or by downloading and installing the 2.1 version of the Microsoft Data Access Components from at Microsoft’s Universal Data Access Web site .
What are ADP files?
While MSDE doesn’t ship with Enterprise Manager, Microsoft does offer a user-friendly interface in the form of an Access project. Microsoft introduced a new file type (.adp) to hold Access projects. This new file type is tailored to work with MSDE and other SQL Server-style databases (from now on we’ll use the term SQL Server databases).

Access projects seamlessly integrate remote SQL Server data sources with Access forms, reports, modules, and data access pages that are stored locally in the .adp file. Access projects offer faster, less resource-intensive data access to SQL Server data sources. Users don’t have to link to a remote data source or cache data locally by linking an ODBC data source, as was the case with prior Access versions.

Another important advantage of Access projects is their data definition capabilities. For example, you can create a new SQL Server database with a wizard when you create a new project. In addition, Access projects enable you to add tables, views, stored procedures, and database diagrams to a new or existing SQL Server database (as long as your logon supports these permissions).

Using the Microsoft SQL Server Database Wizard, we’ll create a new SQL Server database named ADP1SQL on the Cabxli server with the sa (system administrator) logon ID. Once you enter that information, click Next, then Finish, to launch the database creation process. After a short wait, control returns to the database window. The wizard automatically creates a connection between the ADP1.adp file and the ADP1SQL database.

The Microsoft SQL Server Database Wizard appears after you select Project (New Database) from the General tab of the New dialog box. Designate a file name for your Access project and click Create in the File New Database dialog box. Access 2000 gives you two routes to the New dialog box with the Project (New Database) icon. First, you can choose Access database-wizards, pages, and projects when starting Access. Second, you can click the New toolbar button from an existing .mdb or .adp file.

When creating an Access project, you can elect to connect it with an existing database. Clicking the Project (Existing Database) button in the New dialog box starts this process. The Data Link Properties dialog box allows you to designate an existing database to serve as a data source for a new Access project. Our New Access project will link to the NorthwindCS database on the Cabxli server.

The NorthwindCS database is a client/server version of the traditional Northwind Jet database that ships with Access. Access 2000 ships with two Northwind sample databases—one in the familiar .mdb format for Jet database engines and the other in an .mdf format for SQL Server database engines. Access 2000 also has a sample NorthwindCS.adp file. The first time you open this file, a script will automatically build a connection to the NorthwindCS database if it is on the local server. Otherwise, NorthwindCS.adp will initially open disconnected. However, you can select File, then Connection to display the Data Link Properties dialog box, which lets you connect the adp file to NorthwindCS on a remote SQL Server service.
The answer is: It depends! You can connect to an existing database on a local MSDE server without a network connection. This capability enables road warriors and developers working from an unconnected workstation to work with an existing database. For example, these workers can perform queries against the database and create within it new database objects, such as tables and stored procedures. However, you cannot successfully invoke the Microsoft SQL Server Database Wizard to create a new SQL Server database without a network connection.
Using ActiveX data objects with SQL Server databases
Access projects are very convenient for working with SQL Server databases. However, if the staff of a department or enterprise wants to connect with SQL Server databases via other Microsoft Office components, such as Word or Excel, then this option is not available. One way in which nonAccess Office components can include data from an SQL Server database is with ActiveX data objects (ADO). ADO is a major element in Microsoft’s universal data access strategy (read more at Microsoft’s Universal Data Access Web site ). Professional developers will need to become familiar with ADO to stay current with Microsoft’s data access initiatives.

Developers typically create advanced ADO custom solutions for departments or coach power users in end-user departments to create simple solutions. Any ADO solution will normally perform two steps to extract data from a SQL Server database. First, it will make a connection with the remote data source. Second, it will define a recordset based on a subset of the data in the remote source.

A custom Office application can encompass an ADO solution as it performs some coordinated functions. For example, an Office application can gather information from a user to specify the data to extract from the remote data source. After the ADO solution develops a return set, the Office application can display the return set in a document.

You can expose a UserForm with the Show method. To make the form appear whenever a document opens, invoke the form’s Show method from the document’s Open event. The following event procedure illustrates the syntax for showing a UserForm named frmLookupName when a document opens:

Private Sub Document_Open()
 frmLookupName.Show

End Sub

Clicking Find Extension launches two tasks, and the command button’s click event procedure reflects these (see below). First, the procedure invokes the mysample subprocedure from the ThisDocument folder. Second, it hides the form. The ThisDocument folder is the default folder within a Word project for containing procedures. Including the folder name before the procedure name guards against any confusion about which mysample procedure to use. Recall that VBA allows the re-use of procedures in different folders within the same project. In addition, the Normal template can have a procedure named mysample.

Private Sub _

cmdFindExtension_Click()
 ThisDocument.mysample
 frmLookupName.Hide

End Sub

Listing A shows the logic and syntax for the mysample procedure. This procedure has three major subtasks. First, it makes a connection to the NorthwindCS database on the Cabxli server, which is running MSDE. Second, the procedure extracts a record from the Employees table in the data source that has the same last name as the one in the text box on the UserForm. Third, the procedure writes a sentence to the Word document with the extracted contents from the data source.
Sub mysample()Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim str1 As String
Dim strSentence As String
‘Open connection to NorthwindCS database
‘on cab2200 server
 Set cnn1 = New ADODB.Connection
 strCnn = “Provider=sqloledb;” & _
 “Data Source=cabxli;” & _
 “Initial Catalog=NorthwindCS;” & _
 “User Id=sa;Password=”
 cnn1.Open strCnn
‘Syntax for using msdasql instead of
‘sqloledb provider with connection string
‘ cnn1.Provider = “msdasql”
‘ str1 = “Driver={SQL Server};” & _
 “Server=cabxli;Uid=SA;Pwd=;” & _
 “Database=NorthwindCS”
‘ cnn1.Open str1
‘Syntax for using msdasql provider with dsn
‘ cnn1.Open “dsn=NorthwindCSOnCABxli;Uid=sa”
‘Open employee table
‘Get extension for employee last name on form
 Set rst1 = New ADODB.Recordset
 rst1.CursorType = adOpenForwardOnly
 rst1.LockType = adLockReadOnly
 str1 = “SELECT * FROM Employees ” & _
 “WHERE LastName = ‘” & _
 frmLookupName.txtLookupName & “‘” & “”
 rst1.Open str1, cnn1
‘Insert selected database content on
‘Word document
 str1 = rst1.Fields(“FirstName”) & ” ” & _
 rst1.Fields(“LastName”) & _
 ” has extension ” & _
 rst1.Fields(“Extension”) & “.”
 ActiveDocument.Content.InsertBefore str1
End Sub

Listing A uses one syntax for making a connection to a SQL Server database, but it shows two alternative ones as well (these are commented out). Each style has its benefits. The first connection example relies on the Sqloledb provider. Microsoft optimized this OLE DB provider for use with SQL Server databases (versions 7.0 and 6.5). The second connection sample illustrates how to invoke the Msdasql provider. This provider works with any ODBC data source. Therefore, it is particularly appropriate whenever your applications invoke another data source besides, or in addition to, an SQL Server database. The third connection sample relies on a DSN (data source name) named NorthwindCSOnCABxli. Developers may want to coach power users with this technique because of its simplified syntax.

The final two sections of the mysample procedure illustrate how to reference a UserForm control value and write contents from a return set to a Word document. The WHERE clause for the SELECT statement specifies which employee to include in the return set. This clause sets the LastName field equal to the value of the text box on the UserForm. The procedure concludes by using the InsertBefore method to write a short sentence at the very beginning of the Word document. It displays a simple string built from fields in the return set to the SQL statement for the Recordset object.

Rick Dobson, Ph.D., and his wife operate a development and training consultancy. He is the author of the best-selling book Programming Microsoft Access 2000 for Microsoft Press. Rick is a regular contributor to TechRepublic and numerous computer periodicals. In addition, he has presented training sessions and seminars on Access and Web development topics in Australia, Canada, the United Kingdom, and throughout the United States. Rick is a Microsoft Certified Professional and a Microsoft Certified Trainer. You can reach Rick at either of the two Web sites that his practice maintains (www.programmingmsaccess.com and www.cabinc.net ).

The authors and editors have taken care in preparation of the content contained herein, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.