The following sections describe new features in ADO, ADOX, JRO, and the Microsoft Jet Provider. The functionality exposed by these features is not available in DAO. This section is not intended as a complete list of additional features exposed in ADO, but rather serves to highlight some of the new functionality.
Often, a developer finds a need for a place to temporarily store some data, or wants some data to act like it came from a server so it can participate in data binding in a user interface.
ADO (in conjunction with the Cursor Service for OLE DB) enables the developer to build an empty Recordset object by specifying column information and calling Open. The following code demonstrates this:
Sub ADOCreateRecordset()
Dim rst As New ADODB.Recordset
rst.CursorLocation = adUseClient
' Add Some Fields
rst.Fields.Append "dbkey", adInteger
rst.Fields.Append "field1", adVarChar, 40, adFldIsNullable
rst.Fields.Append "field2", adDate
' Create the Recordset
rst.Open , , adOpenStatic, adLockBatchOptimistic
' Add Some Rows
rst.AddNew Array("dbkey", "field1", "field2"), _
Array(1, "string1", Date)
rst.AddNew Array("dbkey", "field1", "field2"), _
Array(2, "string2", #1/6/1992#)
' Look at the values -
' a value of 1 for status column = newly record
rst.MoveFirst
Debug.Print "Status", "dbkey", "field1", "field2"
Do Until rst.EOF
Debug.Print rst.Status, rst!dbkey, rst!field1, rst!field2
rst.MoveNext
Loop
' Commit the rows without ActiveConnection
' set resets the status bits
rst.UpdateBatch adAffectAll
' Change the first of the two rows
rst.MoveFirst
rst!field1 = "changed"
' Now look at the status, first row shows 2 (modified row),
' second shows 8 (no modifications)
' Also note that the OriginalValue property shows the value
' before the modification
rst.MoveFirst
Do Until rst.EOF
Debug.Print
Debug.Print rst.Status, rst!dbkey, rst!field1, rst!field2
Debug.Print , rst!dbkey.OriginalValue, _
rst!field1.OriginalValue, rst!field2.OriginalValue
rst.MoveNext
Loop
End Sub
Another feature of a creatable recordset is that pending operations can be committed to the Recordset. Any time UpdateBatch is called on a client cursor that has no ActiveConnection set, the changes in the affected row (controlled by the AffectedRows parameter) will be committed to the buffer and the Status flags will be reset. The same applies to CancelBatch, except the changes in the buffer will be reverted and the flag will be reset.
Microsoft Data Links provides a graphical user interface that enables the user to create, edit, and organize connections to a data source. A Data Link file for c:\nwind.mdb can be created as follows:
The following code shows how to use the data link to open the connection rather than providing the connection information directly:
Sub ADOUseExistingDataLink()
' Opens an ADO Connection using a Data Links file (UDL)
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cnn.Open "File Name=.\NorthWind.udl;"
rs.Open "Customers", cnn, adOpenKeyset, adLockReadOnly
rs.MoveLast
Debug.Print rs.RecordCount
rs.Close
cnn.Close
End Sub
It is also possible to use Microsoft Data Links to prompt the user for connection information. The following code demonstrates how to launch the Microsoft Data Links UI from code. In order to run this code, you'll need to add a reference to Microsoft OLE DB Service Component 1.0 Type Library in your project.
Private Sub Command1_Click()
Dim cnn As New ADODB.Connection
Dim dl As New DataLinks
On Error Resume Next
dl.hWnd = Me.hWnd
If dl.PromptEdit(cnn) Then
cnn.Open
End If
cnn.Close
End Sub
The previous code example could be modified to first specify a default value for the provider and data source.
Private Sub Command2_Click()
Dim cnn As New ADODB.Connection
8 Dim dl As New DataLinks
On Error Resume Next
cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
cnn.Properties("Data Source") = ".\NorthWind.mdb"
dl.hWnd = Me.hWnd
If dl.PromptEdit(cnn) Then
cnn.Open
End If
cnn.Close
End Sub
Many database maintenance activities require that the administrator have exclusive access to the database. The database cannot be opened exclusively if other people already have the database open. With DAO the administrator had no way of determining who was logged on to the database, making it difficult to determine who was blocking the administrator's attempt to open the database exclusively.
ADO and the Microsoft Jet Provider expose a schema rowset that contains information about who currently has the database open. This is a provider-specific schema rowset named DBSCHEMA_JETOLEDB_USERROSTER. The following code demonstrates how to open this schema rowset using ADO:
Sub ADOUserRoster()
Dim cnn As New ADODB.Connection
Dim rst As ADODB.Recordset
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Open the user roster schema rowset
Set rst = cnn.OpenSchema(adSchemaProviderSpecific, , _
JET_SCHEMA_USERROSTER)
' Print the results to the debug window
Debug.Print rst.GetString
cnn.Close
End Sub
The first parameter, QueryType, to the ADO OpenSchema method takes an enumeration value. Values are defined for the schema rowsets defined in the OLE DB specification. To use a provider-specific schema rowset such as DBSCHEMA_JETOLEDB_USERROSTER, you must specify adProviderSpecific and then provide the GUID for the schema rowset as the last parameter. In this example the constant JET_SCHEMA_USERROSTER is used in place of the GUID. This constant is contained in the JetOLEDBConstants.txt file included in "Appendix C: Microsoft Jet 4.0 OLE DB Provider-Defined Property Values".
The following table describes the information contained in each column of the schema rowset.
| Column | Description |
| COMPUTER_NAME | The name of the workstation as specified using the Network icon in Control Panel. |
| LOGIN_NAME | The name of the user used to log on to the database if the database has been secured; otherwise, the default value will be Admin. |
| CONNECTED | True, if there is a corresponding user lock in the .ldb file. |
| SUSPECTED_STATE | True, if the user has left the database in a suspect state; otherwise, Null. |
Microsoft Jet 4.0 includes enhanced support for auto-increment columns that allows you to specify an initial value for the column, also known as the seed value, as well as a value by which to increment the column.
The following code demonstrates how to create a new auto-increment column with an initial value of 10 and an increment value of 100. It assumes the Contacts table already exists. To create this table, run the ADOCreateTable example code in the section "Creating and Modifying Tables" earlier in this article.
Sub ADOCreateEnhancedAutoIncrColumn()
Dim cat As New ADOX.Catalog
8 Dim col As New ADOX.Column
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Create the new auto increment column
With col
.Name = "ContactId"
.Type = adInteger
Set .ParentCatalog = cat
.Properties("AutoIncrement") = True
.Properties("Seed") = CLng(10)
.Properties("Increment") = CLng(100)
End With
' Append the column to the table
cat.Tables("Contacts").Columns.Append col
Set cat = Nothing
End Sub
In addition to specifying seed and increment values when the column is created, they can be modified for existing auto-increment columns. Use caution when modifying these values for existing columns as it is possible to create conflicts with existing values. For example, if the table already contains values 1 through 10 in the column, it is possible to set the seed value to 5.
JRO introduces a new property of a replica that is used to indicate the visibility of a replica. The visibility determines which replicas that replica can synchronize with. A replica's visibility may be Global, Local, or Anonymous. The replica's visibility is set when the replica is first created. Once the replica is created the visibility cannot be changed.
A Global replica can synchronize with any other replica in the set. Changes at a Global replica are fully tracked. From a Global replica, you can create replicas that are Global, Local, or Anonymous. Replicas created from a Global replica are global by default.
A Local replica can synchronize only with its parent, a Global replica, and will not be permitted to synchronize with other replicas in the replica set. The parent will proxy any replication conflicts and errors for the Local replica. Other replicas will not be aware of the Local replica. The parent replica can schedule a synchronization with a Local replica. All replicas created from a Local replica will also be local and inherit the same parent replica.
An Anonymous replica can synchronize with its parent, a Global replica. These are replicas who, say, subscribe by way of the Internet, who do not have any particular identity, but instead proxy their identity for updates to the publishing replica. A Global replica will not be able to schedule synchronizations to an Anonymous replica. Anonymous replicas provide a way of getting around the "limit on number of replicas" problem. In addition, it helps to keep out unnecessary topology information about replicas that participate only occasionally. All replicas created from an Anonymous replica will also be anonymous and inherit the same parent replica.
The following code demonstrates how to create a new Anonymous replica:
Function JROMakeAnonReplica(strReplicableDB As String, _
strNewReplica As String) As Integer
Dim repMaster As New JRO.Replica
repMaster.ActiveConnection = strReplicableDB
repMaster.CreateReplica strNewReplica, "Replica of " & _
strReplicableDB, , jrRepVisibilityAnon
Set repMaster = Nothing
End Function
JRO introduces a new property of a replica that is used to indicate the relative importance of a replica during synchronization. If conflicts are encountered during synchronization the replica with the highest priority wins.
The following code demonstrates how to set the priority when creating a new replica:
Function JROMakeAdditionalReplica2(strReplicableDB As String, _
strNewReplica As String, intPriority As Integer) As Integer
Dim repMaster As New JRO.Replica
repMaster.ActiveConnection = strReplicableDB
repMaster.CreateReplica strNewReplica, "Replica of " & _
strReplicableDB, , , intPriority
Set repMaster = Nothing
End Function
With a direct synchronization, your machine is tied up until the synchronization is complete. On fast local area networks (LANs) this may not be an issue. However, synchronization over a slow wide area network (WAN) may take many minutes or more. Indirect synchronization was designed for this scenario. For an indirect synchronization, the synchronizer leaves the changes in a drop box and control returns to the application. The synchronizer for the other replica will then pick up the changes and apply them.
The following code demonstrates how to perform an indirect synchronization:
Sub JROTwoWayIndirectSync()
Dim repMaster As New JRO.Replica
repMaster.ActiveConnection = ".\NorthWind.mdb"
' Sends changes made in each replica to the other.
repMaster.Synchronize ".\NewNorthWind.mdb", jrSyncTypeImpExp, _
jrSyncModeIndirect
Set repMaster = Nothing
End Sub
JRO supports synchronizing changes between a Microsoft SQL Server and a Microsoft Jet database. Note that the Microsoft Jet database and its synchronizer must already be configured to support the replication to SQL Server.
The following code demonstrates how to perform a Microsoft Jet to SQL synchronization:
Sub JROJetSQLSync()
Dim repMaster As New JRO.Replica
repMaster.ActiveConnection = ".\Pubs.mdb"
' Sends changes made in each replica to the other.
repMaster.Synchronize "", jrSyncTypeImpExp, jrSyncModeDirect
Set repMaster = Nothing
End Sub
Notice the TargetReplica parameter for the Synchronize method is an empty string ("") and the SyncMode is jrSyncModeDirect. Leaving the TargetReplica blank indicates that this is a Microsoft Jet-to-SQL Server synchronization. All Microsoft Jet-to-SQL Server synchronizations are direct.
Column-level conflict resolution lets you merge two records and only report a conflict if simultaneous changes have been to the same field. If you frequently have overlapping updates in the same row, setting this option could increase performance.
This option is set when a database is made replicable—it cannot be changed once the process of making the database replicable is complete. Column-level conflict resolution is turned on by default.
The following code demonstrates how to turn on column-level tracking when making a database replicable:
Sub JROMakeDesignMaster2()
Dim repMaster As New JRO.Replica
repMaster.MakeReplicable ".\NorthWind.mdb", True
Set repMaster = Nothing
End Sub
For an example of how to turn off column-level tracking when making a database replicable, see the JRO code example in the section "Making a Database Replicable."