Data Management

Building solutions with MSDE and Access 2000

Rick Dobson discusses the architectural and management issues that you need to understand in order to build solutions with MSDE and Microsoft Access projects.


Access 2000 sports two new features that dramatically enhance its ability to manage client/server solutions. First, it ships with the Microsoft Data Engine (MSDE) as an alternative database engine to Microsoft Jet Database Engine (Jet). Access 2000 is the first version of Access to offer a true client/server database engine in the form of MSDE. Second, Access 2000 adds a new solution file type that contrasts its familiar .mdb (Microsoft Access Database) files. The new .adp (Access data projects) file type is a container for an Access project. IT professionals choosing to create solutions with Access projects can graphically or programmatically generate solutions with MSDE, SQL Server 6.5 (with Service Pack 5), or SQL Server 7.

MSDE is well suited for small workgroup solutions that will escalate to large workgroup or enterprise solutions. It’s also appropriate for solutions that start with a database under a couple of gigabytes but that will grow to terabytes. When the demands of a solution exceed the capacity of the server on which MSDE runs, you can readily transfer the database to SQL Server 7 for servicing a larger community of users and/or a larger database.

Access projects offer several distinct advantages over traditional .mdb files when working with SQL Server data sources, such as those from SQL Server 7 and MSDE. First, an Access project links to its underlying data source through a single OLE DB connection. There is no need to pass through both Jet and ODBC (open database connectivity). Second, OLE DB will ultimately replace ODBC as the standard for low-level data access. It has a component design that accommodates relational data sources, such as those suitable for ODBC, as well as non-relational sources, such as file directory and mail data stores. Third, there is no need to "upsize" an MSDE solution since it already has the format of a SQL Server database.

In this article, we'll provide an overview of architectural and management issues that you need to know to build solutions with MSDE and Access projects. We'll target the Windows 9x platform, which is particularly appropriate for the small and remote workgroups that are prime candidates for adopting MSDE as a database engine. Refer to the article “An Introduction to MSDE for Office 2000 ” for an MSDE primer.

Multiuser solutions with Access projects
Since MSDE is a true client/server database engine, it actively manages its data sources. Users submit queries; MSDE replies with a return set. When using Access 2000 with a MSDE data source, users will often submit requests from an Access project. Each individual in a multiuser solution requires an Access project file. However, users don't share a single, common .adp file. Each individual's Access project can relate to the database in an MSDE.

Tables, views, stored procedures, and database diagrams always reside on an MSDE. Forms, reports, data access pages, macros, and modules reside in individual .adp files. A data access page is a new construct introduced with Access 2000. It exposes Jet, SQL Server, or MSDE data sources on a Web page.

Data type issues for moving from Jet to MSDE
If your organization is moving IT professionals with experience developing Jet tables to building Access projects with MSDE, they need to be aware of some subtle differences between the Jet and SQL Server data types. Table A highlights the Jet data types and the corresponding data types in MSDE.

Table A
Jet SQL Server
text varchar, nvarchar, char, nchar
memo text, ntext
autonumber identity column
currency money, smallmoney
date/time datetime, smalldatetime
yes/no bit
OLE object image
hyperlink no match
long integer int
integer smallint
double, single float, real
Data Type Comparison

The Jet text data type loosely matches the varchar, nvarchar, char, and nchar SQL Server data types. Jet text data type fields can hold up to 255 characters. Access 2000 automatically stores all text and memo fields in the 2-byte Unicode format instead of the more familiar 1-byte ANSI format. The char and varchar data types denote, respectively, fixed and variable length ANSI-formatted character strings. Both varchar and char data type fields can grow to 8,000 characters. The nchar and nvarchar data types specify fixed and variable length characters in a Unicode format. Because of the 2-byte character representation, nchar and nvarchar fields can grow to only 4,000 characters. The Unicode format is particularly useful when you're building an international application that requires the representation of characters in non-English languages.

The Jet memo data type corresponds to the text and ntext SQL Server data types. Both the Jet memo and SQL Server ntext fields represent very large character fields in Unicode format. Without compression, their maximum size is slightly greater than 1 billion characters. On saving to disk, Access 2000 attempts to compress its field representations from 2 bytes to 1 byte. Compression results vary by language character set (English compresses better than some Asian character sets). The SQL Server text data type can store a character field up to 2 billion characters because it always stores data in a 1-byte format.

Other data type differences relate to naming conventions and the availability of data types. The Jet long integer data type compares to the SQL Server int (integer) data type. On the other hand, the Jet integer data type matches the SQL Server smallint (small integer) data type. Another pair of name conversions occurs for floating point numbers. Double and single in Jet data types align with float and real in SQL Server data types.

Finally, the Jet hyperlink data type has no match at all among SQL Server data types. You can still represent a hyperlink with the same format in SQL Server as in Jet, but the link is not operational from the table's datasheet view. On the other hand, Access projects introduced a new property for text box and combo box controls on a form. With this property set to True, the link becomes active on forms based on SQL Server data sources.

Access projects (.adp files) have the same look and feel as traditional Access databases (.mdb files), but there are several subtle differences in other areas besides data types for Access database developers to master as they transition from Access database to Access project files. For this reason, you should take advantage of learning materials as you move your database application platform from Jet to MSDE.

Learning aids for Access projects and MSDE
There are three excellent resources for organizations initiating a transition from Access database applications to Access projects running against MSDE. First, use the Show Me menu in the NorthwindCS Access project file. This project works with a client/server version of the traditional file/server version of the Northwind database. Second, tap the SQL Server Books Online resource. This is Microsoft’s definitive source on installing, administering, and programming SQL Server. Third, a MSDN CD-ROM set ships with the Microsoft Office 2000 Developer Edition. It includes many valuable resources for mastering SQL Server terminology and concepts, including the SQL Server Books Online reference content.

Because most large and medium-sized organizations have Jet experts with an intimate understanding of the Northwind database, this resource represents a good starting point for learning more about client/server development with MSDE. The dialog box offers drill-down links that enable users to dig deeper into selected topics, such as tables, views, and stored procedures. In addition, IT employees can learn special tricks for using Access forms and reports with client/server databases. There’s even tutorial content on using data access pages with MSDE or SQL Server databases. All of these learning materials tie back directly to the NorthwindCS database and an Access project with the same name (NorthwindCS.adp). This lets you see the selections and code that correspond to the explanation of how to use a feature.

The SQL Server Books Online resource is available with SQL Server. SQL Server Books Online includes extensive coverage of SQL DMO development techniques (a handy tool for programmatically administering SQL Server). It also contains detailed descriptions and usage samples for SQL Server data types. This documentation applies to MSDE. Since MSDE appeared initially around version 7 of SQL, you need the Books Online resource for version 7 to have coverage of MSDE.

IT professionals with the Microsoft Office 2000 Developer Edition (MOD) also have access to Books Online if they install its MSDN Library. The Books Online documentation has a different name in the MSDN Library shipping with MOD—it's called Microsoft SQL Server Programmer's Toolkit. You can reach it by traversing the following hierarchy of headings: MSDN Library—Office 2000 Developer | Platform SDK | Database and Messaging Services | Microsoft SQL Server Programmer's Toolkit.

The MSDN CD-ROM set offers excellent content on Access projects and ActiveX Data Objects (ADO) programming techniques for use with MSDE. This resource also includes nearly ten pages offering specific coverage of the MSDE.

Learning from and copying the Pubs database
Consider moving the Pubs database from a full SQL Server version to an MSDE (Pubs does not ship with MSDE, but it is available with SQL Server 7). This will give you a chance to get familiar with moving databases between servers. Many Office samples rely on the Pubs database. If your only, or typical, database engine is MSDE, then having Pubs on MSDE will help you master Access 2000 client/server and ADO innovations.

The Pubs database is the classic SQL Server starter database. Pubs is to SQL Server what Northwind is to Jet. Several ADO samples in the documentation for Office 2000 reference Pubs. However, this core database doesn't ship with Office 2000, nor does it install automatically with MSDE. If an Access 2000 user has a network connection to an SQL Server version, she or he can copy it from the remote SQL Server to a local MSDE. You can achieve this manually or programmatically. The programmatic route is instructive because it generally shows how to copy a database file from one SQL Server to another. The manual route may appeal more to those who prefer more interactive methods of managing a database.

There are two groups of actions to perform when manually copying a file such as Pubs.mdf from a remote SQL Server to a local MSDE. First, you have to stop the remote server. This is because SQL Server actively manages files, and you will encounter a copy error when you try to paste Pubs.mdf if the server is running. Next, copy the file from the remote server to the local MSDE server. Do the same for the corresponding log file on the remote server (this action is essential). Restart the remote server to restore its availability for users.

Second, open the Data Link Properties dialog box for a disconnected Access project file on the computer running MSDE. Using the sa logon (or another appropriate one with sufficient administrative permissions), select the Attach A Database File As A Database Name option. Then, click the Build button next to the Using The Filename text box and navigate to the Pubs.mdf file that you copied to the local computer. Finally, click OK to attach the file to the local MSDE and connect the Access project to the new database on the server.

Unless an .mdf file is available and attached to a server, such as MSDE, users can't process it. The pair of sub procedures shown in Listing A demonstrate how to programmatically copy the Pubs database file from a remote server named Cab2200 and attach the file to a local MSDE. After the successful operation of the procedure, you'll need to connect an Access project to Pubs in order to work with it locally.
Sub callAttachPubsToMSDE()
 attachPubsToMSDE "\\cab2200\c", "Cab2200"
End Sub

Sub attachPubsToMSDE(UNCNameAndShare, _
 RemoteName)

'This sub requires references to:
' Microsoft SQLDMO Object Library and
' Microsoft Scripting Runtime

Dim oSvr As SQLDMO.SQLServer
Dim oFSO As Scripting.FileSystemObject
Dim Pubspath As String

'Connect to remote SQL Server with Pubs database
 Set oSvr = New SQLServer
 oSvr.Connect RemoteName, "sa", ""
 oSvr.DetachDB "Pubs"

'Copy the database file to the local drive
 Set oFSO = New FileSystemObject
 Pubspath = UNCNameAndShare & _
 "\mssql7\data\pubs.mdf"
 oFSO.CopyFile Pubspath, _
 "c:\mssql7\data\pubs.mdf"

'Reattach the database file and disconnect
 oSvr.AttachDBWithSingleFile "Pubs", _
 Pubspath
 oSvr.Disconnect

'Connect to the local MSDE and attach Pubs file
 oSvr.Connect "(local)", "sa", ""
 oSvr.AttachDBWithSingleFile "LocalPubs", _
 "c:\mssql7\data\pubs.mdf"

 Set oSvr = Nothing
End Sub

The programmatic means of attaching a remote database file has the advantage of not shutting down an entire database server to copy a file from one computer to another. The technique does temporarily detach the database that it copies, but it automatically re-attaches the database. This process affects only one file on the remote server. The subprocedure performing the major portion of the work requires a link to two object libraries. Recall that SQLDMO is a particularly powerful library for programmatically manipulating any SQL Server. The Microsoft Scripting Runtime enables the manipulation of the FileSystemObject. A method of this object supports copying files.

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 as well as 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.

Editor's Picks

Free Newsletters, In your Inbox