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