|
ODBC Configuration and Connections ODBC and related configurations can be managed from the ODBC control panel utility. Select the ODBC icon in the Control Panel window to view the current state of ODBC drivers and sources installed. If the ODBC icon does not appear in the Control Panel window, ODBC core components are not loaded in your system.
The following dialog box is presented:
As you can see, there are three types of DSNs (Data Source Name). Let us understand how to use this dialog.
User DSN
The User DSN is a data source that is user specific. A User DSN is stored locally but is available only to the user who creates it. User DSNs are not used by Microsoft Query 97. If you use Microsoft Jet, ODBC, or Structured Query Language (SQL) commands and bypass Microsoft Query, User DSNs are required. User DSNs are stored in the Windows registry under the following key:
HKEY_CURRENT_USER\Software\Odbc\Odbc.ini\Odbc Data sources
System DSN
Unlike a User DSN, a System DSN is not user specific. A System DSN is stored locally and is not dedicated to a particular user. Any user who logs on to a computer that has permission to access the data source can use a System DSN. Some programs, such as Microsoft SQL Server or Microsoft Internet Information Server (IIS), require a System DSN. This DSN must be created on the server where the program is located. System DSNs are stored in the Windows registry under the following key:
HKEY_LOCAL_MACHINE\Software\Odbc\Odbc.ini\Odbc Data sources
File DSN
The File DSN is created locally and may be shared with other users. The File DSN is file-based, which means that the .DSN file contains all the information required to connect to the data source. Note that you must install the ODBC driver locally to use a File DSN. Microsoft Query uses File DSNs but Microsoft Jet and ODBC do not use File DSNs.
The File DSNs are stored by default in the Program Files\Common Files\Odbc\Data Sources folder. File DSNs are not stored in the Windows registry. The .DSN file is a text file that you can view in any text editor, such as Microsoft Notepad.
To find out which ODBC drivers are currently loaded:
Choose the ODBC Drivers tab.
If there are no drivers loaded, this list will be blank. To load drivers for a particular data engine, you will need to refer to the documentation of that software. You can not create a data source unless the driver is loaded. Each driver is typically a DLL, written by the vendor of the database.
Tracing
This tab allows you to log the ODBC traffic of connections and related components. This feature is used mainly to troubleshoot and debug ODBC related problems.
ODBC Connections
Now that we know how to configure ODBC, let us see how to create a connection to ODBC and use it in a program.
Here we will use an example to see how you can get data stored in an RDBMS into Excel using ODBC. This does not require any programming. Later on, we will also see how this can be done programmatically in Visual Basic.
Prerequisites for the example:
There are two computers with one having Windows NT loaded.
SQL Server 6.5 or higher is loaded.
Client utilities of SQL Server are loaded on your client machine. This loads the SQL Server ODBC driver
You have a valid Windows NT user-id and password.
You have a valid login for SQL Server, and its password. For the purpose of this example, we will assume that the user-id is sa and no password is required.
You have Excel 97 loaded on the client machine.
Adding a data source
From the client machine choose Start - Settings - Control Panel - ODBC.
Choose the System DSN tab. We will use the System DSN so that all users using the client machine can use this data source.
Choose Add.
From the list of drivers, choose SQL Server.
Now, depending upon the version of ODBC loaded in your machine, a dialog or wizard will appear.
Specify a name for the Datasource as mytest. If you are using a wizard, the following dialog will appear:
Choose the database server by giving the server name of the Windows NT Server. The name of SQL Server is same as the machine name of Windows NT. We will assume it is myserver. You can change it to the name of your server.
If you are not using a wizard the same options are available in a slightly different dialog box. For specifying the database name choose Next button on wizard. If you are using a dialog choose the Options button to expand the dialog.
Specify the name of the database as PUBS. This database is automatically loaded when SQL Server is set up. This database contains sample data which is preloaded.
If you are using a wizard you can click on the Next button and choose the With SQL Server authentication using login id and password radio button. Now specify 'sa' as the username and no password. If you have a different name and password configured, please enter it accordingly.
Click on Next till you see the Finish button. Click the Finish button.
The Test Datasource button will appear on the next dialog. Choose it to confirm that the database connection is successful.
Now we have successfully configured the ODBC datasource for the database PUBS of the SQL Server. In order to fetch data from it we can use various methods.
Here are the most commonly used methods and their respective usage:
Excel: For quick retrieval of data for reference, Excel is the simplest method. This method does not require any programming knowledge. In addition, as Excel has extensive features for data handling, pivot tables, sorting, filtering, statistical analysis, graph drawing, mathematical and financial calculations, utilising the retrieved data is simplified.
Word: For quick mail-merge, Word provides an excellent and easy-to-use interface for the purpose of using names and addresses stored in a database. You can also fetch data into Word tables, and format and modify them.
MS Query: MS Query is available with MS Office and SQL Server 6.5 for accessing data from any ODBC source and editing, filtering, sorting or viewing it. For quickly editing and adding data, this is a good generic front-end.
Using programming: You can use various data access methodologies to use data from an ODBC data source in your application. Here are some of the common ways of accessing data programmatically:
Method
Where is it used
ODBC API
Used from C, C++ or other languages for low level API Access. This requires more complex function calls than other methods mentioned below.
DAO
The JET database engine which is a part of VB and Access can use ODBC to manage server data. This provides for base functionality like Cursors, Dynasets, Snapshots, execution of Stored Procedures, etc.
RDO
This is a superset of DAO and is optimised for remote data. This supports stored procedures with output parameters, multiple recordsets and other advanced functions.
ADO
Active Data Objects are the latest methods, which work with another technology called OLEDB.
FoxPro
This uses its own DBF engine to connect to remote data and manipulate it. This functionality is termed 'remote views'.
|
