How To Create A File DSN To Connect CX-Supervisor To An SQL Database

Introduction

CX-Supervisor Machine Edition v3.2 Supports Microsoft Access Database files directly - using both the MDB and the newer ACCDB formats. If you wish to use a database such as MS SQL Server, Oracle or mySQL, you'll need to use CX-Supervisor PLUS Edition and create a DSN file that contains details of how to connect to the database. 

This article shows how to use the ODBC Data Source Administrator to create a DSN File. It is possible to create one manually but this utility reduces the chances of making mistakes.

To learn more about CX-Supervisor and database connection applications in the pharmaceutical industry, download the whitepaper from the packaging solutions area at the following link.

industrial.omron.eu/food-and-pharma-regulations
 

Using The Correct ODBC Data Source Administrator

If you are running Windows 7 32 bit, simply type ODBC into the search box and press enter, this will run the correct software. If you are running Windows 7 64 and using a 32 bit database, you'll need to navigate to the 32 bit version. This is located in C:\Windows\SysWOW64\odbcad32.exe

If you run the wrong one, you won't be able to see your database.

Linking to the Correct Database

Now the utility has started, select the 'File DSN' tab then press the 'Add...' button

You will now be presented with a dialog showing all the database drivers on the system, you'll need to locate the correct driver for your database. For this example we are using a database that was created in Microsoft SQL Server Express 2008, this needs us to select the 'SQL Server' entry in the list. If you get this wrong you won't be able to locate your database at later stages of this procedure.

You will now be asked to enter a file name for the File DSN. This can be located anywhere but we usually put it in the CX-Supervisor Project Folder. 

You have now finished the first steps! But its not over yet, when you press Finish you'll start off the configuration for SQL Server Connections.

Setting up the MS SQL Server Settings

The dialog which now pops up is specific to the Database Server which you are using. We are using MS SQL Server, other setups will be similar. You will need to understand how your database is set up including the database type and access permissions.

The first dialog asks you to give your database a description, this can be anything, it does not have to be related to the actual name. You will also need to show where the server is, for this article we are using the local server.

You will now be asked to set the default database. The Server dropdown should contain the database you have already set up. If not you may be using the wrong driver (or the 64 bit Data Source Administrator). You can leave the rest of the settings as they are for the rest of the dialogs.

The final dialog shows you the settings and offers an option to test the settings.

When you press Finish, the File DSN will have been created in the folder you selected. 

Connecting to the File DSN in CX-Supervisor

Now you have created the File DSN you have to connect to the database using it in the usual way with CX-Supervisor, first right click on the database tab in Workspace and choose the 'Add Connection' Option.

In the resulting Add Connection Dialog, press the '...' button to browse for the DSN file you created.

In the Select Database dialog, you must navigate to the correct folder where you placed your DSN file earlier. At the bottom right is a drop down menu, here you must select the option for Data Source Name (*.dsn):

Note: Your project needs to be a PLUS Edition project in order to connect to DSN files. If Access is the only option shown, then you need to change the project to PLUS Edition in the Project->Runtime Settings-> Target Settings menu.

Once you have located your DSN file, press 'Open' and that will be shown in your Add Database dialog.

You may choose to rename this database Connection to a better name than 'Connection1' - you'll be using this name in your CX-Supervisor scripts to access the database so a meaningful name is a good idea.

Press OK and the database is ready to use. You can right click and 'Connect' to it so you can choose tables from your database to be used in CX-Supervisor.

You've now linked CX-Supervisor to the SQL database on your local machine and you can use it in your application.

Link:
http://www.myomron.com/index.php?action=kb&print=1528