Open Database Connectivity, or ODBC for short, is an Application Programming Interface (API) for connecting to databases of various types. (An API is essentially just a set of functions, also called services, for performing various tasks. These functions are usually contained in one or more dynamic link libraries (DLLs).) The term database is used here in a very general sense to refer not only to traditional relational databases, such as Access or FoxPro databases, but also to less traditional “databases” such as delimited text files or Excel worksheets.

Typically, the functions in the ODBC API are implemented in database-specific ODBC drivers. In this way, an application is shielded from having to know the specifics of the various types of databases.

Figure C-1 shows the components involved in the use of ODBC.

Figure C-1. An overview of ODBC

Since most data access is done using the SQL language, the primary ODBC-related task for an application is to submit SQL statements to the Driver Manager, which sends the commands to the appropriate driver and also processes any data that is returned as a result of the SQL statements.

The purpose of the ODBC Driver Manager is to manage communication between the application and the driver. The application communicates directly with the Driver Manager, which in turn either processes the command or sends it on (with or without some modification) to the driver. (It is possible for an application to communicate directly with a driver, but this is not usual.)

Generally, the Driver Manager just passes API function calls from the application to the correct driver. However, it does implement some API functions and also performs some basic error checking. In particular, it is responsible for implementing the following driver/data source information functions:

The Driver Manager is also responsible for managing the connection to and disconnection from an ODBC driver. In particular, when an application wants to use a particular driver, the application calls one of the following connection functions:

Each of these functions must include information about the driver in its parameters (in different forms, however). Using this driver information, the Driver Manager loads the driver (if it is not already loaded) and calls the appropriate connection function (SQLConnect, SQLDriverConnect, or SQLDriverBrowse) in the driver.

When the application is done using the driver, it calls SQLDisconnect. The Driver Manager passes this call to the driver, which disconnects from the data source.

An ODBC driver is a code component that implements the functions in the ODBC API. Each driver is specific to a particular database type. Drivers expose the capabilities of the underlying database management system (DBMS) but do not, in general, enhance its capabilities. The main exception is that drivers for DBMSs that do not have standalone database engines, as is the case with dBASE, Xbase, and ASCII text, for example, must implement a database engine that supports a minimal amount of SQL.

In particular, an ODBC driver must implement the following tasks (among others):

A data source is, in general, a source of data. However, this term is one of the most abused and inconsistently misused terms in database-related programming (at least in Microsoft’s arsenal). For instance, when the data is contained in a text file, then the term “data source” refers simply to the physical data in the file. Similarly, when the data is contained in an Access database file (extension .mbd) that is being accessed by a file-based driver, the term data source refers to the MDB file. On the other hand, when the data are contained in an Access database file that is being accessed by a DBMS-based driver, then the data source is considered to be the combination of the Access DBMS and the MDB file. On the other hand, in the context of the new VB6 DataBinding object model, the term data source refers to a source for the data binding, which is often a VB6 class module that has its DataSourceBehavior property set to vbDataSource. In this case, the data source itself contains no data whatsoever!

Thus, just what constitutes a data source depends upon the circumstances. In fact, since a data source is always associated with a particular driver under ODBC, we will usually think of the pair together. This view is supported by the fact that when configuring a data source using the ODBC Administrator, we are first required to select a driver.

The term data source is also sometimes used (unfortunately) to stand for the description of a data source—that is, the name and path of the database, password, user name, connection attributes, and so on. What a mess.

Creating DSNs: The ODBC Administrator

DSNs are generally created by the user with a program called the ODBC Administrator . This program is accessed by clicking on the ODBC icon in the Windows Control Panel. The opening dialog box is shown in Figure C-2.

Figure C-2. The ODBC Administrator

Once the type of DSN (User, System, or File) is chosen and the user clicks the Add button, the dialog box in Figure C-3 is displayed, prompting the user for the name of the driver.

Figure C-3. Choose a driver

The ODBC Administrator then calls the driver so it can display any of its dialog boxes that request specific information required by the driver to connect to the data source. (Thus, these dialog boxes vary from driver to driver.) After the user enters the information, the DSN data is stored in the appropriate place (the registry or a DSN file).

Example DSNs

It is helpful to take a look at a few examples of DSNs created using the ODBC Administrator.

Excel system data source

Here is an example of the registry entries for a system DSN consisting of an Excel workbook. The DSN name is ConnectExcel:

[HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INIConnectExcel]
"Driver"="C:\WINNT\System32\odbcjt32.dll"
"DBQ"="d:\bkado\connect.xls"
"DefaultDir"="d:\bkado"
"Description"="An example Excel data source"
"DriverId"=dword:00000316
"FIL"="excel 5.0;"
"ReadOnly"=hex:00
"SafeTransactions"=dword:00000000
"UID"=""

[HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INIConnectExcelEngines]

[HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INIConnectExcelEnginesExcel]
"ImplicitCommitSync"="Yes"
"MaxScanRows"=dword:00000008
"Threads"=dword:00000003
"UserCommitSync"="Yes"
"FirstRowHasNames"=hex:01

As you can see, the Driver value entry holds the name of the ODBC driver for Excel. The DBQ value entry gives the name of the Excel workbook, which is the database in this case. Each worksheet in the workbook is a database table. (For some reason, the value of FIL is "excel 5.0", even though the version of Excel that I used here is Excel 97.) The EnginesExcel subkey reports, among other things, whether the Excel tables (worksheets) use the first row for field names.

The ODBC Administrator dialog boxes that created this data source are shown in Figures C-4 and C-5.

Excel file data source

The contents of an Excel file DSN are shown here:

[ODBC]
DRIVER=Microsoft Excel Driver (*.xls)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
ReadOnly=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=512
ImplicitCommitSync=Yes
FIL=excel 5.0
DriverId=790
DefaultDir=D:kado
DBQ=D:BkAccessIIConnect.xls

Note that this is not as extensive as the system DSN. For instance, it does not include the FirstRowHasNames value.

Figure C-4. Creating an Excel data source, Part 1

Figure C-5. Creating an Excel data source, Part 2

Text-system data source

Here is an example for a text data source. In this case, a “table” is a text file with extension .txt, .csv, .tab, or .asc.

[HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INIConnectText]
"Driver"="C:\WINNT\System32\odbcjt32.dll"
"DefaultDir"="D:\bkado"
"Description"="A text data source"
"DriverId"=dword:0000001b
"FIL"="text;"
"SafeTransactions"=dword:00000000
"UID"=""

[HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INIConnectTextEngines]

[HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INIConnectTextEnginesText]
"Extensions"="txt,csv,tab,asc"
"ImplicitCommitSync"="Yes"
"Threads"=dword:00000003
"UserCommitSync"="Yes"

Note that nowhere in the registry is there a reference to the actual table (text file) or tables for this data source. This information is placed in a special text file called schema.ini that is created by the ODBC Administrator. The file is placed in the directory DefaultDir. Here are the contents of the schema.ini file, which in this case actually describes two separate text connections:

[donna.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=25
CharacterSet=OEM
Col1=FIRSTNAME Char Width 255
Col2=LASTNAME Char Width 255

[textfile.csv]
ColNameHeader=False
Format=CSVDelimited
MaxScanRows=25
CharacterSet=OEM
Col1=F1 Char Width 255
Col2=F2 Char Width 255

Note that if new text “tables” are added to the connection, additional sections are created in the schema.ini file. The ODBC dialog boxes that created the first connection are shown in Figures C-6 and C-7.

Figure C-6. Text data-source setup

Figure C-7. Setup for the donna.txt source file

UID=SRoman;ServerPWD=SubRosa;DBPWD=Secret;

DSN=ConnectToWhatever;

You may be able to get some limited help for an ODBC driver by starting the DSN creation process through the ODBC Administrator and then clicking the Help button once a driver-specific dialog box appears. This brings up the ODBC Microsoft Desktop Database Drivers Help file. However, this information is at best sketchy and often misleading. For instance, under the topic Section C.4.6, the help file says that a connection string includes the following keywords:

However, as you will see in the upcoming examples, the DBQ value is the name of the directory for the Microsoft Text Driver, but not the name of the actual workbook for the Microsoft Excel Driver! The help file also does not give any indication as to when or whether these keywords are always required. Nevertheless, the information contained in the help file can be very useful.

It is clear that in order to use ODBC effectively, the programmer may need to know what drivers and data sources exist on a particular computer. This information is accessible through a few ODBC API calls.

The following code includes a procedure called ListODBCSources, which prints (to the Immediate window) a list of all data sources on a system, and ListODBCDrivers, which prints a list of ODBC drivers on the system. This code can be placed in an Access code module:

Const SQL_NULL_HANDLE = 0
Const SQL_HANDLE_ENV = 1
Const SQL_FETCH_NEXT = 1
Const SQL_FETCH_FIRST = 2
Const SQL_SUCCESS = 0
Const SQL_ATTR_ODBC_VERSION = 200
Const SQL_OV_ODBC2 = 2
Const SQL_IS_INTEGER = -6

Dim nRetCode  As Long

Declare Function SQLDrivers Lib "odbc32.dll" (ByVal _
    EnvironmentHandle As Long, ByVal Direction As Integer, _
    ByVal DriverDescription As String, ByVal BufferLength1 As Integer, _
    DescriptionLengthPtr As Integer, ByVal DriverAttributes As String, _
    ByVal BufferLength2 As Integer, AttributesLengthPtr As Integer) _ 
    As  Integer

' Note that pointers to numbers are passed as numbers by reference!
Declare Function SQLDataSources Lib "odbc32.dll" (ByVal _
    EnvironmentHandle As Long, ByVal Direction As Integer, _
    ByVal ServerName As String, ByVal BufferLength1 As Integer, _
    NameLength1Ptr As Integer, ByVal Description As String, _
    ByVal BufferLength2 As Integer, NameLength2Ptr As Integer) As Integer

Declare Function SQLFreeHandle Lib "odbc32.dll" (ByVal _
    HandleType As Integer, ByVal Handle As Long) As Integer
    
Declare Function SQLAllocHandle Lib "odbc32.dll" (ByVal _
    HandleType As Integer, ByVal InputHandle As Long, _
    OutputHandlePtr As Long) As Integer

Declare Function SQLSetEnvAttr Lib "odbc32.dll" (ByVal _
    EnvironmentHandle As Long, ByVal EnvAttribute As Long, _
    ByVal ValuePtr As Long, ByVal StringLength As Long) As Integer

Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal _
    ConnectionHandle As Long) As Integer
    
Public Function Trim0(sName As String) As String

' Keep left portion of string sName up to first 0.

Dim x As Integer
x = InStr(sName, Chr$(0))
If x > 0 Then Trim0 = Left$(sName, x - 1) Else Trim0 = sName

End Function


Private Sub ListODBCSources(  )

' Prints a list of ODBC data soruces/drivers on system

Dim lHEnv              As Long
Dim sServerName        As String * 32
Dim sDescription       As String * 128
Dim nServerNameLength  As Integer
Dim nDescriptionLength As Integer

' Allocate an environment handle.
nRetCode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, lHEnv)

' Set ODBC behavior
nRetCode = SQLSetEnvAttr(lHEnv, SQL_ATTR_ODBC_VERSION, _
    SQL_OV_ODBC2, SQL_IS_INTEGER)

' Put first data source name in sServerName
nRetCode = SQLDataSources(lHEnv, SQL_FETCH_FIRST, sServerName, _
    Len(sServerName), nServerNameLength, sDescription, _
    Len(sDescription), nDescriptionLength)

Debug.Print "DATA SOURCE / DRIVER"
Do While nRetCode = SQL_SUCCESS

    Debug.Print Left$(sServerName, _
        nServerNameLength) & " / " & Trim0(sDescription)
    
    ' Next data source
    nRetCode = SQLDataSources(lHEnv, SQL_FETCH_NEXT, _
        sServerName, Len(sServerName), nServerNameLength, _
        sDescription, Len(sDescription), nDescriptionLength)

Loop

nRetCode = SQLFreeHandle(SQL_HANDLE_ENV, lHEnv)

End Sub

' -----------------------------------------

Private Sub ListODBCDrivers(  )

' Prints a list of ODBC drivers on system

Dim lHEnv              As Long
Dim sDriverDesc        As String * 1024
Dim sDriverAttr        As String * 1024
Dim sDriverAttributes As String
Dim nDriverDescLength  As Integer
Dim nAttrLength As Integer
Dim x As Integer
Dim sAll As String

' Allocate an environment handle.
nRetCode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, lHEnv)

' Set ODBC behavior
nRetCode = SQLSetEnvAttr(lHEnv, SQL_ATTR_ODBC_VERSION, _
    SQL_OV_ODBC2, SQL_IS_INTEGER)

' Get first driver
nRetCode = SQLDrivers(lHEnv, SQL_FETCH_FIRST, sDriverDesc, _
    Len(sDriverDesc), nDriverDescLength, sDriverAttr, _
    Len(sDriverAttr), nAttrLength)

sAll = ""
Do While nRetCode = SQL_SUCCESS

   ' Replace NULL separators with colons
   sDriverAttributes = Left$(sDriverAttr, nAttrLength - 1)
   Do
      x = InStr(sDriverAttributes, Chr$(0))
      If x = 0 Then Exit Do
      sDriverAttributes = Left$(sDriverAttributes, x - 1) & _
        " : " & Mid$(sDriverAttributes, x + 1)
   Loop
      
    sAll = sAll & Left$(sDriverDesc, nDriverDescLength) & _
        " / " & sDriverAttributes & vbCrLf
    
    ' Next data source
   nRetCode = SQLDrivers(lHEnv, SQL_FETCH_NEXT, sDriverDesc, _
      Len(sDriverDesc), nDriverDescLength, sDriverAttr, _
      Len(sDriverAttr), nAttrLength)

Loop

Debug.Print "ODBC Drivers"
Debug.Print sAll

nRetCode = SQLFreeHandle(SQL_HANDLE_ENV, lHEnv)

End Sub

The output produced by running ListODBCSources on my system is:

DATA SOURCE / DRIVER
MS Access 7.0 Database / Microsoft Access Driver (*.mdb)
Visual FoxPro Tables / Microsoft Visual FoxPro Driver
Visual FoxPro Database / Microsoft Visual FoxPro Driver
MS Access 97 Database / Microsoft Access Driver (*.mdb)
OLE_DB_NWind_Jet / Microsoft Access Driver (*.mdb)
OLE_DB_NWind_SQL / SQL Server
ConnectExcel / Microsoft Excel Driver (*.xls)
ConnectAccess / Microsoft Access Driver (*.mdb)
ConnectText / Microsoft Text Driver (*.txt; *.csv)

The output of ListODBCDrivers is:

ODBC Drivers

SQL Server / UsageCount=10 : SQLLevel=1 : FileUsage=0 : 
DriverODBCVer=02.50 : ConnectFunctions=YYY : APILevel=2 : 
Setup=sqlsrv32.dll : .01= : s=YYN : DSNConverted=F : CPTimeout=60 : 
FileExtns=Null

Microsoft ODBC Driver for Oracle / UsageCount=3 : SQLLevel=1 : 
FileUsage=0 : DriverODBCVer=02.50 : ConnectFunctions=YYY : APILevel=1

Microsoft Access Driver (*.mdb) / UsageCount=10 : APILevel=1 : 
ConnectFunctions=YYN : DriverODBCVer=02.50 : FileUsage=2 : 
FileExtns=*.mdb : SQLLevel=0 : s=YYN

Microsoft dBase Driver (*.dbf) / UsageCount=6 : APILevel=1 : 
ConnectFunctions=YYN : DriverODBCVer=02.50 : FileUsage=1 : 
FileExtns=*.dbf,*.ndx,*.mdx : SQLLevel=0 : [g= :  = : ;g= : g=
xxx
Microsoft FoxPro Driver (*.dbf) / UsageCount=6 : APILevel=1 : 
ConnectFunctions=YYN : DriverODBCVer=02.50 : FileUsage=1 : 
FileExtns=*.dbf,*.cdx,*.idx,*.ftp : SQLLevel=0

Microsoft Excel Driver (*.xls) / UsageCount=4 : APILevel=1 : 
ConnectFunctions=YYN : DriverODBCVer=02.50 : FileUsage=1 : 
FileExtns=*.xls : SQLLevel=0

Microsoft Paradox Driver (*.db ) / UsageCount=3 : APILevel=1 : 
ConnectFunctions=YYN : DriverODBCVer=02.50 : FileUsage=1 : 
FileExtns=*.db : SQLLevel=0

Microsoft Text Driver (*.txt; *.csv) / UsageCount=4 : APILevel=1 : 
ConnectFunctions=YYN : DriverODBCVer=02.50 : FileUsage=1 : 
FileExtns=*.,*.asc,*.csv,*.tab,*.txt,*.csv : SQLLevel=0

Microsoft ODBC for Oracle / UsageCount=2 : SQLLevel=1 : FileUsage=0 : 
DriverODBCVer=02.50 : ConnectFunctions=YYY : APILevel=1 : CPTimeout=120

Microsoft Visual FoxPro Driver / UsageCount=2 : APILevel=0 : 
ConnectFunctions=YYN : DriverODBCVer=02.50 : FileUsage=1 : 
FileExtns=*.dbc,*.dbf : SQLLevel=0

Let us briefly describe the ODBC functions used in these procedures. You can skip this material if it does not interest you.

Declare Function SQLAllocHandle Lib "odbc32.dll" (
     ByVal HandleType As Integer, _
     ByVal InputHandle As Long, _
   OutputHandlePtr As Long) As Integer

nRetCode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, lHEnv)

' Set ODBC behavior
nRetCode = SQLSetEnvAttr(lHEnv, SQL_ATTR_ODBC_VERSION, _
    SQL_OV_ODBC2, SQL_IS_INTEGER)

Declare Function SQLDriverConnect Lib "odbc32.dll" ( _
    ByVal ConnectionHandle As Long, ByVal WindowHandle As Long, _
    ByVal InConnectionString As String, ByVal StringLength1 As Integer, _
    ByVal OutConnectionString As String, ByVal BufferLength As Integer, _
    StringLength2Ptr As Integer, ByVal DriverCompletion As Integer) As Integer

Private Sub ListODBCDrivers(  )

Dim lHEnv              As Long
Dim sDriverDesc        As String * 1024
Dim sDriverAttr        As String * 1024
Dim sDriverAttributes As String
Dim nDriverDescLength  As Integer
Dim nAttrLength As Integer
Dim x As Integer
Dim sAll As String

txtDrivers = ""

' Allocate an environment handle.
nRetCode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, lHEnv)

' Set ODBC behavior
nRetCode = SQLSetEnvAttr(lHEnv, SQL_ATTR_ODBC_VERSION, _
    SQL_OV_ODBC2, SQL_IS_INTEGER)

' Get first driver
nRetCode = SQLDrivers(lHEnv, SQL_FETCH_FIRST, sDriverDesc, _
    Len(sDriverDesc), nDriverDescLength, sDriverAttr, _
    Len(sDriverAttr), nAttrLength)

sAll = ""
Do While nRetCode = SQL_SUCCESS

   ' Replace NULL separators between atributes with colons
   sDriverAttributes = Left$(sDriverAttr, nAttrLength - 1)
   Do
      x = InStr(sDriverAttributes, Chr$(0))
      If x = 0 Then Exit Do
      sDriverAttributes = Left$(sDriverAttributes, x - 1) _
         & " : " & Mid$(sDriverAttributes, x + 1)
   Loop
      
	' Save it
   sAll = sAll & Left$(sDriverDesc, nDriverDescLength) _
       & " / " & sDriverAttributes & vbCrLf
    
    ' Next data source
   nRetCode = SQLDrivers(lHEnv, SQL_FETCH_NEXT, sDriverDesc, _
      Len(sDriverDesc), nDriverDescLength, sDriverAttr, _
      Len(sDriverAttr), nAttrLength)

Loop

txtDrivers = sAll

nRetCode = SQLFreeHandle(SQL_HANDLE_ENV, lHEnv)

End Sub

Declare Function SQLDataSources Lib "odbc32.dll" (ByVal _
    EnvironmentHandle As Long, ByVal Direction As Integer, _
    ByVal ServerName As String, ByVal BufferLength1 As Integer, _
    NameLength1Ptr As Integer, ByVal Description As String, _
    ByVal BufferLength2 As Integer, NameLength2Ptr As Integer) As Integer

Private Sub ListODBCSources(  )

Dim lHEnv              As Long
Dim sServerName        As String * 32
Dim sDescription       As String * 128
Dim nServerNameLength  As Integer
Dim nDescriptionLength As Integer

lstDataSources.Clear

' Allocate an environment handle.
nRetCode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, lHEnv)

' Set ODBC behavior
nRetCode = SQLSetEnvAttr(lHEnv, SQL_ATTR_ODBC_VERSION, _
    SQL_OV_ODBC2, SQL_IS_INTEGER)

' Put first data source name in sServerName
nRetCode = SQLDataSources(lHEnv, SQL_FETCH_FIRST, sServerName, _
    Len(sServerName), nServerNameLength, sDescription, _
    Len(sDescription), nDescriptionLength)

lstDataSources.AddItem "DATA SOURCE / DRIVER"
Do While nRetCode = SQL_SUCCESS

    lstDataSources.AddItem Left$(sServerName, _
        nServerNameLength) & " / " & Trim0(sDescription)
    
    ' Next data source
    nRetCode = SQLDataSources(lHEnv, SQL_FETCH_NEXT, _
        sServerName, Len(sServerName), nServerNameLength, _
        sDescription, Len(sDescription), nDescriptionLength)

Loop

nRetCode = SQLFreeHandle(SQL_HANDLE_ENV, lHEnv)

End Sub