You are currently offline, waiting for your internet to reconnect

How To Configure ODBC Data Sources on the Fly

This article was previously published under Q110507
This article has been archived. It is offered "as is" and will no longer be updated.
You can configure ODBC (Open Database Connectivity) data source namesprogrammatically. This gives you flexibility to export data without forcingthe user to explicitly use the ODBC Administrator or other programs tospecify the names of data sources. This might, for example, enable yourprogram to use the ODBC API (application programming interface) to exportan .XLS file. To do this, use the SQLConfigDataSource() function.

The following example uses SQLConfigDataSource to create a new Excel datasource called "New Excel Data Source":
   SQLConfigDataSource(NULL,ODBC_ADD_DSN,     (LPSTR) "Excel Files (*.xls)",     (LPSTR) "DSN=New Excel Data Source\0"     "Description=New Excel Data Source\0"     "FileType=Excel\0"     "DataDirectory=C:\\EXCELDIR\0"     "MaxScanRows=20\0");
Note that the data source is actually a directory (C:\EXCELDIR). The Exceldriver has directories as its data sources, and files as the individualtables (one table per .XLS file).

For additional information on creating tables, please see the followingarticle(s) in the Microsoft Knowledge Base:
110508Creating Tables with Foundation Database Classes
The information below discusses the parameters that need to be passed tothe SQLConfigDataSource() ODBC API function. To use theSQLConfigDataSource() function, you must include the ODBCINST.H header fileand use the ODBCINST.LIB import library.

NOTE: For 32-bit applications, you must still include ODBCINST.H headerfile, however you must now link with ODBCCP32.lib

NOTE: The information contained within this article is duplicated in the'Programming with MFC Encyclopedia' shipped with Visual C++ 4.0. Thearticle can be found by searching for "SQLConfigDataSource" and selectingthe article titled 'FAQ: Programatically Configuring an ODBC Data Source'.
NOTE: This article was originally written for the 16-bit ODBC componentsonly. The 16-bit ODBC components use INI files to store information onconfigured datasources (ODBC.INI) and installed drivers (ODBCINST.INI). The32-bit ODBC components no longer use INI files but, instead, write suchinformation to the registry. System datasource information and installeddriver information is stored in HKEY_LOCAL_MACHINE\SOFTWARE\ ODBC\ inODBC.INI and ODBCINST.INI, respectively. Non-System datasources are storedin HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI. In the remainder of thisarticle, references to ODBC.INI should be interpreted as referring to theappropriate section of the registry if you are using the 32-bit ODBCcomponents.

An ODBC data source name can be created using the ODBC Administratorprogram or similar utility. However, sometimes it is desirable to create adata source name directly from your application so that access can beobtained without requiring the user to run a separate utility.

The ODBC Administrator (typically installed in the Windows Control Panel)creates a new data source by putting entries in the ODBC.INI file. Thisfile is queried by the ODBC Driver Manager to obtain the requiredinformation about the data source. It's important to know what informationneeds to be placed in the ODBC.INI because you'll need to supply it withthe call to SQLConfigDataSource().

Although this information could be written directly to the ODBC.INI file[without using SQLConfigDataSource()], any application that does this isrelying on the current technique that the Driver Manager uses to maintainits data. If a later revision to the ODBC Driver Manager implements recordkeeping about data sources in a different way, then any application thatused this technique would be broken. It is generally advisable to use anAPI function when one is provided.

Below, you will find an explanation of the parameters of theSQLConfigDataSource() function. Much of the information is taken from theODBC API Programmer's Reference supplied with Visual C++ version 1.5. Function prototype:
   BOOL SQLConfigDataSource(HWND hwndParent,UINT fRequest,                            LPCSTR lpszDriver,                            LPCSTR lpszAttributes);
hwndParent - This is the window that will be used as the owner of any dialog boxes which are created by either the Driver Manager or the specific ODBC Driver to obtain additional information from the user about the new data source. If there is not enough information provided in the lpszAttributes parameter, a dialog box will appear. This parameter may be NULL, see the reference for specifics.

fRequest - The operation to be performed. Possible values are:
                      ODBC_ADD_DSN: Add new user data                        source.                      ODBC_CONFIG_DSN: Modify an                        existing data source.                      ODBC_REMOVE_DSN: Remove an                        existing data source.
The following values are available in ODBC 2.53.0 or later 32-bit only:
                      ODBC_ADD_SYS_DSN: Add a new                        system data source.                      ODBC_CONFIG_SYS_DSN: Modify                        an existing system data                        source.                      ODBC_REMOVE_SYS_DSN: Remove                        an existing system data                        source.
lpszDriver - Driver description. As the documentation mentions, this is the name presented to the users rather than the physical driver (the DLL). You can determine the description of a driver using the ODBC Administrator program as follows:

  1. Run the ODBC Administrator program.
  2. Choose Add. This will give you a list of installed drivers.
The list contains driver descriptions. It is this description that you will use as the lpszDriver parameter. Note that the ENTIRE description is used [for example, "Excel Files (*.xls)"] including the file extension and parentheses if they exist in the description.

Optionally, you can examine the file ODBCINST.INI, which contains a list of all driver entries and descriptions in the section [ODBC Drivers].

lpszAttributes - List of attributes in the form "keyname=value". These strings are separated by null terminators with two consecutive null terminators at the end of the list. These attributes will primarily be default driver-specific entries, which go into the ODBC.INI file for the new data source. One important key, which is not mentioned in the ODBC API reference for this function, is "DSN" which specifies the name of the new data source. The rest of the entries are specific to the driver for the new data source. Often times it is not necessary to supply ALL of the entries because the driver can prompt the user (if hwndParent is not NULL) with dialog boxes for the new values. You might want to explicitly supply default values so that the user is not prompted.

One way to find the keynames and their values is to examine the registry entries for an already configured data source (perhaps one that has been configured by the ODBC Administrator program):

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:
322756 How to back up and restore the registry in Windows

  2. Find the hive that corresponds to your data source name. There you will find the keyword pairs. Warning, manually editing registry values can cause system instability and perhaps an unrecoverable failure.
You might also want to examine the documentation for the specific driveryou are going to use. Useful information may be found in the online helpfor the driver, which can be accessed by running the ODBC Administrator:click Add, select the driver name, and click OK. When the information forcreating a new data source comes up for that particular driver, selectHelp. This will open the help file for that particular driver, whichgenerally contains important information concerning the use of the driver.
ODBC Programmer's Reference and SDK Guide (available in Books Online).

Article ID: 110507 - Last Review: 12/04/2015 09:58:17 - Revision: 3.2

Microsoft Data Access Components 1.5, Microsoft Data Access Components 2.0, Microsoft Data Access Components 2.1, Microsoft Data Access Components 2.5, Microsoft Data Access Components 2.6, Microsoft Data Access Components 2.7

  • kbnosurvey kbarchive kbdatabase kbhowto KB110507