ODBC Integration and Synchronization

ODBC compliant data sources can be integrated and synchronized codeless with 150+ other data sources using the Layer2 Cloud Connector via the ODBC Data Provider. Please take a look here for supported systems and applications, such as Office 365, SharePoint, Dynamics, Azure or SQL.​​​​​

ODBC (Open Database Connectivity) is a standard programming language middleware API for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems. It is especially helpful to connect to older systems and applications.

To connect to an ODBC compliant data source the data source entity must be configured as follows in the Layer2 Cloud Connector Connection Manager:

Fig.: Sample connection configuration to connect to FileMaker data via ODBC.

ODBC Codeless Integration And Synchronization Specific Settings

Please note the following specific settings.

• Select the ODBC Data Provider to connect. The provider is part of the Windows Operating System and should be already installed. Do not expect to find your specific data source (such as FileMaker) listed here.
• Install the vendor-specific ODBC driver. Note that the driver architecture (32-bit / 64-bit) must fit to the Layer2 Cloud Connector architecture currently running. On 64-bit systems you can simply switch to 32-bit / x86 mode in the Layer2 Cloud Connector settings. You will find this settings under the connections tree's root node.
• Follow the vendor instructions to setup and configure your data source in the ODBC connections manager. See here for an example to setup an ODBC data source.
• Use a System DSN (not User or File DSN) to setup your connection. Refer to the system DSN in the Layer2 Cloud Connector connection string. Verify your setup directly in the ODBC Connection Manager, if supported.
• You can use a connection string like this to connect:
  DSN=myDSN; UID=myUser; PWD=myPassword
  Please find more details about ODBC options at www.connectionstrings.com.
• You can make use of ODBC to query your data as supported by the data provider and source system. For any specification of query language supported (typically some kind of SQL) see vendor documentation.
• You can map your target data fields to certain ODBC source fields in the Layer2 Cloud Connector (mapping section of your connection). Please take care about critical data types (simple type conversions are automatically supported).
• Please enter an appropriate primary key in your connection settings (column with unique values), depending on query (see data preview for this).
• You can use the connection for uni- or bi-directional synchronization. In case of inserts (full CRUD) via external systems please take care of the primary key. Database-side IDs will not work in his case.
• No installation or changes are required at the ODBC data source or data destination.
• Data synchronization can be started manually in the Connection Manager, on-demand per command line, or scheduled in background by the Layer2 Cloud Connector Windows Service. Only the required data changes are processed (no delete and re-insert / bulk import).
• No programming required for setup a connection and sync.
• No need to open your local network for access from outside.
• No 3rd party involved - everything under your full control.
• For many newer IT systems with ODBC support ADO.NET Data Provider are also available. ADO.NET is fully supported by the Layer2 Cloud Connector - make use of it whenever available.

Step-by-Step Intros For ODBC Integration and Synchronization

Just some ideas about systems to connect and sync:

• Sync your ODBC source with local SQL or SQL Azure for better reporting.
• Sync with Microsoft Exchange (on-premises or online) for notifications, tasks, calendar events, mobile access and offline availability on any device.
• Sync with 3rd party tools based on SQL databases like Oracle, mySQL or Microsoft SQL Server.
• Sync FileMaker with Microsoft SharePoint or Office 365 for better collaboration (for example with customers or partners) or for intranet publishing.
• Export Text-/CSV files from your source system and connect to SharePoint or Office 365 via ODBC.
• Connect and sync Lotus Notes databases codeless via ODBC.

Ready to go next steps?