Enhanced Auto-Increment (Counter) Columns

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 document.

Sub ADOCreateEnhancedAutoIncrColumn()

   Dim cat As New ADOX.Catalog

   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.

Replication

Replica Visibility

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 identify 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

Replica Priority

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

Indirect Synchronization

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 dropbox 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

Synchronizing Changes with a Microsoft SQL Server

JRO supports synchronizing changes between a Microsoft SQL Server and a Microsoft Jet database. Note, 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

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".

Obsolete Properties and Methods

This following section describes DAO properties and methods that don't map to properties or methods in ADO, ADOX, or JRO. However, that does not imply that the functionality provided by the DAO properties and methods is not available in ADO, ADOX, or JRO.

Below, each property or method not exposed is listed and followed by a description of why it is not exposed and, if applicable, how to get the equivalent functionality using ADO, ADOX, or JRO.

Object	Property/Method	Explanation
DBEngine	DefaultType	DAO 3.5 introduced ODBCDirect as a means to work with ODBC data sources without loading the Microsoft Jet database engine. To use ODBCDirect, you set the DefaultType and/or Type properties to dbUseODBC. As discussed in the "Introduction" section, ADO has a different approach to enabling access to ODBC data sources as well as enabling native access to various data sources such as Microsoft SQL Server. ADO allows users to choose which OLE DB provider they want to use to access the data. So, to work with ODBC data sources without loading the Microsoft Jet database engine, specify MSDASQL rather than Microsoft.Jet.OLEDB.4.0 as the provider name.
DBEngine	DefaultPassword	In DAO, the DefaultPassword property assigns the default password for a new workspace created on the DBEngine object. Such a property doesn't make sense in the context of the ADO connection model.
DBEngine	DefaultUser	In DAO, the DefaultUser property assigns the default user id for a new workspace created on the DBEngine object. Such a property doesn't make sense in the context of the ADO connection model.
DBEngine	RegisterDatabase	The ODBC API provides this functionality. Users should call the ODBC API directly.
DBEngine	RepairDatabase	The functionality found in RepairDatabase has been incorporated into CompactDatabase in Microsoft Jet 4.0. Compacting a database will also repair it.
Workspace	Name	The Name property identifies a Workspace in the Workspaces collection.  Since there is no concept of a collection of connections in ADO, this property doesn’t apply.
Workspace	Type	See comments for DBEngine DefaultType property.
Database	V1xNullBehavior	Support for version 1.0 Null behavior has been dropped. This functionality is obsolete.
Recordset	CacheStart	Using ADO and the Microsoft Jet provider, it is not necessary to explicitly set the CacheStart property. The Microsoft Jet provider will start caching from the current record.
Recordset	Edit	The process of updating records has been simplified with ADO such that Edit is not needed. With DAO, you had to call the Edit method to put the Recordset into edit mode before modifying a value otherwise an error would occur. With ADO, modifying a value automatically puts the Recordset in edit mode.
Recordset	FillCache	As with the CacheStart property, it is not necessary with ADO and the Microsoft Jet provider to explicitly set the FillCache property. The Microsoft Jet provider will start caching from the current record.
Recordset	LastModified	After modifying a record (or creating a new one) and calling the Update method to save the changes, DAO users had to set the Bookmark property to the LastModified property to ensure that the current record was the record they had just modified. With ADO, this is not necessary as ADO automatically ensures that the current record stays the same after a call to Update.
Recordset	Name	ADO does not have a recordset collection so there is no need for a name apart from the name the user has given the Recordset when it was declared.
Recordset	Restartable	All ADO Recordset objects are restartable so a property indicating whether or not it can be resarted is not necessary.
QueryDef	ReturnsRecords	With DAO QueryDef  objects it was necessary to know whether the query returned records in order to execute the query. If the query returned records, you had to use the OpenRecordset method to execute the query. If it did not return records, you had to use the Execute method. With ADO you no longer need to know whether or not the query returns records in order to execute it because the Execute method is used in either case. If the query returns records, the Execute method returns a Recordset object otherwise it returns Nothing.
Container	Name	The Container object's functionality can be found as part of the User and Group objects.
Document	Name	The Document object's functionality can be found as properties on the objects themselves or as part of the User and Group objects.
User	Password	The user's password is part of the connection string.
User	PID	PIDs are only required to rebuild a corrupted or destroyed workgroup information file. Keep a current backup of workgroup information files to avoid this, or use SQL DDL commands to specify the PID for a new user.
Group	PID	PIDs are only required to rebuild a corrupted or destroyed workgroup information file. Keep a current backup of workgroup information files to avoid this, or use SQL DDL commands to specify the PID for a new group.

Conclusion

In the past, DAO made working with tabular databases easier than calling DLL functions by wrapping up those functions with objects. This made data access relatively simple for most developers. ADO uses this strength of DAO and expands on it. ADO uses OLE DB to work with data sources, so you will be able to reach many more data sources than with DAO. Many of these data sources won't be stored as traditional, tabular databases. In those cases, using DAO won't be possible. ADO is similar enough to DAO that you won't need to relearn an entire object model to handle these new data sources. Once you've learned ADO, you're ready for the future of data access.

Appendix A: DAO to ADO Quick Reference

The table provided below is intended to be a quick reference for determining how to map DAO properties and methods to ADO, ADOX, and JRO properties and methods. However, it is not intended to imply a direct, one-to-one mapping between the properties and methods listed. There may be subtle, or not so subtle, differences between the mapped properties and methods. For more detailed information on the ADO, ADOX, and JRO properties and methods see the documentation for the object model. Use the information provided earlier in this document to map the code for common tasks that are performed using DAO to ADO, ADOX, and JRO code.

DAO Object	Property/Method	ADO/ADOX/JROModel	Object	Property/Method
DBEngine	DefaultType1	N/A	N/A	N/A
DBEngine	DefaultPassword1	N/A	N/A	N/A
DBEngine	DefaultUser1	N/A	N/A	N/A
DBEngine	IniPath	ADO	Connection	Jet OLEDB:Registry Path2
DBEngine	LoginTimeout	ADO	Connection	ConnectionTimeout
DBEngine	SystemDB	ADO	Connection	Jet OLEDB:System Database2
DBEngine	Version	ADO	Connection	Version
DBEngine	BeginTrans	ADO	Connection	BeginTrans
DBEngine	CommitTrans	ADO	Connection	CommitTrans
DBEngine	Rollback	ADO	Connection	RollbackTrans
DBEngine	CompactDatabase	JRO	JetEngine	CompactDatabase
DBEngine	CreateDatabase	ADOX	Catalog	Create
DBEngine	CreateWorkspace	ADO	Connection	Open
DBEngine	Idle	JRO	JetEngine	RefreshCache
DBEngine	OpenDatabase	ADO	Connection	Open
DBEngine	RegisterDatabase1	N/A	N/A	N/A
DBEngine	RepairDatabase1	N/A	N/A	N/A
DBEngine	SetOption	ADO	Connection	Properties3
Workspace	IsolateODBCTrans	ADO	Connection	Isolation Levels2
Workspace	LoginTimeout	ADO	Connection	ConnectionTimeout
Workspace	Name1	N/A	N/A	N/A
Workspace	Type1	N/A	N/A	N/A
Workspace	UserName	ADO	Connection	User Id2
Workspace	BeginTrans	ADO	Connection	BeginTrans
Workspace	CommitTrans	ADO	Connection	CommitTrans
Workspace	Rollback	ADO	Connection	RollbackTrans
Workspace	Close	ADO	Connection	Close
Workspace	CreateDatabase	ADOX	Catalog	Create
Workspace	CreateGroup	ADOX	Groups	Append
Workspace	CreateUser	ADOX	Users	Append
Workspace	OpenDatabase	ADO	Connection	Open
Database	CollatingOrder	ADO	Connection	Locale Identifier2
Database	Connect	ADO	Connection	ConnectionString
Database	Name	ADO	Connection	Data Source2
Database	QueryTimeout	ADO	Connection	CommandTimeout
Database	Replicable	JRO	Replica	MakeReplicable
Database	ReplicaId	JRO	Replica	ReplicaId
Database	ReplicationConflictFunction	JRO	Replica	ConflictFunction
Database	RecordsAffected	ADO	Connection	Execute(RecordsAffected)
Database	Transactions	ADO	Connection	Transaction DDL2
Database	Updatable	ADO	Connection	Mode
Database	V1xNullBehavior	N/A	N/A	N/A
Database	Version	ADO	Connection	DBMS Version2
Database	Close	ADO	Connection	Close
Database	CreateProperty	N/A	N/A	Not supported in this release
Database	CreateQueryDef	ADOX	Command	Dim New4
Database	CreateRelation	ADOX	Key	Dim New4
Database	CreateTableDef	ADOX	Table	Dim New4
Database	Execute	ADO	Connection	Execute
Database	MakeReplica	JRO	Replica	CreateReplica
Database	NewPassword	ADOX	Catalog	Modify
Database	OpenRecordset	ADO	Recordset	Open
Database	PopulatePartial	JRO	Replica	PopulatePartial
Database	Synchronize	JRO	Replica	Synchronize
Recordset	AbsolutePosition	ADO	Recordset	AbsolutePosition
Recordset	BOF	ADO	Recordset	BOF
Recordset	EOF	ADO	Recordset	EOF
Recordset	Bookmark	ADO	Recordset	Bookmark
Recordset	Bookmarkable	ADO	Recordset	Supports
Recordset	CacheSize	ADO	Recordset	Jet OLEDB:Fat Cursor Cache Size2
Recordset	CacheStart1	N/A	N/A	N/A
Recordset	DateCreated	ADOX	Table	DateCreated
Recordset	LastUpdated	ADOX	Table	DateModified
Recordset	EditMode	ADO	Recordset	EditMode
Recordset	Filter	ADO	Recordset	Filter
Recordset	Index	ADO	Recordset	Index
Recordset	LastModified1	N/A	N/A	N/A
Recordset	LockEdits	ADO	Recordset	LockType
Recordset	Name1	N/A	N/A	N/A
Recordset	NoMatch	ADO	Recordset	Find
Recordset	PercentPosition	N/A	N/A	Not supported in this release.
Recordset	RecordCount	ADO	Recordset	RecordCount
Recordset	RecordStatus	ADO	Recordset	EditMode
Recordset	Restartable1	N/A	N/A	N/A
Recordset	Sort	ADO	Recordset	Sort
Recordset	Transactions1	N/A	N/A	N/A
Recordset	Type	ADO	Recordset	CursorType
Recordset	Updatable	ADO	Recordset	Recordset.Supports(adUpdate)
Recordset	ValidationRule	ADOX	Table	ValidationRule
Recordset	ValidationText	ADOX	Table	ValidationText
Recordset	AddNew	ADO	Recordset	AddNew
Recordset	CancelUpdate	ADO	Recordset	CancelUpdate
Recordset	Clone	ADO	Recordset	Clone
Recordset	Close	ADO	Recordset	Close
Recordset	CopyQueryDef	ADO	Recordset	Source
Recordset	Delete	ADO	Recordset	Delete
Recordset	Edit1	N/A	N/A	N/A
Recordset	FillCache1	N/A	N/A	N/A
Recordset	FindFirst	ADO	Recordset	Find
Recordset	FindLast	ADO	Recordset	Find
Recordset	FindNext	ADO	Recordset	Find
Recordset	FindPrevious	ADO	Recordset	Find
Recordset	GetRows	ADO	Recordset	GetRows
Recordset	Move	ADO	Recordset	Move
Recordset	MoveFirst	ADO	Recordset	MoveFirst
Recordset	MoveLast	ADO	Recordset	MoveLast
Recordset	MoveNext	ADO	Recordset	MoveNext
Recordset	MovePrevious	ADO	Recordset	MovePrevious
Recordset	OpenRecordset	ADO	Recordset	Open
Recordset	Requery	ADO	Recordset	Requery
Recordset	Seek	ADO	Recordset	Seek
Recordset	Update	ADO	Recordset	Update
QueryDef	CacheSize	ADO	Command	Jet OLEDB:Fat Cursor Cache Size2
QueryDef	Connect	ADO	Command	Jet OLEDB:Link datasource2
QueryDef	DateCreated	ADOX	Procedure	DateCreated
QueryDef	LastUpdated	ADOX	Procedure	DateModified
QueryDef	KeepLocal	JRO	Replica	Get/SetObjectReplicability
QueryDef	LogMessages	N/A	N/A	Not supported in this release.
QueryDef	MaxRecords	ADO	Command	MaxRecords
QueryDef	Name	ADOX	Procedure	Name
QueryDef	ODBCTimeout	ADO	Command	Jet OLEDB:ODBC Command Timeout2
QueryDef	RecordsAffected	ADO	Command	Execute(RecordsAffected)
QueryDef	Replicable	JRO	Replica	Get/SetObjectReplicability
QueryDef	ReturnsRecords1	N/A	N/A	N/A
QueryDef	SQL	ADO	Command	CommandText
QueryDef	Type	N/A	N/A	Not supported in this release.
QueryDef	Updatable	N/A	N/A	N/A
QueryDef	Close	ADO/X	Command / Procedure	Set to Nothing
QueryDef	CreateProperty	N/A	N/A	Not supported in this release
QueryDef	Execute	ADO	Command	Command.Execute
QueryDef	OpenRecordset	ADO	Recordset	Open
TableDef	Attributes	ADOX	Table	Properties5
TableDef	ConflictTable	JRO	Replica	ConflictTables
TableDef	Connect	ADOX	Table	Jet OLEDB:Link Datasource2
TableDef	DateCreated	ADOX	Table	DateCreated
TableDef	LastUpdated	ADOX	Table	DateModified
TableDef	KeepLocal	JRO	Replica	Get/SetObjectReplicability
TableDef	Name	ADOX	Table	Name
TableDef	RecordCount	ADO	Connection	OpenSchema6
TableDef	Replicable	JRO	Replica	Get/SetObjectReplicability
TableDef	ReplicaFilter	JRO	Filter	FilterCriteria
TableDef	SourceTableName	ADOX	Table	Jet OLEDB:Remote Table Name2
TableDef	Updatable	N/A	N/A	N/A
TableDef	ValidationRule	ADOX	Table	Jet OLEDB:Table Validation Rule2
TableDef	ValidationText	ADOX	Table	Jet OLEDB:Table Validation Text2
TableDef	CreateField	ADOX	Columns	Append
TableDef	CreateIndex	ADOX	Indexes	Append
TableDef	CreateProperty	N/A	N/A	Not supported in this release.
TableDef	OpenRecordset	ADO	Recordset	Open
TableDef	RefreshLink	ADOX	Table	Jet OLEDB:Create Link2
Field	AllowZeroLength	ADOX	Column	Jet OLEDB:Allow Zero Length2
Field	Attributes	ADOX	Column	Properties5
Field	CollatingOrder	ADO/X	Field/Column	Collation Name2
Field	DataUpdatable	ADO	Field	Attributes
Field	DefaultValue	ADOX	Column	DefaultValue
Field	FieldSize	ADO	Field	ActualSize
Field	ForeignName	ADO	Column	RelatedColumn
Field	Name	ADO/X	Field/Column	Name
Field	OrdinalPosition	N/A	N/A	Not supported in this release.
Field	Required	ADO/X	Field/Column	Attributes
Field	Size	ADO/X	Field/Column	DefinedSize
Field	SourceField	N/A	N/A	Not supported in this release.
Field	SourceTable	N/A	N/A	Not supported in this release.
Field	Type	ADO/X	Field/Column	Type
Field	ValidateOnSet	ADOX	Column	Jet OLEDB:Validate On Set2
Field	ValidationRule	ADOX	Column	Jet OLEDB:Column Validation Rule2
Field	ValidationText	ADOX	Column	Jet OLEDB:Column Validation Text2
Field	Value	ADO	Field	Value
Index	Clustered	ADOX	Index	Clustered
Index	DistinctCount	ADO	Connection	OpenSchema6
Index	Foreign	ADOX	Key	Type
Index	IgnoreNulls	ADOX	Index	IndexNulls
Index	Name	ADOX	Index	Name
Index	Primary	ADOX	Index	PrimaryKey
Index	Required	ADOX	Index	Index.IndexNulls
Index	Unique	ADOX	Index	Unique
Index	CreateField	ADOX	Column	Dim New3
Index	CreateProperty	N/A	N/A	Not supported in this release
Relation	Attributes	ADOX	Key	Properties5
Relation	ForeignTable	ADOX	Key	RelatedTable
Relation	Name	ADOX	Key	Name
Relation	PartialReplica	JRO	Filter	FilterCriteria
Relation	Table	ADOX	Key	Parent Table Object7
Relation	CreateField	ADOX	Column	Dim New3
User	Name	ADOX	User	Name
User	Password	N/A	N/A	N/A
User	PID	N/A	N/A	N/A
User	CreateGroup	ADOX	Groups	Append
User	NewPassword	ADOX	User	ChangePassword
Group	Name	ADOX	Group	Name
Group	PID	N/A	N/A	N/A
Group	CreateUser	ADOX	Users	Append
Container	AllPermissions	ADOX	User/Group	GetPermissions8
Container	Inherit	ADOX	User/Group	Get/SetPermissions
Container	Name1	N/A	N/A	N/A
Container	Owner	ADOX	Catalog	Get/SetObjectOwner
Container	Permissions	ADOX	User/Group	Get/SetPermissions
Container	UserName	ADOX	User/Group	Get/SetPermissions
Document	AllPermissions	ADOX	User	GetPermissions8
Document	Container1	N/A	N/A	N/A
Document	DateCreated	ADOX	Applicable Object	DateCreated
Document	LastUpdated	ADOX	Applicable Object	DateModified
Document	KeepLocal	JRO	Replica	Get/SetObjectReplicability
Document	Name1	N/A	N/A	N/A
Document	Owner	ADOX	Catalog	Get/SetObjectOwner
Document	Permissions	ADOX	User/Group	Get/SetPermissions
Document	Replicable	JRO	Replica	Get/SetObjectReplicability
Document	UserName	ADOX	User/Group	Get/SetPermissions

1. This property or method does not map to ADO, ADOX, or JRO. See the section "Obsolete Properties and Methods" earlier in this document.

2. This property is part of the object's Properties collection.

3. See the section "Setting Microsoft Jet Options" for more information on mapping the SetOption method to the Connection properties.

4. The object is creatable. Use the Visual Basic for Applications Dim New syntax to create a new object.

5. The DAO Attributes property is a bitmask of a number of constants that map to several properties in the ADOX model. For a detailed mapping of the DAO constants to ADOX properties, see the sections "Creating and Modifying Tables" and "Enforcing Referential Integrity."

6. The record count for a table can be retrieved via the Cardinality column in the TABLES_INFO schema rowset and the distinct count for an index can be retrieved via the Cardinality column in the INDEXES schema rowset.

7. The primary table in a relationship is represented in ADOX by the Table object that contains a primary Key object in its Keys collection. Primary keys are specified by a Type property value of adKeyPrimary.

8. Unlike the AllPermissions property in DAO, the GetPermissions property in ADOX does not add in the permissions that the user inherits from the groups to which that user belongs. This information must be extracted from the Group objects.