Anticlue

Setting up an ODBC Data Source

For this reporting lesson, We will be connecting via ODBC, so we need to set up an ODBC connection.

From the administrative tools section, click on DataSources (ODBC). You should see the ODBC Data Source Administrator. In the ODBC Admin, you have a choice of making the connection a User Based, System Based, or File Based.

A User Based connection only works for this user and this machine. A System Based connection works for all users of this machine. A File Based connection will create a file on the network with the desired information, for all machines to use. For this example, we will be using a System based connection.

Please click on the System DSN tab, and then click the Add button.

The create New Datasource window will appear. For SQL Server, Scroll the Vertical Bar to the Bottom. Highlight SQL Server and Click Finish.

A create New Data Source window will appear under name place the name of the ODBC Connection, for my example it will be cnNorthwind. This is the name that will appear in the ODBC administrator. Next enter the name of the SQL Server which contains the database you are trying to access.

For my reporting tools, I make a standard practice to use a reporting account, in this case, we will call that account reporter. I use SQL Server authentication, and enter the reporter account and password. Click Next when finished.

Set the default database for this connection, to the desired database. I used the Northwind database.

Finally review the final page settings, make any desired configuration settings, otherwise click finish.

Now in the ODBC Administrator, you will see the new ODBC connection.

Congratulations, you have made your first foray into the world of ODBC connections. ODBC is a great tool for connecting databases to applications.