October 1999 Build Client/Server Databases With MSDE Microsoft Data Engine and Access projects add substantial scalability to your Office solutions. by Rick Dobson

Reprinted with permission from Visual Basic Programmer's Journal, October 1999, Volume 9, Issue 10, Copyright 1999, Fawcette Technical Publications, Palo Alto, CA, USA. To subscribe, call 1-800-848-5523, 650-833-7100, visit www.vbpj.com, or visit The Development Exchange.

What you need: Access 2000 MSDE

In this article, I'll introduce you to MSDE, then show you a series of form samples that illustrate how Access projects can present data in a business solution. The samples demonstrate how to bind both a simple main form and a main/sub form to an MSDE data source. In the process, you'll find out how to programmatically make forms read-only for selected users. I'll also show you two approaches to setting the data source for a form at run time and how to limit the number of records in the snapshot data cache behind a form. Because you can't just copy the database sample and Access project to your computer and run the samples, you'll learn a SQL Distributed Management Objects (DMO) procedure that automates loading the sample MDF database file to your local MSDE (see the sidebar, "Load the Sample Access Project").

Solutions developed using Access projects and MSDE or SQL are highly scalable—from a single processor laptop and a database maximum size of 2 GB to a 16-processor cluster of servers that can comfortably manipulate multiterabyte databases. MSDE's scalability is limited in isolation (for example, it can accommodate up to two processors on a Windows NT box, and it has a 2 GB database size limit). However, MSDE has the same database format as SQL Server, so you can build a prototype (or a starter) solution using MSDE. Then, when the application grows in popularity or you decide to roll it out from its prototype test environment, you can migrate it easily to any version of SQL Server.

Although MSDE represents a significant advance over Jet, it steps up the database administration requirements. A wizard makes it a snap to create a database, but unlike Jet, you need to perform a separate step to create the database. You can't just open an MDB file and work in the corresponding database; Access projects must connect to a database maintained by a database server, such as MSDE. If the server isn't running, your Access project disconnects from its data source. When you use an Access project with SQL Server or MSDE, it stores tables, views, stored procedures, and database diagrams with the database server, and it stores forms, reports, data access pages, macros, and modules with the Access project. The ADP file type seamlessly integrates these two resource sets.

Install and Configure MSDE
A standard version of MSDE ships with Office Premium and Microsoft Office 2000 Developer (MOD). Unfortunately, it doesn't install through the standard setup program. You have to run Setupsql.exe from the Sql\X86\Setup folder of the CD1 installation disc. MSDE installs on a Windows 9x or NT computer.

After you install MSDE, you might need to configure it. For example, you can't access a SQL Server database unless the server is running, but MSDE doesn't start automatically on a Win9x computer. You can run the SQL Server Service Manager to start MSDE whenever you need it, or set your computer so MSDE runs automatically every time your computer boots. The SQL Service Manager appears as a new icon toward the right edge of the status bar. MSDE starts automatically on an NT4 computer.

The MSDE installation procedure on a Win9x computer sets the Server Network Utility to work with multiprotocol and TCP/IP. By default, NT computers attempt to connect to MSDE on a Win9x computer with the Named Pipes network protocol. So, you need to change the network client settings for NT computers from Named Pipes to Multiprotocol and TCP/IP for their connection to MSDE on a Win9x computer. The Client Network Utility (on the MSDE and SQL Server 7 menu) provides a user interface for accomplishing these changes. You don't need this configuration step for MSDE running on NT computers.

Other computers on a LAN can connect to databases managed by MSDE, even if they're using older technologies such as Open Database Connectivity (ODBC). This is particularly convenient for enabling legacy versions of Office to connect with MSDE. However, you need version 3.7.0623 or higher of the SQL Server ODBC driver. Because Microsoft gives the same DLL name to different driver versions, it can be confusing to determine whether you have the proper driver. Use the Drivers tab of the ODBC Control applet to determine if you have version 3.7.0623 or later. If you have an out-of-date driver, you can correct the situation as easily as copying the correct one over it (say, from another computer). A more comprehensive approach is to install the 2.1.1.3711.11 (GA) version of the Microsoft Data Access Components (MDAC) from www.microsoft.com/data/. The MDAC has the most up-to-date driver.

MOD includes a second version of MSDE: a self-extracting executable program. Unlike the standard version that ships with Office 2000 Premium, this one is redistributable, so you can deploy it with your custom solutions similar to the way you deploy the Access runtime version. To deploy the redistributable version, you must silently expand the redistributable MSDE, msdex86.exe, on a user's hard drive ("silent expansion" requires no user intervention). Use the Package and Deployment Wizard to accomplish this. First add the ADP file for your Access project, the MDF database file, and the redistributable MSDE to the files the wizard deploys. Second, start the MSDE installation after your setup program completes all other tasks. Finally, use SQL DMO programming to start your redistributable MSDE and attach your custom database file to it (see Links for more information).

MSDE vs. Other Data Engines
The best way to learn about MSDE is to compare and contrast it with its alternatives: Jet and SQL Server 7. First, take a look at some similarities between Access projects (based on MSDE or SQL Server databases) and Access database files (based on Jet databases). The good news about MSDE is that Access projects offer an integrated GUI for creating databases. When you start Access 2000, the General tab of the New dialog offers four icons representing options for working with databases. The last two icons enable you to connect a new project to an MSDE or SQL Server database. Double-clicking on the Project (New Database) icon opens the Microsoft SQL Server Wizard. Use this wizard to create a new database for a new Access project. Selecting the Project (Existing Database) icon and clicking on OK creates a new Access project, then presents the Data Link Properties dialog. Use this dialog to select an existing MSDE or SQL Server database your new Access project connects to.

You can base forms and reports in Access projects on the underlying database connection, and you can use the AutoForm tool to create a new bound form based on a table in an MSDE database. You can also base a form on a table from Design view or programmatically at your preference. The Access project includes a graphic tool for building views that resemble the one for building queries in traditional Access database files.

Although the similarities between Access projects and Access database files are helpful in getting started, there are many differences between them. Access projects don't have a queries collection. Instead, they have collections of views and stored procedures. Views neither support the Order By SQL phrase nor enable action queries or parameter queries. Stored procedures support the query types not available with Views, but Access has no GUI for building stored procedures. If you're migrating from Jet to MSDE or SQL Server, try to become familiar with writing stored procedures. Stored procedures are actual programs stored on the server that can contain a combination of SQL queries along with branching and looping constructs, variable assignments, database transaction control, and so on.

There are also some significant variations in table data types between Jet and SQL Server. The text data type in Jet corresponds to the SQL Server varchar, nvarchar, char, and nchar data types. However, Jet memo data types are similar to the SQL Server text and ntext data types. The n prefix for SQL Server data types explicitly specifies them as Unicode rather than ANSI format. This is especially helpful if you represent different language character sets in your solutions. A potentially confusing point is how Jet and SQL Server each use the same name to refer to different data types. A Jet Integer data type references a two-byte number. The same term (namely, Integer or int) in SQL Server designates a four-byte number.

SQL Server has more features and better scalability than MSDE. With MSDE, you must use the Access project interface to manage a database, because the SQL Server Enterprise Manager isn't available with MSDE. Also, the Access interface isn't as rich as SQL's Enterprise Manager. Unlike SQL Server, MSDE won't act as an online analytical processing (OLAP) server. In addition, MSDE doesn't enable Query Analyzer and English Query, and an MSDE database can't act as a Publisher for a transactional publication.

MSDE's limited scalability is more significant than its feature deficit. Microsoft claims to have tuned MSDE so it works best with up to a handful of concurrent users. One beta tester reported (off the record) acceptable results with as many as 40 users. The objective facts: MSDE enables only two symmetric multiprocessors instead of the four with standard versions of SQL Server and the 16 with clustering on the Enterprise version. Beyond that, MSDE databases are restricted to 2 GB instead of the multiterabyte-sized databases that SQL Server enables. However, the ease of migrating solutions from MSDE to SQL Server moderates the practical impact of these scalability differences.

Use Main and Main/Sub Forms
Building bound forms is basic to many Access applications. All versions of Access have had a simple AutoForm tool for creating a bound form with a single click. Even if the tool's output wasn't exactly what you needed, it often made a great starting place for customization. This same AutoForm tool handles MSDE data sources, and works nearly the same way. There is one minor difference in a form's behavior. If your form must enable updating as well as adding and deleting records, its underlying data source must include a primary key specification—unnecessary with Jet data sources.

	Figure 1 Bind to Client/Server Data. Click here.

Start using MSDE by creating a form with the AutoForm Wizard for the Categories table, using this article's sample database (see Figure 1 and download the code here). The wizard's output relates to a snapshot of the MSDE Categories table specified in the Access project's Data Link Properties dialog. The wizard sets the form's RecordsetType property to Updatable Snapshot (read/write), but you can override this setting at design time or run time. Using a Snapshot setting (read-only) for the form's RecordsetType property is one way to convert the form from a read/write to a form that enables data browsing only.

To make a form read-only for all logins with a User ID different from sa, embed the segment in a form's Open event procedure to empower the form to selectively update its underlying recordsource. When working with MSDE, you identify users by the User ID for the login used to connect with the data source. Notice that you access the User ID property from the OLE DB connection of the active Access project. The default read/write value for the RecordsetType property is 4. The code segment conditionally updates the default value to 3, which is the read-only Snapshot setting value:

Set cnn1 = CurrentProject.Connection
Const conSnapshot = 3
If cnn1.Properties.Item(_
   "User ID").Value <> "sa" Then
   Forms("frmCategoriesSecure") _
      .RecordsetType = conSnapshot
End If

The form in Figure 1 doesn't take advantage of any natural hierarchies in a data source. One way to capture data hierarchies is to use a main/sub form design, such as the one between an order and its line items. Using a main/sub form design can sometimes make working with pictures more efficient, because placing the picture in a sub form can save retrieval time from a remote SQL Server. However, this isn't the case with Access projects, because they load the data into a local cache for their forms. Access project forms retrieve and update data in the local cache, which automatically updates the server when necessary.

Figure 2 Present One Form Inside Another. Click here.

Compare Figure 1 to Figure 2, which shows a main/sub form version of Figure 1's main form. I placed the category picture in a sub form—it isn't camouflaged in any way. You can see its navigator buttons and its link field (CategoryID) for synchronization with the main form. The label for the sub form also shows. In a normal application, you might edit some of these settings for improved appearance, but showing them here highlights the sub form on the main form.

Constructing a main/sub form is a straightforward Access project task. First, remove the picture on your main form and save this form. Then, create another form with the picture and an ID value. This second form serves as the sub form. Its ID value matches a picture to the current record on the main form. Close and save the second form, but leave the first form open in Design view. Finally, drag the second form onto the main form. Unless the main and sub form recordsets are already related, you have to specify the relation by setting the Link Child Fields and Link Master Fields properties on the subform control.

Set a Form's RecordSource Property
When you use the AutoForm button to create a form, Access automatically sets the form's RecordSource property. This property is one of two that let you specify the data source for a form. The second one, the Recordset property, is new with Access 2000.

The RecordSource property is a string that denotes a table name, query name, or SQL statement. You can set this property at design time from the form's Property settings sheet, or you can achieve the same result at run time with Visual Basic for Applications (VBA). (Runtime settings override design time settings.) The RecordSource property enables one form to display data from multiple subsets, such as employees in different departments. You just have to assign the SQL statement for selecting employees in a particular department to the form's RecordSource property. Because the value for the property setting can be only a table name, query name, or a SQL statement, you can't run stored procedures, take advantage of ActiveX Data Objects (ADO) Command objects, or reference previously saved Recordset objects.

You can set the Recordset property with an ADO recordset, or a Data Access Objects (DAO) recordset when working with a Jet data source. You can't assign an OBDCDirect recordset to this property, because this property is available exclusively through VBA. With an ADO recordset and an MSDE data source, you have read/write access to the data behind a form based on a single table. If your form contains data from a one-to-many relationship, you must set the form's UniqueTable property to the "many" table in the relationship for read/write access to data on the one-side of the relationship. Otherwise, the form is read-only. Forms based on tables in many-to-many relationships are always read-only. Setting the Cursor Location property explicitly specifies the data cache for a form as local, but Access projects automatically perform this way. A table must have a primary key for a form to have update privileges for it. Setting a form's Recordset property at run time overrides any design recordsource setting.

Next, write a procedure that opens the form. Edit the Notes field to verify that users can update the underlying data exposed through the form. Opening the form is a two-part process. First, you define a recordset; in this case, based on the Employees table. However, any other appropriate source for a recordset can serve equally well. After creating a recordset, your code has to open the form that will use the recordset. Second, you assign the recordset to the form's Recordset property:

'Create the recordset for the 
'Employees form
   Set rst1 = New ADODB.Recordset
   rst1.CursorLocation = adUseClient
   rst1.Open "Employees", _
      CurrentProject.Connection, _
      adOpenKeyset, adLockOptimistic, _
      adCmdTable
      
'Open the Employees form and
'set its recordset property
   DoCmd.OpenForm "Employees"
   Set Forms("Employees").Recordset _
      = rst1

When working with client/server databases, limiting the amount of data the server passes to the client can impact performance dramatically. With the MaxRecords property, your application can set an upper limit for the number of records a server returns to a client. You can set this property through the user interface for individual forms or globally for all forms. Set a value globally from the Default MaxRecords textbox on the Advanced tab of the Tools | Options dialog. Use the MaxRecords property box for an individual form to set a maximum number of records for a specific form. In addition, you can set the MaxRecords property at run time.

You can also integrate the use of the RecordSource, Recordset, and MaxRecords properties with MSDE. In the top form, users can choose to populate a second form with data through either the RecordSource or Recordset properties by clicking on one of two command buttons. A textbox allows users to designate a number of records in the return set. With Access projects, this limit is 10,000 by default, but the sample application imposes a new lower default of 8,000. In addition, users can enter a new value to specify a lower or higher number of records. You can also open the form through a RecordSource specification. Users can click on the red Cancel button to terminate the download of any further data from a server. The bottom form demonstrates the form's appearance as it accepts a value from a local recordset. Notice the Cancel button isn't red, because the server already downloaded all the records. The form just links to the recordset cache when it opens through its Recordset property setting.

The Form_frmOpener2 class module is the code for the top form (see Listing 1 for the two form class modules' code). The event procedure for the last command button in the module illustrates how simple it is to open a form through its RecordSource property. If you're willing to accept the default RecordSource property setting, such as the one generated by the AutoForm button, you don't even require the second line. Notice the syntax for the second line encloses the table name in square brackets, indicative of the SQL Server syntax MSDE uses. The brackets are necessary because a blank space is inside the table name for the recordsource.

The next to last and top procedures in the Form_frmOpener2 class module demonstrate one approach to opening the form based on a Recordset object (see Listing 1). The call to LoadOrderDetailsRecordset2 in the cmdOpenRecordset_Click event procedure opens the recordset and the form based on the recordset. The event procedure orchestrates the appearance of an hourglass icon because there can be a silent, unmarked pause as the LoadOrderDetailsRecordset procedure opens the recordset for the form. The larger this recordset, the longer the pause. The event procedure for the RecordSource Open command button automatically converts the cursor to an hourglass, but this doesn't happen by default, because the application loads the local recordset for the form with data from MSDE.

Aside from managing form display, the chief purpose for the Form_frmOrderDetails class module is to assign a value to the form's MaxRecords property. This value is the default value of 8,000 or whatever number a user enters into the textbox on frmOpener2. After setting the MaxRecords property, the Load event procedure requeries the form to update the form from its prior MaxRecords property setting.

As you've seen, working with Access projects and MSDE databases seems familiar and easy, because the GUI for Access projects is similar to that for traditional Access databases. You can use Access forms, reports, and data access pages along with modules in ways that you already know. But don't let this familiarity lull you into a sense that there are no differences between Access database files and Access project files for MSDE or SQL Server databases; the differences range from significant changes to minor annoyances. Although this article mentions selected data type differences, many other differences, such as the absence of stored queries in MSDE, can force changes in the way you tackle tasks. Despite some drawbacks to MSDE, you'll find the benefits of developing with Access projects and MSDE far outweigh any disadvantages to learning a new database development model.


Rick Dobson, Ph.D., is an author, trainer, and developer for Office, Web, and database development issues. His computer practice has been in full-time operation since 1991. He's the author of Programming Microsoft Access 2000 (Microsoft Press). You can reach his consulting practice at http://www.cabinc.net or www.programmingmsaccess.com.