ADO 2.1, the Engine behind Access 2000

By Rick Dobson, Ph.D.

Access 2000 supports two data access models. One, ActiveX Data Objects (ADO), is new to Access, and is the focus of this article. The other data access approach is the Data Access Objects (DAO) model developed for the initial version of Access and its Jet database engine. Microsoft has upgraded DAO many times in subsequent Access releases (Access 2000 offers version 3.6), but it still targets the Jet database engine.

ADO is one of the fundamental building blocks of Microsoft’s Universal Data Access strategy that’s been unfolding for years. ADO will eventually supplant both DAO and RDO (a popular remote data access model for VB developers). ADO is an application-level interface with a consistent design across many programming environments. It offers access to Jet data, traditional remote data sources, and new data sources, such as e-mail and file directory systems.

ADO works hand-in-glove with OLE DB to achieve universal data access. OLE DB is Microsoft’s component architecture for data access. It incorporates ODBC database connectivity, and adds new functionality to support universal data access. You connect to data sources in ADO through OLE DB providers.

This article is the first in a four-part series on ADO in Access 2000. Access 2000 supports ADO 2.1, and introduces three object libraries for database programming. By segmenting data access into libraries, Access offers a smaller footprint for applications that don’t require all of its data access models. To take advantage of this, you need to know the unique capacities of each data access library. This introduction reviews the design of the object models to identify the strengths and opportunities they provide. Selected examples illustrate new programming techniques. Subsequent articles will drill down to the behavior of the models and offer detailed programming examples, as well as examine the ADO event model.

ADO 2.1 for Access 2000 comprises three libraries:

The Microsoft ActiveX Data Objects 2.1 Library enables the ADODB object model, which supports the general database objects, Connection, Command, and Recordset.

The Microsoft ADO Ext 2.1 for DDL and Security Library supports the ADOX object model. Developers can use this model for data definition (e.g. table creation) and security (e.g. implementing password protection for sensitive data).

The Microsoft Jet and Replication Objects 2.1 Library targets database replication with the Jet database engine. Use the JRO model to support tasks such as creating a design master, creating replicas, and performing synchronization.

To work with any of the ADO libraries, you must first create references to them. In the VBE, select Tools | References to display the References dialog box, and select the libraries (see FIGURE 1).

FIGURE 1: Adding the ADO 2.1 libraries to a VBA application at the References dialog box.

FIGURE 2 is a diagram of the objects and collections in the ADO object model. The model enables you to connect to a database, process data with commands, and examine answer sets. You can also use these objects to execute commands that add, delete, or update data sources. Optionally, you can specify that either all changes to a data source are successful, or that none of the changes occurs.

FIGURE 2: The ADO Object Model. The Connection, Command, Recordset, and Field objects contain a Properties collection.

The Connection object acts as an interface between your application and a database. Unlike DAO, ADO is a general data access language. There is a special OLE DB provider for Jet 4.0, the one that ships with Access 2000. The custom provider allows ADO to reflect many of the special Jet strengths.

When referring to a database in another file, you will often want to include a Provider parameter. This points to the physical location of a database when it is not in the current project. FIGURE 3 lists a selection of providers available for use with ADO in Access 2000. As you can see, there are special providers for SQL Server, Oracle, and Jet. There are also providers that offer connectivity with non-traditional database sources, such as file directories and Microsoft Index Server content. The ODBC provider enables your Access/ADO applications to connect to virtually any traditional data source. New OLE DB providers are in development by third-party developers; visit http://www.microsoft.com/data/oledb for the latest provider information.

FIGURE 3: Summary of selected OLE DB data providers for use with ADO 2.1.

When working with Jet databases, you will, as a minimum, want to designate the provider and the data source. FIGURE 4 shows five approaches. The first two build a connection string for the Northwind database. The first uses the ConnectionString parameter of the Open method. The second uses the ConnectionString property of the Connection object. With this approach, your application can assign a Jet data source dynamically.

Sub MakingAConnection()

  Dim cnn1 As ADODB.Connection

  On Error GoTo connTrap

  ' Assign the connection reference.
  Set cnn1 = New ADODB.Connection

  ' Method 1: Using a Jet provider to connect to Northwind.
  With cnn1
    .Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=C:\Program Files\Microsoft Office\" & _
      "Office\Samples\Northwind.mdb;"
    .Close
  End With

  ' Method 2: Incrementally building a connection string.
  With cnn1
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
    .ConnectionString = .ConnectionString & _
      "Data Source=C:\Program Files\Microsoft Office\" & _
      "Office\Samples\Northwind.mdb;"
    .Open
    .Close
  End With

  ' Method 3: Connect to Microsoft SQL Server.
  With cnn1
    .Provider = "SQLOLEDB"
    .ConnectionString = "data source = cab2200;" & _
      "user id = sa; initial catalog =pubs"
    .Open
    .Close
  End With

  ' Method 4: Using an MSDASQL provider connection string.
  With cnn1
    .Open "Provider=MSDASQL;Driver=SQL Server;" & _
      "Server=CAB2200;Database=Pubs;uid=sa;pwd=;"
    .Close
  End With

  ' Method 5: Use DSN to specify MSDASQL provider.
  With cnn1
    .Open "Provider=MSDASQL;DSN=Pubs;"
    .Close
  End With

connExit:
  ' Close any connection still open before exiting.
  cnn1.Close
  Exit Sub

connTrap:
  If Err.Number = 3705 Then
    ' Close an open connection for its reuse.
    cnn1.Close
    Resume
  ElseIf Err.Number = 3704 Then
    ' The connection is already closed; skip close method.
    Resume Next
  Else
    Debug.Print Err.Number; Err.Description
    Debug.Print cnn1.Provider
    Debug.Print cnn1.Provider; cnn1.ConnectionString
  End If

End Sub

FIGURE 4: Five ways to connect with OLE DB providers.

The third connection example in FIGURE 4 illustrates the ADO connection to a Microsoft SQL Server database; the Connection object’s Provider property is set to SQLOLEDB.

The fourth and fifth examples in FIGURE 4 demonstrate two ways to access an MSDASQL provider. If your work requires interacting with a wide variety of data source types, you’re likely to need this provider, since it works for any ODBC data source. The fourth example shows how to connect to a remote data source. The fifth example relies on the entries in a data source name (DSN) declaration. Use the ODBC Data Source Administrator (accessed by clicking on the “32bit ODBC” icon in Windows Control Panel) to create DSNs. The explicit statement doesn’t rely on a system DSN (which is susceptible to changes by users), but amounts to a hard-wire.

The Recordset object is another pivotal element in the ADO Object Model. You can base a Recordset on a table or view, a SQL statement, or a command that returns rows. Access 2000 enables the designation of simple row-returning queries as views. What you can do with a Recordset will depend on its OLE DB provider. ADO offers a cluster of features that different providers can selectively enable. Selective feature implementation plus native data source attributes impact how you can manipulate Recordsets based on a data source.

A Recordset’s ActiveConnection property allows your application to tap an open connection. You can set this property any time after setting the object reference for the Recordset. Its use simplifies your Open method statement for the Recordset by removing the need to include connection information.

The Open method is commonly used to make a Recordset available in a procedure. The Source property designates the data source; typical Source assignments include tables, SQL statements, saved Recordset files, or stored procedures. You can optionally use the Open method’s Options parameter to designate the source type when you open a Recordset.

The CursorType property determines how and where you navigate through a Recordset, and the types of locks you can impose on it. ADO supports four cursor types: dynamic, keyset, static, and forward-only.

Dynamic cursors enable Recordset users to view changes to a data source made by other users of the underlying data source. This cursor type enables Recordset maintenance functions, such as adding, changing, and deleting records. Dynamic cursors also permit bi-directional navigation around a database without relying on bookmarks. The Jet 4.0 provider doesn’t support dynamic cursors, but you can use this cursor type with other providers, such as SQLOLEDB and MSDASQL.

Keyset cursors have most of the properties of dynamic cursors, except you do not have ready access to changes by other users of a data source. One way to view changes made by others is to invoke a Recordset’s Requery method. A static cursor is a snapshot of a Recordset at a point in time, and allows bi-directional navigation. Changes to the database by other users are not visible. The forward-only cursor goes in one direction, and it can speed cursor performance. This is the default ADO; if you need another type of cursor, you must set the CursorType property before opening the Recordset.

Five Recordset methods enable navigation by re-positioning the cursor:

The MoveFirst method changes the current record to the first record in a Recordset. The order of records depends on the current index, or, if there is no index, on the order of entry. This method functions with all cursor types. Its use with forward-only cursors can force a re-execution of the command that generated the Recordset.

Invoke the MoveLast method to establish the last record in a Recordset as the current record. This method requires a cursor type that supports backward movement, or at least movement based on bookmarks. Using the method with a forward-only cursor will generate a run-time error.

The MoveNext method relocates the current record one record in the direction of the Recordset’s final record. If the current record is already the last record, the Recordset’s EOF property is set to True. If EOF is already True, a run-time error results.

The MovePrevious method sends the current record one record in the opposite direction of MoveNext. This method can turn the BOF property to True if the cursor is on the first record, or generate a run-time error if the property is already True. The MovePrevious method also generates a run-time error if you use it with a forward-only cursor type.

The Move method can move the current record a variable number of records in either direction. Use a positive NumRecords argument value to signify moves toward the last record, and a negative number to move toward the first record. If a move will extend beyond the first or last record, it will set the Recordset’s BOF or EOF property to True. If that property is already True, the Move method will generate a run-time error. Movement is relative to the current record unless you specify a Start parameter that can enable movement from the first or last record.

You can enhance the performance of the Move method by using the Recordset’s CacheSize property. CacheSize settings cause ADO to store a fixed number of records in the local workstation’s memory. Because it’s much faster to retrieve records from memory than from a provider’s data store, you can speed Move-method navigation by using a CacheSize greater than one. In the case of a forward-only cursor, you can enable backward and forward scrolling by using a CacheSize greater than one. If your cache setting is equal to the number of records in a Recordset, you can scroll the full extent of the Recordset in both directions. (The CacheSize property does not enable backward scrolling with the MovePrevious method.)

FIGURE 5 shows a simple application of the Recordset object. Notice there is no explicit Connection object. The Open method includes an argument that sets the ConnectionString as it specifies a table that acts as a data source for the Recordset. The outer loop uses MoveNext to advance through each record. The inner loop iterates the Fields collection for the Recordset. (Again, FIGURE 2 depicts the hierarchical relationship between the Fields collection and the Recordset object.) As the inner loop enumerates the fields in a row, it builds a string with all the field values. After printing the string, the procedure re-initializes the string to a zero-length string variable, ready for another row.

Sub EasyLoop()
  Dim rst1 As Recordset
  Dim fldMyField As Field
  Dim strForRow As String

  Set rst1 = New ADODB.Recordset
  rst1.Open "customers", _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Program Files\Microsoft Office\" & _
    "Office\Samples\Northwind.mdb;"
  ' Loop through recordset and fields with rows.  Do Until rst1.EOF    strForRow = "    For Each fldMyField In rst1.Fields      strForRow = strForRow & fldMyField & "; "    Next fldMyField    Debug.Print strForRow
    rst1.MoveNext
  Loop
  rst1.CloseEnd Sub

FIGURE 5: Enumerating the fields of a row in an ADO recordset with a nested loop.

Looping is an easy way to picture the behavior of rows and columns within a Recordset. However, it’s not the most efficient way. The NoEasyLoop procedure presents an alternative that prints all the fields on all rows in one step (see FIGURE 6). The GetString method returns a Recordset as a string. It can take up to five arguments. The example in FIGURE 6 illustrates three of those arguments. Designate the adClipString constant as the first argument. This is your only choice for this argument. It specifies the format for representing the Recordset as a string. The second argument specifies the number of Recordset rows to return; this example returns five rows. Leaving this argument blank would enable the method to return all the rows in the Recordset. The third argument designates a semicolon as the delimiter for the columns within a row (the default column delimiter is a tab). The fourth and fifth arguments (neither of which appears in the example) specify a column delimiter and an expression to represent null values. The default values for these arguments are a carriage return, and a zero-length string, respectively.

Sub NoEasyLoop()
  Dim rst1 As Recordset

  Set rst1 = New ADODB.Recordset
  With rst1
    .Open "products", _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=C:\Program Files\Microsoft Office\" & _
      "Office\Samples\Northwind.mdb;"
    ' Print records without a loop.
    Debug.Print .GetString(adClipString, 5, "; ")
    .Close
  End With
End Sub

FIGURE 6: Printing the fields of a row in an ADO recordset without a nested loop.

The Recordset GetString method replaces a nested loop. If the defaults are acceptable, you can use the method without any arguments. This makes for a very simple approach for extracting values from a Recordset.

Command objects deliver three major benefits. First, they can perform a SELECT query to return a set of rows from a data source. Second, they execute a parameterized query so you can input run-time search criteria. Third, they support action queries against a data source to perform such operations as updating, deleting, and adding records. The Command object can serve additional roles with other libraries.

You must designate a Connection object on which to run a command. Just as for Recordsets, you can implicitly create a Connection object as you specify a command, or you can explicitly assign an existing Connection object to a command.

There are several types of Command objects. The CommandType property lets you set the type of Command object. The constants suggest some of the diverse capabilities available from this object (see FIGURE 7). You can base your command on a SQL statement, a table, or a stored procedure. Changing the CommandType constant from its default setting (unknown) can speed the operation of a command.

FIGURE 7: CommandType constants and their behaviors.

Use the CommandText property to write a SQL statement for the command to execute. You can also set this property to the name of a stored procedure. Setting the Prepared property to True will cause the statement to be compiled and stored on the database server before it is executed. This will slow the first execution of the command, but will speed subsequent executions.

The Execute method for a Command object invokes the code in the CommandText property, e.g. the SQL statement or stored procedure. You can optionally specify up to three arguments for the Execute method. The first argument allows the Command object to tell the procedure invoking it how many records were affected by the command. Your second argument is a Variant array with parameters to drive the command. The third argument tells ADO how to evaluate the source. It can be any of the constants shown in FIGURE 7.

Another Command method that you’ll want to master is CreateParameter. This method creates a new parameter for a command. After creating the parameter, you can use the Append method to add the parameter to the Parameters collection for a command. Before running a parameterized query, you must assign a value to the parameter.

The example in FIGURE 8 illustrates how to design a parameterized query. The code runs against a simple table, named MyTable, with three columns and six rows:

Sub ParameterQCommand()  Dim cmd1 As Command  Dim rs1 As Recordset, str1 As String  Dim fldLoop As ADODB.Field  Dim prm1 As ADODB.Parameter, int1 As Integer

  ' Create and define command.
  Set cmd1 = New ADODB.Command
  With cmd1
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "Parameters [Lowest] Long;" & _
      "SELECT Column1, Column2, Column3 " & _
      "FROM MyTable WHERE Column1>=[Lowest]"
    .CommandType = adCmdText
  End With
  ' Create and define paramter.
  Set prm1 = cmd1.CreateParameter( _
               "[Lowest]", adInteger, adParamInput)
  cmd1.Parameters.Append prm1
  int1 = Trim(InputBox("Lowest value?", _
                       "MS Developer's Handbook"))
  prm1.Value = int1
  ' Run parameter query.
  cmd1.Execute
  ' Open recordset on cmd1 and print it out.
  Set rs1 = New ADODB.Recordset
  rs1.Open cmd1
  Do Until rs1.EOF
    str1 = "
    For Each fldLoop In rs1.Fields
      str1 = str1 & fldLoop.Value & Chr(9)
    Next fldLoop
    Debug.Print str1
    rs1.MoveNext
  Loop
End Sub

FIGURE 8: Using the Command object with a parameterized query.

The code in FIGURE 8 uses a three-part design. The first part creates and runs the command. It declares cmd1 as a Command object. Next, it sets three critical properties of the object. Every command must have a properly assigned ActiveConnection property before it can be run against a database. The Command object relies on a SQL statement to represent its query. The SQL statement syntax involves a Parameters declaration line that specifies the name and data type of the parameter. The WHERE clause should also reference one or more parameters, so the parameters can impact the return set. The procedure uses the CommandText property to accept the SQL statement, and the CommandType property to designate it as a SQL string.

The procedure’s second part adds the parameter, and appends the parameter to the command with ADO code. Invoke the CreateParameter method to add the parameter. The example uses three arguments with the CreateParameter method: the first names the parameter, the second designates a data type for the parameter, and the third declares a direction for the parameter. The adParamInput constant is actually the default that declares the parameter an input to the query. Other constants enable you to designate output, input/output, and return value parameters.

After creating the parameter, the example appends it to the Parameters collection for the command. Next, the example uses an InputBox function to get input from the user. The procedure’s second part concludes by invoking the Execute method for the command to develop a return set based on the SQL statement and the parameter’s value.

The example’s last part opens a Recordset object based on cmd1. It then prints the return set with tab delimiters (Chr(9)) in the Immediate window. Just as the first part will return any number of columns in the data source of command, the third part will print any number of columns in any number of rows.

The ADO Extensions for Data Definition Language and Security (ADOX) library supports — among other things — schema and security tasks (see FIGURE 9). This article restricts its focus to a subset of schema issues you can implement with the ADOX library. I’ll drill down to security and a broader set of schema topics in a subsequent article.

FIGURE 9: The ADO Extensions for Data Definition Language and Security (ADOX) Object Model.

You can use the ADOX library to manage objects and thereby modify the architecture of your application’s database design. As you can see, the Catalog object is at the top of the hierarchy and contains five collections. These collections are used to add and organize new objects in a catalog. The Table, Index, and Column objects each contain a Properties collection. These are used to manage the behavior of these objects within an application. The Users and Groups collections control permissions to ADOX objects, e.g. tables, views, and procedures.

Your applications can use the ADO and ADOX libraries together to build applications. For example, you can build Command objects with ADO, then save them as procedures with ADOX. You can search for the availability of a table before basing a Recordset on it. If a necessary table doesn’t exist, you can create it, and populate it with values. The ability of the ADOX library to define new data structures permits the library to serve as an alternative to SQL DDL.

As already mentioned, the Catalog object is the highest level container in the ADOX library. Its members specify the definition and security model for a database. The Catalog object has a single property, ActiveConnection, which stores the Connection object defining the database connection for the catalog. The Catalog object is the database’s container for tables, views, procedures, users, and groups within a database. You designate it when you declare the catalog.

Use the Catalog object’s Create method to assign a connection and source to a catalog. This method allows you to concurrently open a new database and gain access to its catalog.

One of the exciting features in Access 2000 is the availability of views and procedures in a local Access database. Views are row-returning, non-parameterized queries. Procedures, in contrast, are parameterized queries and queries that perform actions. You no longer have to work with a remote database to have these objects available.

The three examples in FIGURE 10 illustrate the use of the Catalog object in three typical kinds of contexts. The CatCon procedure opens the Northwind database. This procedure gives the current application programmatic access to the structure of the Northwind database. Notice that you need a Connection object for the catalog. This tells ADO which catalog to make available in a procedure. Assign the Connection object to the catalog via its ActiveConnection property. Once ADO knows which database to reference with a catalog, you have programmatic access to the contents of the catalog.

Sub CatCon()
  Dim cnn1 As New Connection
  Dim cat1 As New Catalog
  Dim proc1 As Procedure

  cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Program Files\Microsoft Office\" & _
    "Office\Samples\Northwind.mdb;"
  Set cat1.ActiveConnection = cnn1
  Debug.Print cat1.Tables(0).Name
End Sub

Sub CatCon2()
  Dim cnn1 As New Connection
  Dim cat1 As New Catalog

  Set cnn1 = CurrentProject.Connection
  Set cat1.ActiveConnection = cnn1
  Debug.Print cat1.Tables(1).Name; " "; cat1.Tables(1).Type
End Sub

Sub CatCon3()
  Dim cat1 As New ADOX.Catalog
  Dim tbl1 As Table

  On Error GoTo CatCon3Trap

  cat1.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\My Documents\NewDB.mdb"
  For Each tbl1 In cat1.Tables
    Debug.Print tbl1.Name
  Next tbl1

CatCon3Exit:
  Exit Sub

CatCon3Trap:
  If Err.Number = -2147217897 Then
    ' Database already exits
    Kill "c:\My Documents\NewDB.mdb"
    Resume
  Else
    Debug.Print Err.Number; Err.Description
    Resume CatCon3Trap
  End If
End Sub

FIGURE 10: Three sample procedures illustrating the use of the Catalog object.

The second example shows how to gain access to the catalog for the current project. It sets a Connection object equal to the connection for the current project. Then, it assigns that connection to the ActiveConnection property of the cat1 catalog. Finally, it prints the name of the second table in the catalog’s Table collection. The numeric index values for collections in the Catalog object start with 0. The code also prints the type of table.

There are six types of tables available to Table objects, determined by the Table object’s Type property, e.g. Access Table, Link, Pass-Through, System Table, Table, and View. The Table, Link, and Pass-Through tables are typical tables in an application. The Table type is a locally-defined table. The Pass-Through table is a linked table from an ODBC data source. A Link table denotes a linked table from a non-ODBC data source. The View type isn’t a table; it’s a result set that looks and acts like a table. The Access Table and System Table types are two varieties of system tables. You won’t normally view these in the Database window; however, you can expose them in the Database window by selecting the System objects check box on the View tab of the Options dialog box. You can achieve the same result programmatically with the SetOption method for the Access Application object.

The third example in FIGURE 10 uses the Catalog object’s Create method to make a new Access database file. It saves the database in the \My Documents folder. After creating the database, the procedure enumerates all the members of the Tables collection in the database’s catalog. If the file already exists, Access returns an error with a number of -2147217897. The third example traps this error, kills the old file, and runs the Create method again to create the file. Other errors just have the number and description written to the Immediate window before the procedure terminates gracefully.

A procedure is a parameterized row-returning query, or an action query that adds, deletes, or updates records. Procedures and views are similar because they can persist Command objects. Views represent non-parameterized row-returning queries, and procedures represent the remaining query types. The syntax for enumerating either is similar. While views are visible in the Access Database window, procedures are not.

Procedures have two pivotal properties: Name and Command. The Name property is a unique handle for referencing a procedure. The Command property makes accessible the properties of the Command behind the procedure. The CommandText property is particularly useful because it sets or returns the SQL statement the Command object implements. You can use this property to modify the SQL in the Command behind a procedure, or to view the SQL statement. Examining the SQL statement can acquaint you with the parameters you need to set to run a procedure.

Listing One (beginning on page X) contains four VBA procedures that illustrate how to program ADO procedures. The first, CreateCustomerByIDProc, illustrates how to create an ADO procedure. The CustomerByID procedure is a parameterized query that accepts a CustomerID and makes available the record for that customer from the Northwind Customers table. You can simply link to that table from any database file to make the record source available to the VBA procedures.

The CreateCustomerByIDProc procedure starts by setting a reference for the current database connection. It assigns that connection to the ActiveConnection property for the Command object that will become the basis of the stored procedure, CustomerByID. Next, it sets the CommandText property of the Command to the parameter query SQL for looking up the customer data by CustomerID. After defining the Command, it needs to save it as a stored procedure to persist it in the database. The first step to this goal is to assign the current project’s connection to the catalog’s ActiveConnection property. Once the catalog has a connection, you can simply append the command to the Procedures collection and give it a name of CustomerByID.

After you add several procedures to a catalog, you will frequently want to list them by name. The EnumerateProcs procedure accomplishes this task. It simply runs a For...Each loop through all the procedures in the active catalog. One way to set up for this is to assign the current project’s connection to a Connection object, then assign that object to the catalog’s ActiveConnection property.

The first procedure, CreateCustomerByIDProc, will fail if you run it a second time because ADO doesn’t automatically handle existing stored procedures when you want to create new ones with the same name. One way to circumvent this problem is to delete the old stored procedure before attempting to create a new one with the same name. The DeleteProc procedure illustrates how to accomplish this. As you can see, its approach is very similar to enumerating the stored procedures. Instead of printing the procedure names, this procedure examines them to find the one to delete. Then, the procedure applies the Delete method to the Procedures collection.

The final VBA procedure in Listing One illustrates how to run the CustomerByID stored procedure from within a VBA procedure to return the information for a desired customer. There are four elements to the solution. First, assign the stored procedure to a Command object. Second, create the parameter for the stored procedure and assign a value to it. The VBA procedure accepts a value for CustID from a calling procedure. Third, Execute the Command object representing the query. Fourth, open a Recordset on the return set from the Command object. This will always be a single record for the Northwind Customer table because CustID is a primary key for the table. However, a parameter query persisted as a stored procedure can return one or more records.

The Jet Replication Object (JRO) model facilitates database replication between Jet databases and SQL Server databases. FIGURE 11 shows a schematic of the JRO model. JRO supports three main functions. These are:

Create and synchronize replicas.

Compact a database with options, such as passwords and encryption.

Refresh memory cache by writing to a database from memory and reading from a database to memory.

FIGURE 11: The Jet Replication Object (JRO) Model.

As it fulfills this role, Jet replication provides several specific benefits. First, it simplifies distributing software based on Access objects; with Jet replication, you can readily transfer copies of new forms and reports from a design master to other replicas in a replica set. Second, Jet replication facilitates transferring updates of one replica in a replica set to all others. This reduces problems of concurrency because users can add data to different replicas. In off-peak periods (or literally anytime), replica synchronization can merge changes made to different members of the replica set. Third, Jet replication supports traveling workers with laptops well because a computer need not be continually connected in order to copy its entries and receive updates from a central database. Fourth, if there is an especially heavy user group, a special replica can support that group. By running each replica on a separate machine, you balance the load across two or more computers. Finally, you can use replicas to hold backup copies of the data in a database. You can readily use a database application with this approach even while the system is in active use.

A replica is a copy of a database. There are three types of replicas: design master, full replica, and partial replica. The ReplicaType property of the Replica object corresponds to these types. The design master can share its data and its structure with the other replicas in a replica set. There can only be one design master replica at a time, but a replica system can change which replica in a set serves that role over time. This feature is particularly convenient if your design master becomes damaged or unavailable for an extended period. A full replica can send and receive updates from other replicas, but it cannot share its structure. This means any forms developed explicitly for a replica remain at that replica (unless you temporarily make that replica the design master). Partial replicas can increase performance by requiring the replication of just a part of a database. These are particularly convenient for traveling workers who only need a portion of the central database delivered to them over a telephone line. Use the FilterCriteria property of a replica to specify criteria for a full replica to replicate its contents with a partial replica.

Several important Replica object methods include the MakeReplicable, CreateReplica, Synchronize, and PopulatePartial. Use the MakeReplicable method to make a database replicable. This method enables you to start a replica set. Typically, the first database made replicable in a replica set is the design master, but that role is transferable to other replicas through the DesignMasterID property. Use the CreateReplica method to add a new member to a replica set. You can optionally specify either a full or partial replica type as you create the new member. You can also specify other replica properties, such as visibility, priority, and updatability that impact the performance of a member in a replica set.

Invoke the Synchronize method to merge the updates from two separate replicas. You can synchronize two replicas directly over a LAN or WAN, indirectly via synchronizers that collect updates and share them with local replica members, or indirectly over the Internet. The Synchronize method permits replication to Microsoft SQL Server replica members. The PopulatePartial method adds data to a partial replica from a full replica according to its filter. If the partial replica has data, PopulatePartial empties it before fully re-populating from the full replica.

As you merge the changes between replicas, conflicts will occasionally occur. These can have multiple causes, including two replicas changing the same field in the same record between synchronizations, or changes to key values that orphan records. Access 2000 has built-in conflict-resolution rules and a Conflict Resolution wizard. Beyond that, you can develop your own automatic conflict resolution functions. The Replica object has a ConflictFunction property for denoting the entry point into a custom conflict-resolution system.

The JRO model also includes the JetEngine object for programmatically controlling compacting and refreshing data from the memory cache. The JetEngine object has two methods — one for each of its roles. Use the CompactDatabase method to manage encryption, copying from one version of Access to another, and searching for replica set members during a compact and repair operation. The RefreshCache method targets multi-user systems. The RefreshCache method forces a write to the database and releases memory locks.

This article introduces you to the new data access model that ships with Access 2000. It takes a separate look at the ADO, ADOX, and JRO libraries. Subsequent articles will drill down into each of these libraries, but this introduction gives you a running start with programming examples for the first two libraries. The subsequent articles will take a more detailed look at example applications for the objects in each library. You will also learn how to program ADO events to manage asynchronous database operations.

Rick Dobson, Ph.D., is president of CAB, Inc. CAB offers Office/Web/database services as well as support for Office-based automated accounting packages. Rick is the Microsoft Press author for Programming Microsoft Access 2000. Visit the CAB site at http://www.cabinc.net for samples, presentations, full-length articles, and prizes.

Sub CreateCustomerByIDProc()
  Dim cnn1 As New ADODB.Connection
  Dim cmd1 As New ADODB.Command
  Dim cat1 As New ADOX.Catalog

  ' Open the Connection.
  Set cnn1 = CurrentProject.Connection
  ' Create the parameterized command; specific to Jet.
  Set cmd1.ActiveConnection = cnn1
  cmd1.CommandText = "PARAMETERS [CustId] Text;" & _
    "SELECT * FROM Customers WHERE CustomerId = [CustId]"
  ' Open the Catalog.
  Set cat1.ActiveConnection = cnn1
  ' Create the new Procedure.
  cat1.Procedures.Append "CustomerById", cmd1
End Sub

Sub EnumerateProcs()
  Dim cnn1 As ADODB.Connection
  Dim cat1 As New ADOX.Catalog
  Dim pro1 As ADOX.Procedure

  Set cnn1 = CurrentProject.Connection
  Set cat1.ActiveConnection = cnn1
  For Each pro1 In cat1.Procedures
    Debug.Print pro1.Name
  Next pro1
End Sub

Sub DeleteProc(procName As String)
  Dim cnn1 As ADODB.Connection
  Dim cat1 As New ADOX.Catalog
  Dim pro1 As ADOX.Procedure

  Set cnn1 = CurrentProject.Connection
  Set cat1.ActiveConnection = cnn1
  For Each pro1 In cat1.Procedures
    If pro1.Name = "CustomerByID" Then
      cat1.Procedures.Delete procName
    End If
  Next pro1
End Sub

Sub RunCustomerByID(CustID As String)
  Dim cnn1 As ADODB.Connection
  Dim cat1 As New ADOX.Catalog
  Dim pro1 As ADOX.Procedure
  Dim cmd1 As ADODB.Command
  Dim prm1 As ADODB.Parameter
  Dim rst1 As Recordset

  Set cnn1 = CurrentProject.Connection
  Set cmd1 = New ADODB.Command

  With cmd1
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "CustomerByID"
    .CommandType = adCmdStoredProc
  End With

  Set prm1 = cmd1.CreateParameter("[CustID]", adWChar, _
                                  adParamInput, 20)
  cmd1.Parameters.Append prm1
  prm1.Value = CustID
  cmd1.Execute

  Set rst1 = New ADODB.Recordset
  rst1.Open cmd1

  Debug.Print rst1.Fields(0), rst1.Fields(1)
End Sub