Access Subscribers

SQL Replication

Replication

Access Subscribers

Microsoft® SQL Server™ 2000 includes an ODBC driver and OLE DB provider that supports Microsoft Access 97 or Microsoft Access 2000 subscriptions to SQL Server publications. SQL Server 2000 Setup installs the driver and provider automatically.

To replicate to Access Subscribers, you must assign the MSSQLServer service the same domain user account assigned by SQL Server Agent, for the service to have the necessary permissions to connect to an .mdb file over the network. Use the Services application in Control Panel to do this.

Note  When you register a Access DSN on a remote server, supply a UNC path (not a redirected drive letter).

Important  If you do not enable heterogeneous Subscribers and you create the subscription database in the Create Publication Wizard, the schema will be published to the Subscriber, but the data will not be, and you will not receive an error. To enable heterogeneous Subscribers, on the Specify Subscriber Types page of the Create Publication Wizard, select Heterogeneous data sources, such as Oracle or Microsoft Access; devices running SQL Server CE; or servers running earlier versions of SQL Server.

Data Type Mapping to Jet-SQL 3.51 (Access 8) for Transactional Replication

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

SQL Server 2000 data type Access Jet-SQL 3.51 data type
binary(n) LONGBINARY
bit BIT
char(n) LONGTEXT
datetime CHAR (23)
decimal CHAR (30)
float DOUBLE
image LONGBINARY
int LONG
int LONG
money CHAR (25)
nchar(n) LONGTEXT
ntext LONGTEXT
numeric CHAR (30)
nvarchar(n) LONGTEXT
real SINGLE
smalldatetime DATETIME
smallint SHORT
smallmoney DOUBLE
text LONGTEXT
timestamp BINARY (8)
tinyint BYTE
uniqueidentifier CHAR (36)
varbinary(n) LONGBINARY
varchar(n) LONGTEXT
Data Type Mapping to Jet-SQL 4.0 for Transactional Replication

The following table maps data types for transactional replication to Access Subscribers. When you replicate to ODBC Subscribers, the distribution task maps SQL Server 2000 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 replication, transactional replication, and merge replication.

SQL Server 2000 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)