When a field in Access is configured as a multi value column (The Display Control is a Combo Box and the Allow Multiple Values option is set to Yes) the data stored in the field may be received by Sync & Save in a scrambled format that may even cause values from one column to display in a second column.
This is a bug in the OLEDB database driver used to communicate with Access. The data is received by Sync & Save in the scrambled format.
There are two possible solutions to this problem:
- Do not use multi value fields for data you wish to use in Sync & Save.
- Connect to Access using ODBC.
Multiple values in a field will be returned as values separated by a semi-colon.
To implement option 2, change the Source Type in the Lookup Table job to ODBC. Use a connection string with the following format:
Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\mydatabase.accdb;
Change the path to the database to match your configuration.