Implementing Merge Replication to Access Subscribers

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

If you select MSDE 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 installation 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.

To enable a Jet 4.0 database as a Subscriber

To create a publication for a Jet Subscriber

To add a push subscription to a Jet Subscriber

Data Type Mapping to Jet-SQL 4.0 for Merge Replication

The following table maps data types for merge replication to Access Subscribers. When you replicate to ODBC Subscribers, the distribution task maps SQL Server data types to the closest data type on the target database.

Note The data type mapping from SQL Server to Jet-SQL 4.0 is the same for snapshot, transactional, and merge replication.

SQL Server data type	Microsoft Jet-SQL 4.0 data type
binary(n)	BINARY (n)
bit	BIT
char(n)	CHAR (n)
datetime	DATETIME
decimal	DECIMAL
float	FLOAT
image	IMAGE
int	INT
money	CURRENCY
nchar(n)	NCHAR (n)
numeric	NUMERIC
nvarchar(n)	NCHAR VARYING (n)
real	REAL
smalldatetime	DATETIME
smallint	SMALLINT
smallmoney	CURRENCY
text	LONGTEXT
timestamp	BINARY
tinyint	BYTE
uniqueidentifier	GUID
varbinary(n)	VARBINARY (n)
varchar(n)	VARCHAR (n)

Configuring Linked Servers	Using the Configure Publishing and Distribution Wizard
Using the Push and Pull Subscription Wizards	Using the Create Publication Wizard
sp_addlinkedserver	Programming Subscriptions to a Jet 4.0 Database

Implementing Merge Replication to Access Subscribers

Data Type Mapping to Jet-SQL 4.0 for Merge Replication

See Also