Data Management

Deploying ADP solutions

Rick Dobson discusses the new ADP hybrid file type, which provides Access' rapid application development capabilities and SQL Server's industrial-strength database handling.

Microsoft recently introduced the new ADP file type. ADP is a hybrid file type: It has a graphical user interface (GUI) that resembles a traditional Access solution, but it has a SQL Server backend. Developers and their enterprise clients get the best of both worlds—Access’ rapid application development capabilities and SQL Server’s industrial-strength database handling.

In this article, we’ll introduce you to technologies that help in the management of deploying database solutions that rely on ADP files. In particular, we’ll provide you with an introduction to an object library that targets the management of SQL Server. We’ll provide step-by-step instructions for applying the library to the task of copying SQL Server databases between servers. We’ll also offer tips that will help you hone your ability to deploy custom solutions based on ADP files.

What are ADP files?
How do you deploy solutions based on this new file type? With ADP files, you must consider two file types: a database server and a connection between the ADP file and the database server. The ADP file contains the Access application objects, including the forms, reports, data access pages, macros, and modules. This ADP file typically has a connection to a SQL Server that, in turn, manages an MDF file. The MDF file serves as a storehouse for an application’s tables, views, stored procedures, and database diagrams. As an application moves from development to production environments, you must package the ADP files for deployment and physically distribute them. You will probably also have to copy your MDF file from a development to a production SQL Server. In addition, you may have to modify the connection from the ADP file to the database in the new SQL Server. If your application uses the redistributable MSDE version that ships with Microsoft Office 2000 Developer (MOD), you can physically deploy this MSDE version along with your solution through the Package And Deployment Wizard that also ships with MOD.

SQL-DMO and its requirements
SQL Distributed Management Objects (SQL-DMO) is an object library that exposes SQL Server database and replication management resources. You’ll probably need this object model as you automate the deployment of custom solutions built with ADP files. SQL-DMO lets you control SQL Server programmatically from any software package that can manipulate COM, such as Access 2000. You can use SQL-DMO to manage programmatically just about anything that you can with SQL Server. We’ll discuss connecting to a SQL Server, enumerating its objects, and attaching new databases to a SQL Server.

To run an SQL-DMO application, the object library must be available, and your application must create a reference to it. Your operating system must be Windows 9x, Windows NT, or Windows 2000. Of course, your application must have connections to one or more SQL Server or MSDE database servers on a network. You must also have the SQL Server ODBC Driver, version 3.7 or later. This ODBC driver ships with SQL Server 7.0, and it’s also available with the Microsoft Data Access Components . The VBA project for your application must have a reference to the SQL-DMO object library. After setting the reference, your application can use SQL-DMO to manage data access. It does not require an ODBC or OLE DB connection definition or an ODBC Administrator data source definition (a DSN).

An SQL-DMO sample application
Database administrators can use a form to create custom views of the databases and objects that they manage. In our example, the form runs in an ADP file without a connection to a data source. A combo box enables users to select any pre-designated SQL Server. After a user selects a value for the combo box, the application automatically enumerates all the databases attached to that server in a list box. Clicking on any database name in that list box causes the application to enter all of the table names for that database in a second list box. You can readily extend this application to list the members of any SQL-DMO collection.

In order to run this application in your environment, you need to set the SQL Server names into the combo box’s Row Source property. Since this is its Value list, you also need to set the Row Source Type property to Value List. The three SQL Servers in the test environment are CABxli, CAB1700, and CAB2200. Change these so they match those in your environment.

Listing A presents the two event procedures that populate our two list boxes. Selecting a SQL Server from the combo box fires the cboSQLServer_AfterUpdate event procedure. This procedure starts by clearing the two list boxes before it populates the first list box with the name of databases attached to the server. The ListDatabases function procedure returns properly formatted values for the Row Source property of the first list box. The event procedure passes the name of the database selected in the combo box to the function procedure.
Private Sub cboSQLServer_AfterUpdate()
'Clear listbox controls
Me.lstDatabases.RowSource = ""
Me.lstTables.RowSource = ""

'Populate the lstDatabases control with the
'return values from the ListDatabases function
With Me.lstDatabases
 .RowSourceType = "Value List"
 .RowSource = ListDatabases(cboSQLServer.Value)
End With

End Sub
Private Sub lstDatabases_AfterUpdate()
'Populate the lstTables control with the
return values from the ListTables function
With Me.lstTables
 .RowSourceType = "Value List"
 .RowSource = ListTables(cboSQLServer.Value, lstDatabases.Value)
End With

End Sub

The lstDatabases_AfterUpdate event procedure has the same general form as the first event procedure. This second event procedure populates the second list box with the tables from whatever database a user clicks in the first list box. The call to the ListTables function relies on the SQL Server name in the combo box and the selected database name in the first list box.

Listing B displays the two function procedures called by the event procedures in Listing A. These illustrate how to use the SQL-DMO model.
Function ListDatabases(ServerName As String) As String
On Error GoTo ListDatabasesTrap
Dim sqlSrv1 As New SQLDMO.SQLServer
Dim sqlDb1 As SQLDMO.Database
Dim intOrigTimeout As Long

'Open a connection to a server
'with a shorter timeout set for
'the trapping of connection errors
sqlSrv1.LoginTimeout = 120
sqlSrv1.Connect ServerName, "sa", ""

'Enumerate and save the names of the
'Databases collection members
For Each sqlDb1 In sqlSrv1.Databases
 ListDatabases = ListDatabases & sqlDb1.Name & ";"

'Strip the trailing semi-colon
ListDatabases = Left(ListDatabases, Len(ListDatabases) - 1)

Set sqlSrv1 = Nothing
Set sqlDb1 = Nothing
Exit Function

If Err.Number = -2147221504 Then
'Message user that server did not open
 MsgBox "Could not open connection to SQL Server. " & _
"Check to make sure it is available."
 MsgBox "Unanticipated error. Check " & _
 "Immediate window for details."
 Debug.Print Err.Number, Err.Description
End If
Resume ListDatabasesExit

End Function
Function ListTables(ServerName As String, _
 DatabaseName As String) As String
On Error GoTo ListTablesTrap
Dim sqlSrv1 As New SQLDMO.SQLServer
Dim sqlDb1 As New SQLDMO.Database
Dim sqlTbl1 As SQLDMO.Table

'Open a connection to a server
sqlSrv1.Connect ServerName, "sa", ""
Set sqlDb1 = sqlSrv1.Databases(DatabaseName)

'Enumerate and save names of the Tables collection
'filter on typeof = 8 to exclude many non-user-defined tables
For Each sqlTbl1 In sqlDb1.Tables
 If sqlTbl1.TypeOf = 8 Then
 ListTables = ListTables & sqlTbl1.Name & ";"
 End If

'Strip the trailing semi-colon
ListTables = Left(ListTables, Len(ListTables) - 1)

'Clean up object pointers
Set sqlSrv1 = Nothing
Set sqlDb1 = Nothing
Set sqlTbl1 = Nothing
Exit Function

If Err.Number = 5 And Len(ListTables) = 0 Then
'err of 5 with no length means no user-defined tables
 MsgBox "No user-defined tables in database. " & _
 "Pick another database."
MsgBox "An unanticipated error in the ListTables " & _
 "function. See Immediate window for details."
 Debug.Print Err.Number; Err.Description
End If
Resume ListTablesExit

End Function

The first function procedure shows how to enumerate the databases on a server. You must declare two objects to accomplish this task. One points to the SQL Server selected in the combo box on our form. Before attempting to itemize the databases on the server, the procedure attempts to connect to it. The SQLServer object has the name sqlSrv1. A large selection of SQLServer object properties, methods, and events enable you to refine how your applications process servers. The sample function procedure illustrates how to vary the login timeout default value (the default value is 60 seconds, but the sample doubles that value in case the server is very busy). The SQL Server object has a Databases collection. A For Each...Next loop iterates through the members of the collection with a second object that has a Database type. As the loop passes through the members of the collection, it builds a string for the Row Source property of our form’s first list box. After the loop, a string function strips the final trailing semicolon from the string.

The second function procedure in Listing B works with the Tables collection of the SQL-DMO Database object. Not all the tables in a database are user-defined. Therefore, the procedure uses the TypeOf property for the Table object. SQL-DMO denotes user-defined tables with a TypeOf property value of 8. Actually, this property value includes some tables that are not necessarily user-defined, but it excludes all tables designated as system tables.

MSDE: One technology, but two versions
The Microsoft Data Engine (MSDE) is a new database engine that ships with Access 2000. Developers can build solutions with either Jet or MSDE when building solutions with Access 2000. You can obtain a general introduction to using MSDE with Office 2000, including Access 2000, by reading “An introduction to MSDE for Office 2000 .” Chapter 12 in Programming Microsoft Access 2000 from Microsoft Press demonstrates how to build solutions with MSDE and ADP files.

MSDE is a Microsoft SQL Server 7.0 compatible storage server. SQL Server is a more robust server, but MSDE and SQL Server 7.0 rely on the same database technology. Therefore, you can use ADP files with either SQL Server 7.0 or MSDE servers. When using SQL-DMO, you may notice no differences in core features. For example, Figure B lists three servers for its SQL-DMO demonstration, but one of these is a MSDE server. There are no special steps for enumerating databases or tables from the MSDE server versus the two SQL Servers.

Two versions of MSDE ship with Microsoft Office 2000. When you install the standard version that ships with the Office 2000 Premium Edition, you receive a version that includes special interfaces for working with the visual elements of ADP files. This is a proprietary version that is not redistributable with your custom solutions. End users can use your custom solutions based on ADP files with this version. They must have a license to the MSDE version that ships with the Office 2000 Premium Edition (or the one that ships with the stand-alone version of Access 2000).

A second version of MSDE ships with MOD. This version is redistributable, but it doesn’t include support for the visual elements of ADP files. You can distribute custom solutions with the redistributable MSDE just as you do the Access run-time component. In both cases, you must have a license for the Microsoft Office 2000 Developer edition, but the redistributable MSDE is royalty free just like the Access run-time component.

This tale of two MSDE versions implies that there are two ways to deploy custom solutions using ADP files.

Deploying a solution from one SQL Server to another
Deploying a solution built with ADP files between two servers has two major steps. First, you’re likely to copy your database from a development to a production server. Second, if you do copy your database to a new server, you must revise the ADP file’s Data Link Properties settings so that they point to the new copy of the database. Since many enterprises are likely to have several SQL Servers deployed strategically throughout an organization, this deployment technique has broad applicability. With this strategy, you don’t have to install and configure a redistributable MSDE.

When copying a database between two servers, you must first detach the MDF file for a database from its current server so that you can copy the file from one location to another. After you do that, you can copy the MDF file. If you follow default conventions, you’ll copy the file from the Data subfolder of the Mssql7 folder on one computer to the same location on another computer. After copying the SQL Server database file, you’ll want to reattach the original copy to the first database server. Conclude the copying process by attaching the duplicate file to the database server on the second computer.

Although you can perform these steps manually, it’s actually easier and faster to implement them programmatically. For example, if you try to attach using the Data Link Properties dialog box, you’ll need access to both the MDF and the LDF files. This is not required when programming with SQL-DMO.

Listing C presents a simple procedure for copying a database from the current database server to one located on the cab1700 computer. It uses both the SQL-DMO and the Scripting Runtime object libraries. Your application must have a reference to these libraries in order for the procedure to function. The comments in the listing provide line-by-line commentary. However, it’s worth noting that this procedure requires that the database not exist previously on the production computer. The invocation of the CopyFile method for the FileSystemObject sets this requirement. You can easily circumvent any difficulties arising from the requirement by manually or programmatically removing the MDF file from the second server.
Sub CopyandAttachMdf()
Dim sqlSrv1 As New SQLDMO.SQLServer
Dim fsoObj1 As New Scripting.FileSystemObject
Dim strSource As String
Dim strDestination As String

'Open connection to local SQLServer
sqlSrv1.Connect "(local)", "sa", ""

'Detach the LocalPubs database
sqlSrv1.DetachDB "LocalPubs"

'Copy .mdf for LocalPubs from local to cab1700 server
strSource = "c:\Mssql7\Data\pubs.mdf"
strDestination = \\cab1700\c\Mssql7\Data\pubsfromcabxli.mdf
fsoObj1.CopyFile strSource, strDestination, True

'Reattach .mdf for LocalPubs database
'to local server
sqlSrv1.AttachDBWithSingleFile "LocalPubs", "c:\Mssql7\Data\pubs.mdf"

'Disconnect sqlSrv1 pointer

'Reconnect sqlSrv1 pointer to cab1700 server
sqlSrv1.Connect "cab1700", "sa", ""

'Attach copied file to cab1700 server
sqlSrv1.AttachDBWithSingleFile "LocalPubs",

'Cleanup the object pointers
Set sqlSrv1 = Nothing
Set fsoObj1 = Nothing
End Sub

After creating a copy of the database on another computer, you can manually update the Data Link properties for your ADP file. Make it point to the new version of the database, then distribute the ADP files with the altered Data Link properties to application clients. The MOD Package and Deployment Wizard can create automatic setup and removal procedures for your ADP files. My article “Deploying Office 2000 solutions ” shows how to operate the Package and Deployment Wizard.

Deploying a solution with the redistributable MSDE
Using the redistributable MSDE has appeal for custom applications that will have a light load of users per installation but will have many installations. The MSDE targets small workgroups with database sizes at or below 2 GB. There’s no set limit for the number of users, but built-in design features do degrade performance as you move past a handful of users.

There are three steps to packaging custom solutions for deployment with the MSDE:
  1. Install the MSDE on the user’s machine
  2. Start MSDE
  3. Load the database into MSDE

Scott Smith offers a step-by-step demonstration of how to package custom solutions for deployment with the redistributable MSDE . His approach highlights applying the MOD Package And Deployment Wizard to install and customize the redistributable MSDE. Scott’s paper provides sample SQL-DMO procedures for automating the start of MSDE on a client’s computer and loading a database into it.

In this article, we’ve examined techniques for deploying custom solutions based on ADP files and SQL Server or MSDE databases. SQL-DMO is essential to the automation of this task. There are two distinct options for distributing custom ADP solutions—one based on SQL Server and the standard MSDE, and the other relying on the redistributable MSDE. This article shows you how to distribute solutions based on previously installed SQL Server and standard MSDE servers.

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 ( and ).

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