Implementing Merge Replication to Access Subscribers

When using releases of Microsoft® Access later than Access 8, you have a choice between using the SQL Server 2000 Desktop Engine or Microsoft Jet as the database engine and data storage for your Access database. Desktop Engine is a data store based on Microsoft SQL Server™ 2000 technology, but it is designed and optimized for use on smaller computer systems, such as a one computer or small workgroup server. Because Desktop Engine is based on the same database engine as SQL Server, most Access projects or client/server applications run on either Desktop Engine or SQL Server Standard or Enterprise Edition unchanged. However, unlike other editions of SQL Server, Desktop Engine has a 2 gigabyte database size limit, it does not support symmetrical multiprocessing (SMP), and it cannot be a Publisher for a transactional publication (although it can be a Subscriber to transactional publications).

If you select Desktop Engine or SQL Server as the database engine for your application, there are no further steps required to replicate between a SQL Server Publisher and an Access Subscriber. The computer running Access appears in SQL Server Enterprise Manager as simply another server.

If you select Microsoft Jet as the database engine for your Access application, you must enable the Jet version 4.0 database as a Subscriber. To do so, you must configure SQL Server to use an OLE DB connection to the database for each Jet Subscriber. The easiest way to do this is through SQL Server Enterprise Manager; however, you can also add a Jet database as a linked server programmatically by executing sp_addlinkedserver.

Replication to Access Subscribers is subject to the following restrictions:

• Microsoft Jet 4.0 does not support case-sensitive sort orders. Do not use an instance of SQL Server with a case-sensitive sort order installed to create publications for Jet 4.0 Subscribers.
  
  
• Microsoft Jet 4.0 does not support push subscriptions from Publishers running on DEC Alpha servers to Jet 4.0 Subscribers running on other platforms. Instead of creating a push subscription at the DEC Alpha Publisher, create a pull subscription at the Jet 4.0 Subscriber.
  
  
• SQL Server does not support known pull subscriptions but does support anonymous pull subscriptions from Jet 4.0 Subscribers. This functionality is implemented using the Microsoft ActiveX® replication controls.
  
  
• You cannot replicate both merge and transactional publications from the same publication database to a Jet Subscriber.
  
  
• When running the Merge Agent with the –validate parameter, only rowcount validation is supported. You cannot use checksum validation when validating replicated data to a Jet Subscriber.
  
  
• Column names cannot be the same names as those columns used during Jet replication. Reserved column names include: s_Generation, s_GUID, s_Lineage and s_ColLineage.