Migrating Oledb to ODBC

Older versions of the Gateway used OleDb drivers to connect to lookup sources, but as OleDb is no longer supported, ODBC drivers should be used instead.

Installing the Microsoft Access Database Engine (ADE) installs the required ODBC drivers for Excel, Access, CSV (Delimited Text), SQL Server and Oracle.

The provider names for each type of ODBC driver can be obtained from the Windows ODBC Data Source Administrator, for example, in Windows 10:

Connection String Changes

As the LookupDataSource configurations are specific to a project it is not possible to provide automatic upgrades from configurations that specified OleDb connections to ODBC connections, so they must be updated manually.

Note: There is no change in the Query syntax when migrating from OleDb to ODBC.

The following are the connection string changes.

Excel:

Defining an Excel lookup using an OleDb driver would be similar to:

<MSExcelLookupDataSource id="ExcelLookup1" file="<FilePath>" provider="Microsoft.ACE.OLEDB.12.0"
extendedProperties="Excel 12.0; HDR=YES" query="SELECT [Source], [Destination] FROM [Sheet1$]"

This should be updated to support an ODBC driver:

<MSExcelLookupDataSource id="ExcelLookup1" file="<FilePath>"
provider="Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"
extendedProperties="Excel 12.0; HDR=YES" query="SELECT [Source], [Destination] FROM [Sheet1$]"

Access:

Defining an Access lookup using an OleDb driver would be similar to:

<MSAccessLookupDataSource id="MSAccessLookup" file="<FilePath>"
query="SELECT [Source], [Destination] FROM [ObjectID]" sourceColumn="Source" targetColumn="Destination" />

This should be updated to support an ODBC driver:

<MSAccessLookupDataSource id="MSAccessLookup" file="<FilePath>"
provider="Microsoft Access Driver (*.mdb, *.accdb)"
query="SELECT [Source], [Destination] FROM [ObjectID]" sourceColumn="Source" targetColumn="Destination" />

CSV:

Defining a CSV lookup using an OleDb driver would be similar to:

<CsvLookupDataSource id="CSVLookup" file="<FilePath>"
separator="," sourceColumn="Source" targetColumn="Destination" />

This should be updated to support an ODBC driver:

<CsvLookupDataSource id="CSVLookup" file="<FilePath>"
provider="Microsoft Access Text Driver (*.txt, *.csv)" separator="," sourceColumn="Source" targetColumn="Destination" />

Notes: "Provider" in Excel, Access and CSV is optional. If Provider is not provided, default values would be:

• Excel - Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)

• Access - Microsoft Access Driver(*.mdb, *.accdb)

• CSV - Microsoft Access Text Driver (*.txt, *.csv)

SQL Server:

Defining an SQL server lookup using an OleDb driver would be similar to:

connectionString="Provider=SQLOLEDB; Server=serverxxx; Database=Databasexxx; User ID=userxxx; Password=pwdxxx;"

This should be updated to support an ODBC driver:

connectionString="Driver={SQL Server}; Server=serverxxx; Database=Databasexxx; UID=userxxx; PWD=pwdxxx;"

'Defining an Oracle lookup using an OleDb driver would be similar to:

Oracle:

Defining an SQL server lookup using an OleDb driver would be similar to:

<OracleLookupDataSource id="OracleLookup"
connectionString="Provider=OraOLEDB.Oracle;
Data Source=serverAddressxxx;
User ID=userxxx; password=passwordxxx;"
query="select Source, Destination from DocumentLookup"
sourceColumn="Source" targetColumn="Destination" />

This should be updated to support an ODBC driver:

<OracleLookupDataSource id="OracleLookup" connectionString="Driver={Oracle in OraClient12Home1};
DBQ=serverAddressxxx;
UID=userxxx; PWD=passwordxxx;"
query="select Source, Destination from DocumentLookup"
sourceColumn="Source" targetColumn="Destination" />

Note: Alternative ODBC Drivers are available, but the connectionString details will be different. Examples of these are:

• https://go.microsoft.com/fwlink/?linkid=2168524 for SQL Server

• https://www.oracle.com/in/database/technologies/releasenote-odbc-ic.html for Oracle