What is ODBC?

FlexSim can use ODBC, and ODBC can connect to many different kinds of data sources, including files (like Excel) and databases. It allows you to use SQL queries to get data from any supported data source.

ODBC uses a driver determine how to get data from a given data source. A driver is translator between ODBC and whatever data source you are querying. For example, there is a driver for Excel files. If you have that driver on your system, you could use ODBC to query data from Excel files. As another example, there is a driver for SAP HANA, the database for SAP. If you have that driver, then ODBC will know how to talk to SAP.

This means that if the correct driver is installed, and is working correctly, that you can use FlexSim to query any ODBC data source.

Using the Database Connector

FlexSim has a tool called the Database Connector. You can use it to configure a connection to a database. This article uses that tool. For more information, see
https://docs.flexsim.com/en/20.2/Reference/Tools/DatabaseConnectors/

As an example, let's say that you want to connect to Excel using ODBC. Assuming you have the Excel ODBC driver installed on your system, you can configure a Database Connector to look like this:

Note that you must specify the full connection string. In the connection string, you can see that the driver and the file are specified. Then you can query the data in a given sheet with a query like this:

SELECT * FROM [Sheet1$]

You can find more information on querying Excel files at websites like this:
https://querysurge.zendesk.com/hc/en-us/articles/205766136-Writing-SQL-Queries-against-Excel-files-Excel-SQL-

If you use Office 365, you may need to install the Microsoft Access Database Engine 2016 Redistributable. This includes newer drivers for Excel and Access Be sure to install it with the /quiet flag on the command line. Instructions can be found in this troubleshooting guide:

https://docs.microsoft.com/en-us/office/troubleshoot/access/cannot-use-odbc-or-oledb

Note that FlexSim has an Excel tool, which is usually easier to use. This tool requires Excel to be installed, but does not require the ODBC driver for Excel to be installed on your computer. For more information, see https://docs.flexsim.com/en/20.2/Reference/Tools/ExcelInterface/. Excel makes a good example because most people have it, and it's easy to get the driver for it.

Connection Strings

Different kinds of connections require different connection strings. The following list has an example connection string for a few data sources:

Excel

Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=Path\To\Excel\File.xlsx

Access

Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=Path\To\Access.accdb

SQLite

Driver={SQLite3 ODBC Driver};Database=Path\to\sqlite.db

SAP HANA

DRIVER={HDBODBC};UID=myUser;PWD=myPassword;SERVERNODE=myServer:30015

Checking for Drivers

Note that each connection string specifies a driver, and then additional information. The additional information depends on the driver you are using. In order to determine which drivers are on your system, you need to open the ODBC Data Sources Administrator window. To do that, hit the windows key, and then type ODBC. Then choose the option called ODBC Data Sources (64 bit). If you are running 32-bit FlexSim, open the 32 bit version.

Go to the Drivers tab. Here is what my Drivers tab looks like:

You can see I have drivers for Access, Excel, SQL Server, and SQLite3. I don't have drivers for SAP HANA. If I did, you'd see a driver named HDBODBC in the list. To access that kind of database, I'd need to install that driver.

You can also see that the name of the driver used the the connection string must match exactly to what is shown here.

Other Info

You may see an exception appear when you test the connection to your database. If the view shows that the connection succeeded, then it has succeeded. The exception happens because FlexSim tries to get a list of tables from the database that it's querying. FlexSim may not guess correctly for your particular data source. That exception can be safely ignored.

If you used the old db() commands in the past, consider upgrading to using the Database Connector. It will be orders of magnitude faster to read in an entire table.