The most up-to-date version of this file is at the Microsoft Download Center.

The SQL Server documentation team cannot answer technical support questions, but welcomes your suggestions and comments about this readme documentation. You can quickly and directly send e-mail feedback by using the following link: Submit Feedback.

Please send your feedback in English.

Contents

1.0 Introduction

To synchronize data between a Microsoft® Access database on a desktop and Microsoft SQL Server 2005 Everywhere Edition (SQL Server Everywhere Edition) database on a device, use the Microsoft SQL Server Everywhere Edition Access Database Synchronizer (Access Database Synchronizer). The Access Database Synchronizer is free to download.

[Top]

1.1 Installation Requirements

Before you run the Access Database Synchronizer (SyncWithAccess-EN.msi) installer, you must install the following software:

  • Microsoft Access 2000 or a later version

  • Microsoft ActiveSync 4.0 or a later version

    ActiveSync is available as a free download at ActiveSync Download.

Note:
Once the computer is set up for synchronizing, you have to make sure that there is an ActiveSync connection setup between the desktop and device. Synchronization will not occur if there is no ActiveSync connection established.

[Top]

1.2 Supported Platforms

On a desktop, the following operating systems will be supported:

  • Microsoft Windows XP Professional SP2

  • Microsoft Windows XP Home Edition SP2

  • Microsoft Windows XP Media Center Edition 2002 SP2

  • Microsoft Windows XP Media Center Edition 2004 SP2

  • Microsoft Windows XP Tablet PC Edition SP2

On devices, the following operating systems will be supported:

  • Windows Mobile 5.0 Pocket PC

The following versions of Microsoft Access will be supported:

  • Microsoft Access 2000 SP3 (Microsoft Office 2000)

  • Microsoft Access 2002 SP3 (Microsoft Office 2002)

  • Microsoft Office Access 2003 SP2 (Microsoft Office 2003)

[Top]

2.0 Obtaining Access Database Synchronizer

You can download Access Database Synchronizer (SyncWithAccess-EN.msi) at the Microsoft Download Center.

[Top]

3.0 Documentation Notes

3.1 Data Conversion Rules

3.1.1 Database Constraints

Database referential integrity constraints on desktop cannot be copied to SQL Server Everywhere Edition database on devices. Compatible constraints should be manually created on a device. It leads to synchronization failure if data entered on desktop and device violates the constraints on either side. For default values and the constraints that are not supported, the actual values will be pulled and pushed during the synchronization. Default value constraint will not be pulled.

Microsoft Access Database Constraints Corresponding SQL Server Everywhere Edition constraint

Field size

(Data type, Field size) combination in

Access will be mapped to different data types in SQL Server Everywhere Edition

Format

Not supported

Input Mask

Not supported

Caption

Not supported

Default Value

Not supported

Validation Rule

Not supported

Validation Text

Not supported

Required

Allow NULLs set to No

Allow zero length

Allow NULLs

Indexed

Index

Unicode compression

Not supported

IME Mode

Not supported

IME Sentence Mode

Not supported

Smart Tags

Not supported

[Top]

3.1.2 Data Type Conversions

The data type of a column determines what type of data can be stored in that column. Microsoft Access and SQL Server Everywhere Edition have different data types. At the time of synchronization, the following conversion rules apply for the data types.

Access Data Types SQL Server Everywhere Edition Data Types Remarks

Text

Nchar

Limit 255 characters on Access, Limit of 4000 character on SQL Server Everywhere Edition.

Field size on Access control the maximum number of character.

Nchar(n). n control the maximum character on SQL Server Everywhere Edition.

At the time of synchronization, the n should be set equal to Field Size (less than 255).

NChar is Unicode; Text is not Unicode.

Memo

NText

NVarChar is Unicode; Memo is not Unicode

Number (Byte)

Tinyint

Number stores 1, 2, 4, 8, or 16 bytes based on Field size.

Numeric storage size is 19 bytes. It stores values from -10^38+1 to 10^38-1

Number (Integer)

Smallint

-

Number (Long integer)

Integer

-

Number (Single)

Real

-

Number (Double)

Float

-

Number (Decimal)

Numeric

-

Number (ReplicationID)

GUID

-

Date/Time

DateTime

Date/Time and DateTime both are 8 bytes.

From Jan 01, 1753 to Dec 31, 9999

Currency

Money

Currency and Money both are 8 bytes.

Values from -2^63/10000 to 2^63-1

AutoNumber (Random/Increment)

Integer

Auto numbers are used for uniquely identifying a row.

AutoNumber (GUID)

GUID

Table with AutoNumber (GUID) field should be pulled as a read-only table in which case push will not possible. Table without AutoNumber will be pulled with tracking and the changes will be pushed.

YesNo

Bit

-

OLE Object

Image

-

Hyperlink

NText

Hyperlink will be stored as NText in SQL Server Everywhere Edition

Lookup wizard

NText

-

[Top]

3.2 Synchronization Rules

Device Desktop

INSERT UPDATE (non- PK¹ column) UPDATE (same PK to different values) UPDATE (different PK to same values) DELETE  Result

X

 

 

 

 

1) Same Primary Key for the INSERTED records leads to Primary Key Violation.

2) Different Primary Key results in both win (both are updated).

X

 

 

 

 

Not a valid scenario.

X

 

 

 

 

Primary Key Violation.

X

 

 

 

 

Non collision scenario. Both win.

 

 

 

 

X

Not a valid scenario.

 

 

 

 

X

Both win - Device might encounter an error/warning.

 

 

 

 

X

Device wins (data on the device is final).

 

 

 

 

X

Desktop wins (data on the desktop is final).

 

 

 

 

X

PK1, PK2 are the rows1) If PK1 is deleted and PK2 is updated to PK1 on desktop and PK1 is deleted on device. Eventually PK1 is also deleted when PUSH occurs.

2) Otherwise DELETE fails and Device might encounter an error/warning.

 

X

 

 

 

Non-collision scenario. Both win.

 

 

X

 

 

Device update fails.

 

 

 

X

 

PK violation.

 

X

 

 

 

Device update fails.

 

 

X

 

 

Device update fails.

 

 

 

X

 

Device update fails.

INSERT UPDATE (non-PK column) UPDATE (same PK to different values) UPDATE (different PK to same values) DELETE  Result

X

 

 

 

 

1) Same Primary Key for the INSERTED records leads to Primary Key Violation

2) Different Primary Key results in both win

 

 

 

 

X

Not a valid scenario

 

 

 

X

 

Primary Key Violation

 

 

X

 

 

Non collision scenario. Both win

X

 

 

 

 

Not a valid scenario

 

 

 

 

X

Both win - Device may encounter an error/warning

 

X

 

 

 

Device Wins

 

 

X

 

 

Desktop Wins

 

 

 

X

 

PK1, PK2 are the rows1) If PK1 is deleted and PK2 is updated to PK1 on desktop and PK1 is deleted on device. Eventually PK1 also is deleted when PUSH occurs.

2) Otherwise DELETE fails and Device may encounter an error/warning

X

 

 

 

 

Non collision scenario. Both win

 

 

X

 

 

Device Update fails

 

 

 

X

 

PK Violation

 

 

 

 

X

Device Update fails

 

 

 

 

X

Device Update fails

 

 

 

 

X

Device Update fails

¹PK = primary key

[Top]

3.3 Access Database Synchronization Wizard

Access Database Synchronization Wizard helps set up synchronization between the Access database on the desktop and the SQL Server Everywhere Edition database on the device.

Prerequisites

First, you should install .NET Compact Framework 2.0 and device cab files on the device for the Access Database Synchronization Wizard.

Note:
The device cab files will be updated with the RTW release of SQL Server Everywhere Edition.

How to pull data by using Access Database Synchronization Wizard

  1. Click Start, Programs, and then click Desktop Data Sync Wizard.

  2. Click Configuration.

  3. Under Access Database, specify the name and the path of the Access database on the desktop.

  4. Under Device Database, specify the name and the path of the Device database (SQL Server Everywhere Edition Database) on the device.

  5. Under Specify Desktop URL, specify the name of the desktop computer and the port number that is being used by the "SSEvAccessSync" service. For example, if the name of the desktop computer is “test-tp” and the SSEvAccessSync service is using the port 1024, the URL to be specified will be http://test-tp:1024/.

  6. Click Database.

  7. In the tree view, you should see the names of the Access and Device databases. Select the Access database node. This will enable the Connect menu. Click Connect.

  8. On successful connection, the Access database node in the tree view is populated. This list is collapsed. Click + next to the Access database node to see the list of tables present in the Access database.

  9. Click File and then click Pull Data.

Important:
For pushing or pulling data, both the device database and Access database should be connected.

[Top]

How to push data by using Access Database Synchronization Wizard

  1. Click Start, Programs, and then click Desktop Data Sync Wizard.

  2. Click Configuration.

  3. Under Access Database, specify the name and the path of the Access database on the desktop.

  4. Under Device Database, specify the name and the path of the Device database (SQL Server Everywhere Edition Database) on the device.

  5. Under Specify Desktop URL, specify the name of the desktop computer and the port number that is being used by the SSEvAccessSync service. For example, if the name of the desktop computer is “test-tp” and the SSEvAccessSync service is using the port 1024, the URL to be specified will be http://test-tp:1024/.

  6. Click Database.

  7. In the tree view, you should see the names of the Access and Device databases. Select the Device database node. This will enable the Connect menu. Click Connect.

  8. On successful connection, the Device database node in the tree view is populated. This list is collapsed. Expand (click the +) the Device database node to see the list of tables present in the Device database.

  9. Click File and then click Push Data.

Important:
For pushing or pulling data, both the device database and Access database should be connected.

[Top]

3.4 Restrictions and Limitations of Using Access Database Synchronizer

  • The Access Database Synchronizer is compatible only with SQL Server Mobile 3.0 and later versions.

  • Schema changes on either desktop or devices are not replicated. No incremental changes from the desktop can be pulled to the original table on the device.

  • You cannot create a Pocket PC Access database file on device.

  • You cannot synchronize data between an Access database on a desktop and a SQL Server Everywhere Edition database on the desktop.

  • No conflict resolution is provided. When downloading, the device changes can never be retained. If users require conflict resolution, they must use SQL Server 2005 for merge replication.

  • Connectivity solutions like General Packet Radio Services (GPRS) and Wi-Fi are not supported.

  • The datetime data type in Jet is mapped to the datetime data type in SQL Server Everywhere Edition. However the size of datetime in Jet is 8 bytes, whereas the size of datetime in SQL Server Everywhere Edition is 16 bytes. Therefore, the microsecond data is not copied from the SQL Server Everywhere Edition to Jet when data is pushed.

  • No schema synchronization will be done from desktop to device and no schema synchronization will be done from device to desktop. For example, you cannot create a table in the SQL Server Everywhere Edition database on the device and push it to the Access database.

  • Referential integrity constraints and indexes are not replicated. Additional schema definition must be defined at the client.

  • Schema changes are not enabled. If the schema is changed, the client must drop the table at the client and pull all the data from server again. Depending on the schema change, a push can fail.

  • Synchronization can occur only if there is an ActiveSync connection established between the device and the desktop.

[Top]

3.5 Writing Custom Applications

Desktop Applications

To create desktop applications, you can use “ssevas31.dll”. This is the .dll that exports the ListenSyncRequest function. This function is used by the SSEvAccessSync service. If an application does not want to use the service, the application can directly load this .dll and call the ListenSyncRequest function.

By default, the ListenSyncRequest function listens on port 1024 for any synchronization requests. To change the default port, you can modify the following registry key.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQLServerEverywhere\v3.1\Access\ “Private\Core\repl\httpListener\PORT_NUMBER”

After the application is installed, the SSEvAccessSync service uses the .dll “ssevas31.dll” and calls the ListenSynRequest function exported by the service. The following registry entries are added to start and stop the service whenever the ActiveSync connection is established or disconnected as follows:

String key “SSEvAccessSync” with value “C:\Program Files\Microsoft SQL Server Everywhere\Access\start-service.bat” under the location “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows CE Services\AutoStartOnConnect

String key “SSEvAccessSync” with value “C:\Program Files\Microsoft SQL Server Everywhere\Access\stop-service.bat” under the location “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows CE Services\AutoStartOnDisconnect”

You could also remove these registry entries and start or stop the service manually.

[Top]

Device Applications

The device applications should use the existing Remote Data Access (RDA) programming model. You can refer to the following RDA topics in SQL Server Everywhere Edition Books Online.

  • Introducing Remote Data Access

  • Typical Uses of Remote Data Access

  • How Remote Data Access Works

  • RDA Conflict Detection and Reporting

  • Propagating Data/RDA Programming

SQL Server Everywhere Edition Books Online is available for download at the Microsoft Download Center. As you read through the content, assume "Access database" where there is a reference to "SQL Server 2005 database."

You can also refer to the Access Database Synchronization Wizard as an example to understand how to implement synchronization between an Access database on the desktop and SQL Server Everywhere Edition on the device.

[Top]

4.0 Additional Information

This section describes how to obtain Help and provide feedback about SQL Server Everywhere Edition.

4.1Obtaining Access Database Synchronizer Assistance

For more information, see the SQL Server Everywhere Edition Forum and SQL Server Everywhere Edition Team Blog Web sites.

[Top]

4.2 Providing Feedback about Access Database Synchronizer

To provide suggestions and bug reports about Access Database Synchronizer:

[Top]

4.3 SQL Server Everywhere Edition Books Online

SQL Server Everywhere Edition Books Online is available for download at the Microsoft Download Center. Books Online provides detailed information about the development, administration, and deployment of SQL Server Everywhere Edition on desktop computers, mobile devices and tablet PCs.

[Top]