Microsoft® SQL Server™ includes an ODBC driver and OLE DB provider that supports Microsoft Access 97 or Microsoft Access 2000 subscriptions to SQL Server publications. For more information on the ODBC driver and OLE DB provider, see Driver Support for Heterogeneous Data Sources. SQL Server 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).
The following table maps data types for transactional 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.
| SQL Server 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 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) |