|  | Click Here!

Search the site:
EXPERT SEARCH

| Programming Languages
| Databases
| Security
| Web Services
| Network Services
| Middleware
| Components
| Operating Systems
| User Interfaces
| Groupware & Collaboration
| Content Management
| Productivity Applications
| Hardware
| Fun & Games

EarthWeb sites
Crossnodes
Datamation
Developer.com
DICE
EarthWeb.com
EarthWeb Direct
ERP Hub
Gamelan
GoCertify.com
HTMLGoodies
Intranet Journal
IT Knowledge
IT Library
JavaGoodies
JARS
JavaScripts.com
open source IT
RoadCoders
Y2K Info

#

- Day 19
  - ODBC Data Access via the ODBC API
    - Understanding the ODBC Interface
      - Figure 19.1.
    - Installing the ODBC Interface
      - Registering an ODBC Data Source
        - Figure 19.2.
        - Figure 19.3.
        - Figure 19.4.
        - Figure 19.5.
        - Figure 19.6.
    - Creating the ODBC API Library Routines
      - An ODBC API Crash Course
        - Figure 19.7.
        - Figure 19.8.
    - Listing 19.1. The ODBC API declarations.
    - Listing 19.2. The ODBC constant declarations.
      - The ODBC Library Routines
    - Listing 19.3. Adding the local variables to LIBODBC.BAS.
    - Listing 19.4. Coding the property routines for the objODBC class.
    - Listing 19.5. Coding the Class_Initialize event.
    - Listing 19.6. Coding the AllocateEnv function.
    - Listing 19.7. Coding the Connect function
    - .
    - Listing 19.8. Coding the Disconnect method.
    - Listing 19.9. Coding the DeallocateEnv function.
    - Listing 19.10. Coding the sqlErrorMsg subroutine.
    - Listing 19.11. Coding the Refresh function.
      - Figure 19.9.
    - Listing 19.12. Coding the ExecDirect method.
    - Listing 19.13. Coding the FetchRow method.
    - Listing 19.14. Coding the GetColumn method.
    - Listing 19.15. Adding the SaveColumn method to the class.
    - Listing 19.16. Adding the SaveRow method.
    - Listing 19.17. Coding the GetSQLRec method.
    - Listing 19.18. Coding the GetSQLField method.
    - Listing 19.19. Coding the Move methods for the class.
    - Listing 19.20. Coding the DelRow method.
    - Listing 19.21. Coding the AddRow method.
    - Listing 19.22. Coding the UpdateRow method.
    - Using the ODBC Library to Create a Data-Entry Form
    - - Building the ODBC Test Data-Entry Form
        - Figure 19.10.
      - Coding the ODBC Data-Entry Form
    - Listing 19.23. Coding the general declaration section of the form
    - .
    - Listing 19.24. Coding the StartDB routine.
    - Listing 19.25. Coding the cmdMove_Click event.
    - Listing 19.26. Coding the cmdAction_Click event.
      - Running the ODBC Data-Entry Form
        - Figure 19.11.
    - Looking at Other ODBC Considerations
    - Summary
    - Quiz
    - Exercises

## Day 19

## ODBC Data Access via the ODBC API

Today, you learn how to create data-entry forms that use the low-level Open Database Connectivity (ODBC) API routines to access existing databases. The ODBC API provides an alternative to using the Microsoft Jet database engine to access data. The ODBC interface usually is faster than the Microsoft Jet engine and uses less workstation memory than Microsoft Jet. The ODBC interface is capable of accessing data in client-server databases, desktop ISAM databases (such as dBASE, FoxPro, and so on), Microsoft Access format databases, and even Excel spreadsheets and text files.

Although data access via ODBC is fast, you can work only with Snapshot-type datasets. All data is accessed by using SQL statements to pass data to and from the ODBC data source. Also, data access via the ODBC API requires more code than using data controls or Visual Basic programming code. For these reasons, the ODBC API is not a good choice for every program. After you get an idea of what it takes to write a Visual Basic program using ODBC for data access, you can decide for yourself when to use the ODBC for data access.

> NOTE: You have a number of ODBC-type connection options with Visual Basic 5. In many cases, it is easier to use RDOs/RDC (for Visual Basic 5 Enterprise users) or the ODBCDirect options of the standard data control. This material is included here for those who want direct access to the ODBC interface via Visual Basic code instead of by using the object and ActiveX interfaces.

In today's lesson, you look at installing the ODBC Administrator on your system and using the Administrator program to define and register an ODBC data source for use with the ODBC API. You also briefly examine the ODBC operational model and the minimum ODBC APIs you need to create your own database programs using the ODBC interface.

You then use your knowledge of the ODBC API to construct an ActiveX DLL library that contains the essential API calls and a series of wrapper routines that you can use with all your Visual Basic programs to create data-entry screens for ODBC data sources. Finally, you build a Visual Basic data-entry form that calls the library routines and shows you how to implement a simple data-entry form by using standard Visual Basic controls.

When you complete this lesson, you will know how to register new data sources by using the ODBC Administrator program. You also will have a code library you can use to build solid Visual Basic applications that bypass the Microsoft Jet engine and use the ODBC API set to read and write databases.

### Understanding the ODBC Interface

The ODBC interface is a direct interface between your Visual Basic program and the target database. This interface was developed by Microsoft as a way to provide seamless access to external data formats. The first versions of ODBC were a bit buggy and, in some cases, slow. Although the ODBC interface now is one of the fastest data interfaces available, many programmers still mistakenly think the ODBC interface is too slow for production applications. This is not the case. As you'll see in today's lesson, using the ODBC interface usually is faster than using the Microsoft Jet database engine.

When you use the Microsoft Jet interface to access an ODBC data source, the Microsoft Jet interface does the talking to the ODBC interface, which then talks to the intermediate driver, which talks to the data source your Visual Basic program requested. When you use ODBC API calls, you bypass the Microsoft Jet layer, and your Visual Basic program talks directly to the ODBC interface. Figure 19.1 shows how this process looks on paper.
Figure 19.1. ODBC versus Microsoft Jet interface.

The ODBC interface doesn't really talk to databases. Instead, it links your Visual Basic program to defined data sources. These sources of data can be flat-file databases (such as dBASE and FoxPro), relational databases (such as Microsoft Access and SQL Server), or any file format for which an ODBC interface driver is available. Microsoft provides an ODBC interface driver for Excel spreadsheets and even delimited text files, for example. As long as a driver is available, you can use ODBC to access the data.

Even more important, when you use the ODBC interface to link to a data source, your Visual Basic program is not really talking to the data source directly. Your program talks to the ODBC front-end alone. The ODBC front-end uses removable drivers to translate your requests into a format understood by the target data source. The ODBC drivers exist as a middleman between the ODBC front-end and the target data file. Your Visual Basic programs talk to the ODBC front-end. The ODBC front-end talks to the appropriate driver. The driver talks to the target data file. The advantage of this design is that you easily can replace the translator routines (the drivers) to add improved performance or functionality without having to change your Visual Basic program or the target data source. Also, because the ODBC interface rules are published information, anyone who wants to make data available to users can create a new driver, and that driver then can work with all the installed versions of the ODBC interface that already exist.

Using the ODBC API interface has its limits, however. When you use the basic ODBC API to select and retrieve data, you actually are dealing with Snapshot-type data objects. You collect a set of data, bring it to your machine, make additions or modifications to the dataset, and send those changes back to the data source. Although this method is fast, it can be a bit cumbersome. Also, when you use the ODBC API, you are not able to use any data-bound controls. You are responsible for reading the data, placing it into form controls, and moving the data from the form controls back to the data source when needed. This means that you have more programming to do before you get a data-entry form up and running. Even with these drawbacks, using the ODBC API to access your data can add increased flexibility to your Visual Basic database programs.

#### Installing the ODBC Interface

The most recent version of the ODBC interface is included in the Visual Basic 5 installation files. If you did not install the ODBC interface when you first installed Visual Basic 5, you need to do it now in order to continue the lesson. If you already have installed the ODBC interface, you can skip this section and move on to the section "Registering an ODBC Data Source."

> NOTE: You also might have other software packages that installed the ODBC interface on your system. Look for a program called `ODBCAD32.EXE`. If you do not find this program, refer to the Visual Basic 5 installation disks or CD-ROM to install the ODBC interface.

The ODBC kit that ships with Visual Basic 5 contains drivers for SQL Server. Drivers also are available for accessing desktop file formats such as dBASE, FoxPro, Microsoft Access, and Excel spreadsheets. The ODBC installation options appear when you first install Visual Basic 5. For a full installation, you need to check not just the SQL Server and Oracle Drivers, but also the ISAM drivers (for Microsoft Jet use) and the Desktop ODBC drivers (for ODBC use).

Now that you have the ODBC Administrator installed, you are ready to define an ODBC data source that you can use with your Visual Basic 5 programs.

#### Registering an ODBC Data Source

The ODBC interface is based on the idea that defined data sources are available for users and programs to access. Each desktop has its own list of available ODBC data sources. On 16-bit systems, this list of ODBC data sources is kept in the `ODBC.INI` file in the `\WINDOWS\SYSTEM` directory. On 32-bit systems, the information is stored in the Registry under the `SOFTWARE/ODBC` keys.

> WARNING: Even though you can call up the `ODBC.INI` file with a text editor or open the Windows Registry by using `REGEDIT.EXE`, I do not recommend that you alter these entries using anything other than the ODBC Administrator program. Incorrect data in the ODBC entries in the INI file or in the Registry can cause the ODBC interface to behave unpredictably or to fail completely.

Each of these entries contains basic information about the defined data source, the drive used to access the data, and possibly additional information, depending on the data source and driver used. It is easy to define and register a new ODBC data source; to understand this process, create an ODBC data source that you can use later in this lesson.

First, load the ODBC Administrator program by locating and executing the `ODBCAD32.EXE` program. You can find it in the Control Panel, as shown in Figure 19.2; just double-click the icon.
Figure 19.2. Locating the ODBC Administrator program (`ODBCAD32.EXE`).

When you first start the ODBC Administrator, you see a tabbed dialog box that lists all the data sources currently registered for your workstation, as shown in Figure 19.3.
Figure 19.3. Viewing the registered ODBC data sources.

To define a new ODBC data source, make sure that the User DSN tab is selected and click Add. The Create New Data Source dialog box appears. Select the Microsoft Access Driver (`*.mdb`) and click Finish. The ODBC Microsoft Access 97 Setup dialog box for creating a new ODBC data source then appears.

The Data Source Name field contains the string you use when you call the ODBC connection from your Visual Basic 5 program. The Description field just contains a comment to remind you of the contents of the data source. Enter ODBC API Test in the Data Source Name field and Testing the ODBC API Set in the Description field (see Figure 19.4).
Figure 19.4. Defining a new ODBC data source.

In the Database section, click Select to access the Select Database dialog box shown in Figure 19.5. Locate and select the `C:\TYSDBVB5\SOURCE\CHAP19\TEST\SQLODBC.MDB` database. This is the database your program connects to each time it calls the ODBC data source TYSODBC. Your screen now should resemble the one shown in Figure 19.5.
Figure 19.5. Setting the Database property of the ODBC.

Click OK to store the new data-source definition to the `ODBC.INI` file. You now should be able to see the ODBC API Test data source in the listbox in the first ODBC dialog box form.

Figure 19.6 shows the entries in the `My Computer\HKEY_CURRENT_USER\Software\ODBC\ODBC.INI` section of the Registry file that were created when you added the ODBC API Test data source. Your entries might vary slightly.
Figure 19.6. Viewing the Registry entries for the new data source.

You can register as many data sources as you want. You can add various options to the data-source definition, depending on the target data file with which you are working. With Microsoft Jet databases, for example, you can add the `SYSTEM` security file to the data source to force users to provide valid user IDs and passwords. You also can adjust time-out values and mark the data source for exclusive use only. Other possible entries for other data file formats exist as well.

> TIP: Review the ODBC Administrator online help file for more information on configuring ODBC data sources.

### Creating the ODBC API Library Routines

Now that you know how to define ODBC data sources, you are ready to put together a Visual Basic 5 program that uses the ODBC interface to read and write data. To build your ODBC application, you need to declare several Windows API calls. These calls, along with a handful of predefined constants, are the heart of creating an ODBC-capable database program. This chapter doesn't review all the ODBC API calls--only the essential ones you need to get your ODBC application working.

> TIP: Visual Basic 5 ships with an API viewer that enables you to search for a particular API call and then copy and paste the information from the viewer directly into your Visual Basic 5 application.

After you declare the basic APIs, you need to create a set of Visual Basic routines that use these APIs to perform the low-level operations needed to execute ODBC commands from Visual Basic. After the low-level routines, you write a few mid-level functions that hide most of the grittier features of API programming. Finally, you create a few high-level routines you can use from any Visual Basic data-entry form to start off and maintain your ODBC connections.

#### An ODBC API Crash Course

Dozens of API calls for the ODBC interface are possible. You can write calls that enable you to inspect the type of ODBC driver you are using, to inspect the various details of the data source (database name, format, and so on), to gather information about the dataset (column names, data types for each field, length of each field, and so on), and to actually connect to the data source and move data to and from the ODBC data source. For this lesson, you focus only on those routines needed to move data back and forth through the ODBC interface.

Before you start coding the API calls and wrapper routines, you need to review the basic sequence of ODBC events required to connect to and share data with a registered ODBC data source. Several preliminary steps are involved before you actually can get any data from an ODBC data source. These steps involve defining an environment space for the ODBC connection, completing the actual connection, and then establishing an area of memory for passing data back and forth. Many of the API calls require or return unique values (called handles) to identify the memory spaces reserved for the ODBC interface. Figure 19.7 shows these operations. Most of the preliminary work for establishing an ODBC connection involves creating the handles you use throughout your program.

After the connection is established, you easily can share data with the target data source by using standard SQL statements. You can select a set of rows by using the `SELECT_FROM` statement. Whenever you request a dataset from the ODBC source, you need to go through several steps to actually pass the rows and columns from the source to your Visual Basic program. First, you execute the SQL statement. Then, to receive the dataset, you must determine the number of columns to receive and use that information to tell ODBC to queue up a row of data and send you each column in the row. You do this until you have received all the rows in the dataset. Figure 19.8 illustrates the process of executing the `SELECT` statement and collecting the resulting data.
Figure 19.7. The preliminary steps to establish an ODBC data-source connection.

######

Figure 19.8. Collecting results of a `SELECT` query from an ODBC connection.

You can perform single record adds, updates, and deletes by using SQL `INSERT`, `UPDATE`, and `DELETE` statements. You accomplish this simply by sending the SQL statement to the data source. You even can perform data table `CREATE` and `DROP` statements for most data sources.

The last set of ODBC routines you need to call from Visual Basic are the ones that safely close down the ODBC interface before your program exits. The shutdown routine is basically the same as the startup routine in reverse. First, you need to release the statement handle; then, close the ODBC connection and release the connection handle. Finally, you release the environment handle.

Throughout the process of communicating with the ODBC interface, you need to check for any error codes returned by ODBC. Because the functions are executing outside your Visual Basic program, ODBC errors do not automatically invoke your Visual Basic error handler. Every major ODBC call returns a success code or an error code. After you execute an ODBC API call, you need to check the return code. If it indicates that an error occurred, you also can call an ODBC routine that returns the detailed error message generated by the data source. When you build your ODBC library, you write a routine to perform this error checking. The Low-Level API Calls The first thing you need to do to build your library is to declare the necessary API calls for the ODBC interface. In your project, you declare only a subset of the total ODBC API calls; these are the calls essential for creating a basic data-entry form. You also need a handful of Visual Basic constants that you use throughout the library.

Load Visual Basic 5 and start a new ActiveX project. Add a BAS module to the project and set its Name property to modODBC. Add the API calls in Listing 19.1 to the project.

> TIP: If you want to save yourself some typing (and possible typing errors), you can find the `MODODBC.BAS` file in the `C:\TYSDBVB5\SOURCE\ CHAP19\PRJODBC` directory on the CD. You can load this file into your project by choosing Project | Add File.

### Listing 19.1. The ODBC API declarations.

    Option Explicit

    ` ----------------------------------------------------------
    ` ODBC API Declarations
    ` ----------------------------------------------------------

    `
    ` basic ODBC Declares
    Declare Function SQLAllocEnv Lib "odbc32.dll" (env As Long) As Integer

    Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal env As Long) As Integer

    Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal env As Long, _
     hDbc As Long) As Integer

    Declare Function SQLConnect Lib "odbc32.dll" (ByVal hDbc As Long, _
     ByVal Server As String, ByVal serverlen As Integer, _
     ByVal uid As String, ByVal uidlen As Integer, ByVal pwd As String, _
     ByVal pwdlen As Integer) As Integer

    Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal hDbc As Long) _
     As Integer

    Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal hDbc As Long) _
     As Integer

    Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal hDbc As Long, _
     hStmt As Long) As Integer

    Declare Function SQLFreeStmt Lib "odbc32.dll" (ByVal hStmt As Long, _
     ByVal EndOption As Integer) As Integer

    Declare Function SQLExecDirect Lib "odbc32.dll" (ByVal hStmt As Long, _
     ByVal sqlString As String, ByVal sqlstrlen As Long) As Integer

    Declare Function SQLNumResultCols Lib "odbc32.dll" (ByVal hStmt As Long, _
     NumCols As Integer) As Integer

    Declare Function SQLFetch Lib "odbc32.dll" (ByVal hStmt As Long) As Integer

    Declare Function SQLGetData Lib "odbc32.dll" (ByVal hStmt As Long, _
     ByVal Col As Integer, ByVal wConvType As Integer, ByVal lpbBuf As String, _
     ByVal dwbuflen As Long, lpcbout As Long) As Integer

    Declare Function sqlError Lib "odbc32.dll" Alias "SQLError" (ByVal env As _
     Long, ByVal hDbc As Long, ByVal hStmt As Long, ByVal SQLState As _
     String, NativeError As Long, ByVal Buffer As String, ByVal Buflen As _
     Integer, OutLen As Integer) As Integer

    Declare Function SQLSetConnectOption Lib "odbc32.dll" (ByVal hDbc&, _
     ByVal fOption%, ByVal vParam&) As Integer

    Declare Function SQLSetStmtOption Lib "odbc32.dll" (ByVal hStmt&, _
    ByVal fOption%, ByVal vParam&) As Integer

Listing 19.1 contains the ODBC API calls needed to implement basic connect, data-transfer, and disconnect operations. Now add the constants in Listing 19.2 to the module.

### Listing 19.2. The ODBC constant declarations.

    `
    ` misc constants
    Public Const sqlChar = 1
    Public Const sqlMaxMsgLen = 512
    Public Const sqlFetchNext = 1
    Public Const sqlFetchFirst = 2
    Public Const sqlStillExecuting = 2
    Public Const sqlODBCCursors = 110
    Public Const sqlConcurrency = 7
    Public Const sqlCursorType = 6

Save the module as `MODODBC.BAS`, and save the project as `PRJODBC.VBP`. Now you are ready to build the library functions that use these API calls to perform ODBC operations.

#### The ODBC Library Routines

The next set of routines are separated into two groups. The first group are routines that deal primarily with the ODBC interface. These routines are just wrappers for the API calls. Wrappers are Visual Basic routines that encapsulate the API call. Using wrappers makes it easy to change the underlying API call without having to change your code. If you want to use the 16-bit version of the ODBC, for example, you only need to change the `ODBC32.DLL` reference in each of the API calls to `ODBC.DLL`. Because you are using Visual Basic wrappers, you won't have to make any changes to your Visual Basic programs in order to use 16-bit ODBC.

The second set of library routines deals primarily with Visual Basic. These routines take the data from the ODBC and store it in Visual Basic variables and controls for use on your data-entry forms.

First, you need to add a few global variables that you use throughout the library. Select the default class module for the project and set its Name property to objODBC. Now add the declarations in Listing 19.3 to the file.

### Listing 19.3. Adding the local variables to LIBODBC.BAS.

    Option Explicit
    `
    `
    Private Const BUFFERLEN = 256
    `
    ` sql lock types
    Public Enum sqlLockType
        sqlreadonly = 1
        sqllock = 2
        sqlrowver = 3
        sqlValues = 4
    End Enum

    `
    ` sql cursor drivers
    Public Enum sqlCursorDriverType
        sqluseifneeded = 0
        sqlUseODBC = 1
        sqlUseDriver = 2
    End Enum

    `
    ` cursor types
    Public Enum sqlResultSetType
        sqlforwardonly = 0
        sqlKeyset = 1
        sqldynamic = 2
        sqlStatic = 3
    End Enum

    `
    ` sqlerror type
    Public Enum sqlErrorType
        sqlSuccess = 0
        sqlSuccessWithInfo = 1
        sqlerr = -1
        sqlNoDataFound = 100
    End Enum

    Public Enum sqlStatement
        sqlClose = 0
        sqlDrop = 1
        sqlUnbind = 2
        sqlResetParams = 3
    End Enum

    ` shared ODBC handle properties:
    Public hEnv As Long
    Public hDbc As Long
    Public hStmt As Long
    Public NumCols As Integer
    `
    ` local storage for properties
    Private strDataSource As String
    Private strUserID As String
    Private strPassword As String
    Private strSQL As String
    Private intRecordCount As Integer
    Private strTable As String
    Private strKey As String
    Private intCursorDriver As Integer
    Private intLockType As Integer
    Private intResultSetType As Integer

    ` internal use
    Dim intRecNum As Integer

Most of the items in Listing 19.3 define shared Public variables or enumerated data types for properties of the new class object. You use these values and storage locations both internally within the new class and from your external programs that call this class.

Now define the various properties by using the Visual Basic `Property Let` and `Property Get` statements. Listing 19.4 shows the complete listing of all property routines for this class object.

### Listing 19.4. Coding the property routines for the objODBC class.

    Public Property Get DataSource() As Variant
        DataSource = strDataSource
    End Property

    Public Property Let DataSource(ByVal vNewValue As Variant)
        strDataSource = vNewValue
    End Property

    Public Property Get UserID() As Variant
        UserID = strUserID
    End Property

    Public Property Let UserID(ByVal vNewValue As Variant)
        strUserID = vNewValue
    End Property

    Public Property Get Password() As Variant
        Password = strPassword
    End Property

    Public Property Let Password(ByVal vNewValue As Variant)
        strPassword = vNewValue
    End Property

    Public Property Get RecordCount() As Variant
        RecordCount = intRecordCount
    End Property

    Public Property Let RecordCount(ByVal vNewValue As Variant)
        ` na
    End Property

    Public Property Get SQL() As Variant
        SQL = strSQL
    End Property

    Public Property Let SQL(ByVal vNewValue As Variant)
        strSQL = vNewValue
    End Property

    Public Property Get Table() As Variant
        Table = strTable
    End Property

    Public Property Let Table(ByVal vNewValue As Variant)
        strTable = vNewValue
    End Property

    Public Property Get Key() As Variant
        Key = strKey
    End Property

    Public Property Let Key(ByVal vNewValue As Variant)
        strKey = vNewValue
    End Property

    Public Property Get CursorDriver() As sqlCursorDriverType
        CursorDriver = intCursorDriver
    End Property

    Public Property Let CursorDriver(ByVal vNewValue As sqlCursorDriverType)
        intCursorDriver = vNewValue
    End Property

    Public Property Get LockType() As sqlLockType
        LockType = intLockType
    End Property

    Public Property Let LockType(ByVal vNewValue As sqlLockType)
        intLockType = vNewValue
    End Property

    Public Property Get ResultSetType() As sqlResultSetType
        ResultSetType = intResultSetType
    End Property

    Public Property Let ResultSetType(ByVal vNewValue As sqlResultSetType)
        intResultSetType = vNewValue
    End Property

After coding all the property handlers, add the code from Listing 19.5 to the `Class_Initialize` event.

### Listing 19.5. Coding the Class_Initialize event.

    Private Sub Class_Initialize()
        `
        ` init props
        `
        strDataSource = ""
        strUserID = ""
        strPassword = ""
        strSQL = ""
        intRecordCount = 0
        intCursorDriver = sqlforwardonly
        intLockType = sqlreadonly
        `
    End Sub

Save the class modules as `objODBC.cls` before continuing to the next section. Now you're ready for the first set of Visual Basic routines. Mid-Level Routines The mid-level routines handle the direct calls to the ODBC API and provide simple error checking. The first of the routines allocates an environment handle. This handle is needed before you can attempt to connect to the ODBC interface.

Create a new function called `AllocateEnv` and add the code in Listing 19.6.

### Listing 19.6. Coding the AllocateEnv function.

    Private Function AllocateEnv()
        `
        ` Allocates an ODBC environment handle.
        ` Stores result to hEnv property
        `
        Dim intResult As Integer
        `
        intResult = SQLAllocEnv(hEnv)
        `
        If intResult <> sqlSuccess Then
            Err.Raise vbObjectError + 1, App.EXEName, "Cannot allocate environment handle"
        End If
        `
        AllocateEnv = sqlSuccess
        `
    End Function

The routine in Listing 19.6 calls the `SQLAllocEnv` API and checks for any errors. The `SQLAllocEnv` API establishes an environment for all ODBC transactions for this session. The `hEnv` variable that is set here holds a unique number that identifies all transactions that pass from your Visual Basic program to the ODBC interface.

Create a new function called `Connect`, as shown in Listing 19.7. This routine handles the details of completing a connection to the ODBC data source.

### Listing 19.7. Coding the Connect function.

    Public Function Connect() As Integer
        `
        ` Allocates and establishes connection
        ` to DataSource stored in DataSource
        ` property.
        `
        Dim intResult As Integer
        `
        AllocateEnv
        `
        ` Allocate connection handle:
        intResult = SQLAllocConnect(hEnv, hDbc)
        If intResult <> sqlSuccess Then
            Err.Raise vbObjectError + 3, App.EXEName, "Unable to allocate connection handle"
            Connect = intResult
            Exit Function
        End If
        `
        ` Set cursor driver
        intResult = SQLSetConnectOption(hDbc, sqlODBCCursors, intCursorDriver)
        If intResult <> sqlSuccess Then
            sqlErrorMsg "Error Setting CursorDriver"
            Exit Function
        End If
        `
        ` Login to data source
        intResult = SQLConnect(hDbc, strDataSource, Len(strDataSource), strUserID, Len(strUserID), strPassword, Len(strPassword))
        If intResult <> sqlSuccess And intResult <> sqlSuccessWithInfo Then
            sqlErrorMsg "Unable to connect to DataSource [" & strDataSource & "]"
            Connect = intResult
            Exit Function
        End If
        `
        ` Allocate statement handle.
        intResult = SQLAllocStmt(hDbc, hStmt)
        If intResult <> sqlSuccess Then
            sqlErrorMsg "Unable to allocate statement handle"
            Connect = intResult
            Exit Function
        End If
        `
        ` set cursor type (result set)
        intResult = SQLSetStmtOption(hStmt, sqlCursorType, intResultSetType)
        If intResult <> sqlSuccess Then
            sqlErrorMsg "Error Setting ResultsetType"
            Exit Function
        End If
        `
        ` set locktype
        intResult = SQLSetStmtOption(hStmt, sqlConcurrency, intLockType)
        If intResult <> sqlSuccess Then
            sqlErrorMsg "Error Setting LockType"
            Exit Function
        End If
        `
        Connect = sqlSuccess
        `
    End Function

The routine in Listing 19.7 performs several chores. The first operation establishes a data-source connection handle. Then, after setting the connection type by using the selected CursorDriver, the actual attempt to connect to the data source is performed. The DataSource, UserID, and Password properties are used for this. Next, the ODBC statement handle is established, and the ResultSetType and LockType properties are used to define the details of the connection. The statement handle is used as the unique identifier whenever you want to share data with the ODBC data source.

You also need to disconnect the ODBC link when you exit the program. Create a new function called `Disconnect` and add the code in Listing 19.8.

### Listing 19.8. Coding the Disconnect method.

    Public Function Disconnect()
        `
        ` disconnect from the data source
        `
        Dim intResult As Integer
        `
        ` Deallocate statement handle:
        If hStmt <> 0 Then
            intResult = SQLFreeStmt(hStmt, sqlDrop)
            If intResult <> sqlSuccess Then
                Err.Raise vbObjectError + 6, App.EXEName, "Unable to free statement handle"
                Disconnect = intResult
            End If
        End If
        `
        ` Disconnect
        If hDbc <> 0 Then
            intResult = SQLDisconnect(hDbc)
            If intResult <> sqlSuccess Then
                Err.Raise vbObjectError + 7, App.EXEName, "Unable to disconnect from data source"
                Disconnect = intResult
            End If
        End If
        `
        ` Deallocate connection handle
        If hDbc <> 0 Then
            intResult = SQLFreeConnect(hDbc)
            If intResult <> sqlSuccess Then
                Err.Raise vbObjectError + 8, App.EXEName, "Unable to deallocate connection handle"
                Disconnect = intResult
            End If
        End If
        `
        DeallocateEnv
        `
        Disconnect = sqlSuccess
        `
    End Function

You can see that Listing 19.8 performs the same three functions as `ODBCConnect`, only this time in reverse. First, it releases the statement handle, and then it performs the actual disconnect of the ODBC interface. Finally, the routine releases the connection handle.

Of course, you need a routine to release the environment handle, too. Create the `DeallocateEnv` function and enter the code in Listing 19.9.

### Listing 19.9. Coding the DeallocateEnv function.

    Private Function DeallocateEnv()
        `
        ` Frees specified env. handle
        ` clears stored in hEnv property
        `
        Dim intResult As Integer
        `
        If hEnv <> 0 Then
            intResult = SQLFreeEnv(hEnv)
            If intResult <> sqlSuccess Then
                Err.Raise vbObjectError + 2, App.EXEName, "Unable to free environment handle"
                DeallocateEnv = intResult
                Exit Function
            End If
        End If
        `
        DeallocateEnv = sqlSuccess
        `
    End Function

Listing 19.9 is a simple routine. It tells the ODBC interface that you are done with the session and returns any resulting codes.

The last mid-level routine you need is an ODBC error routine. This routine gathers any error information sent to your Visual Basic program from the ODBC data source. ODBC data sources are capable of sending more than one line of error information. For this reason, you write the routine as a loop that continues to ask for error messages until there are none to be found.

Create a new `Public Sub` called `sqlErrorMsg` and enter the code in Listing 19.10.

### Listing 19.10. Coding the sqlErrorMsg subroutine.

    Public Sub sqlErrorMsg(strMsg As String)
        `
        ` report detailed SQL Error
        `
        Dim strSQLState As String * 16
        Dim strErrorMsg As String * sqlMaxMsgLen
        Dim intErrorMsgLen As Integer
        Dim intOutLen As Integer
        `
        Dim lngErrCode As Long
        Dim strErrCode As String
        Dim intResult As Integer
        Dim intTemp As Integer
        `
        strSQLState = String(16, 0)
        strErrorMsg = String(sqlMaxMsgLen - 1, 0)
        `
        Do
            intResult = sqlError(hEnv, hDbc, hStmt, strSQLState, lngErrCode, strErrorMsg, Len(strErrorMsg), intErrorMsgLen)

            If intResult = sqlSuccess Or intResult = sqlSuccessWithInfo Then
                If intErrorMsgLen = 0 Then
                    Err.Raise vbObjectError + 9, App.EXEName, "Success or SuccessWithInfo Error"
                Else
                    If lngErrCode = 0 Then
                        strErrCode = ""
                    Else
                        strErrCode = Trim(CStr(lngErrCode)) & " "
                    End If
                    Err.Raise vbObjectError + 10, App.EXEName, "<" & strMsg & "> " & strErrCode & Left(strErrorMsg, intErrorMsgLen)
                End If
            End If
        Loop Until intResult <> sqlSuccess
        `
    End Sub

The routine in Listing 19.10 checks the state of the error code and returns any messages it can find. There are times when the error code is set by ODBC, but no message is returned. The routine checks for this and creates its own message, if needed.

Save this class module before you continue with the last set of ODBC library routines. High-Level Routines The last set of ODBC library routines deals primarily with the duties required to make Visual Basic capable of displaying, reading, and writing data via the ODBC interface. These routines take the datasets returned by ODBC and store them in Visual Basic list and grid controls. These controls then are used as holding areas by your Visual Basic program for filling and updating textboxes on your data-entry form. This method of storing result sets in a Visual Basic control reduces the amount of traffic over the ODBC link and improves the response time of your program.

> NOTE: In the examples here, you access relatively small datasets. If your ODBC interface requires the passing of very large datasets, you need to develop more sophisticated methods for storing and retrieving the resulting datasets. It is always a good idea to limit the size of the result set as much as possible, though, because passing large amounts of data over the ODBC link can adversely affect not just your Visual Basic program, but all programs that are using the same network.

The first high-level routine you build actually creates a dataset for your Visual Basic program. This routine handles the creation of the dataset by using all the properties that already have been set. This method is called right after the `Connect` method.

Create a new function called `Refresh` and add the code in Listing 19.11.

### Listing 19.11. Coding the Refresh function.

    Public Function Refresh()
        `
        ` collect data from result set
        `
        Dim intResult As Integer
        Dim intCols As Integer
        Dim intRows As Integer
        Dim strBuffer As String * BUFFERLEN
        Dim strItem As String
        Dim strData As String
        Dim lngOutLen As Long
        Dim intLoop As Integer

        `
        ` run the query
        intResult = ExecDirect
        If intResult <> sqlSuccess Then
            Refresh = sqlerr
            Exit Function
        End If
        `
        ` get the column count
        intResult = NumResultCols(intCols)
        If intCols = 0 Then
            Refresh = sqlerr
            Exit Function
        Else
            NumCols = intCols
        End If
        `
        ` set up for collection
        frmSQLData.rtbSQlData = ""
        strBuffer = String(BUFFERLEN, 0)
        intRows = 0
        `
        ` get data
        Do
            intResult = FetchRow() ` get a row
            Select Case intResult
                Case sqlNoDataFound
                    If intRows > 0 Then
                        Exit Do ` we're done
                    Else
                        Refresh = sqlerr
                        Exit Function
                    End If
                Case sqlSuccess
                    intRows = intRows + 1
                    strItem = ""
                    For intLoop = 1 To intCols
                        intResult = GetColumn(strBuffer, intLoop)
                        SaveColumn strItem, strBuffer
                    Next ` get another column
                    SaveRow strItem
                Case Else
                    intResult = SQLFreeStmt(hStmt, sqlClose)
                    Refresh = sqlerr
                    Exit Function
            End Select
        Loop ` get another row
        `
        intRecNum = 0 ` clear record pointer
        intRecordCount = intRows
        Refresh = sqlSuccess
        frmSQLData.Refresh
        `
    End Function

The routine in Listing 19.11 performs a number of important tasks. First, it calls the internal `ExecDirect` method. This method executes the actual SQL statement. Then, after returning from the `ExecDirect` call, the routine gets the number of data columns and clears a utility form that holds a rich text control. This rich text control is used as an internal buffer area for the downloaded dataset. You build that form in just a moment. Finally, a `Do` loop is established to collect each column of data and save it as a new row. When all rows have been collected, the routine exits.

Now add a single form to your project. Set its Name property to frmSQLData. Add a rich text control to the form and set its Name property to rtbSQLData. Now save the form as frmSQLData. It should look similar to the form shown in Figure 19.9 at this point. You refer to this form again later in the project.
Figure 19.9. Adding the frmSQLData utility form to the project.

Next, you need to code the `ExecDirect` method. This is the wrapper method for the `SQLDirectExec` API call. Add the code in Listing 19.12 to the class module.

### Listing 19.12. Coding the ExecDirect method.

    Public Function ExecDirect()
        `
        ` perform an SQL statement
        `
        Dim intResult As Integer
        `
        ` clear any in-process stuff
        If hStmt <> 0 Then
            intResult = SQLFreeStmt(hStmt, sqlClose)
            intResult = SQLFreeStmt(hStmt, sqlUnbind)
            intResult = SQLFreeStmt(hStmt, sqlResetParams)
        End If
        `
        If intResult <> sqlSuccess Then
            ExecDirect = intResult
            Err.Raise vbObjectError + 11, App.EXEName, "Error freeing old statement handle"
            intResult = SQLFreeStmt(hStmt, sqlClose)
            Exit Function
        End If
        `
        ` Do the query & wait
        intResult = SQLExecDirect(hStmt, SQL, Len(SQL))
        Do While intResult = sqlStillExecuting
            intResult = SQLExecDirect(hStmt, SQL, Len(SQL))
            DoEvents
        Loop
        `
        ` check for errors
        If intResult <> sqlSuccess Then
            ExecDirect = intResult
            Err.Raise vbObjectError + 12, App.EXEName, "Error executing Query"
            intResult = SQLFreeStmt(hStmt, sqlClose)
            Exit Function
        End If
        `
        ExecDirect = sqlSuccess
        `
     End Function

The code in Listing 19.12 clears any current pending statement and then executes the SQL statements stored in the SQL property of the class. The `Refresh` method calls a number of other important methods. The first of these is the `FetchRow` method. This gets a row of data from the ODBC data source. Add the code in Listing 19.13 to your class.

### Listing 19.13. Coding the FetchRow method.

    Public Function FetchRow()
        `
        ` get a row of data
        `
        Dim intResult As Integer
        `
        intResult = SQLFetch(hStmt)
        If intResult <> sqlSuccess Then
            If intResult <> sqlNoDataFound Then
                Err.Raise vbObjectError + 14, App.EXEName, "Error fetching row"
                FetchRow = intResult
            Else
                FetchRow = intResult
            End If
        Else
            FetchRow = sqlSuccess
        End If
        `

`End Function` Next, you need to add the `GetColumn` method. This moves a column of data from the data source to the local memory space. Add the code in Listing 19.14 to the class.

### Listing 19.14. Coding the GetColumn method.

    Private Function GetColumn(strBuffer As String, intCol As Integer)
        `
        ` get a column from the current row
        `
        Dim intResult As Integer
        Dim lngBufferLen As Long
        `
        intResult = SQLGetData(hStmt, intCol, sqlChar, strBuffer, BUFFERLEN, lngBufferLen)
        If intResult <> sqlSuccess Then
            GetColumn = intResult
            Err.Raise vbObjectError + 15, App.EXEName, "Error retrieving column data"
            Exit Function
        Else
            If lngBufferLen > 0 Then
                strBuffer = Left(strBuffer, lngBufferLen)
            Else
                strBuffer = ""
            End If
        End If
        `
        GetColumn = sqlSuccess
        `
    End Function

Now, add the `SaveColumn` method in Listing 19.15 to the class.

### Listing 19.15. Adding the SaveColumn method to the class.

    Private Sub SaveColumn(strRow As String, strColumn As String)
        `
        ` add column to row line
        `
        If Trim(strColumn) <> "" Then
             If Trim(strRow) = "" Then
                strRow = "||" & Trim(strColumn) ` mark first field
            Else
                strRow = strRow & "|" & (strColumn)
            End If
        Else
            strRow = strRow & "|"
        End If
        `
    End Sub

This routine just copies the retrieved column data into a hold variable. The marking values `"||"` (for a new record) and `"|"` (for a new field) are used to retrieve the data from local storage to the caller's data-input form controls later.

Now add the code in Listing 19.16 to the class. This is the `SaveRow` method. This is the code that copies the complete line of data into the rich textbox control for storage.

### Listing 19.16. Adding the SaveRow method.

    Private Sub SaveRow(strRow As String)
        `
        ` save row to rtb control
        `
        frmSQLData.rtbSQLData.Text = frmSQLData.rtbSQLData.Text & strRow & vbCrLf
        `
    End Sub

Now that you have loaded the rich textbox control with the data from the data source, you need a routine that moves individual rows from the control to the caller's input controls on the data-entry form. To do this, you need some support routines: the `GetSQLRec` and `GetSQLField` methods. Add the code in Listing 19.17 to your class.

### Listing 19.17. Coding the GetSQLRec method.

    Private Sub GetSQLRec(intLine As Integer, strLine As String, frmTemp As Object)
        `
        ` move a rec from the rtb into array
        `
        Dim intLoop As Integer
        Dim lngPosMark As Long
        Dim lngRecEnd As Long
        Dim intRec As Integer
        Dim strData As String
        `
        strData = frmSQLData.rtbSQLData.Text
        `
        lngPosMark = 1
        For intLoop = 1 To intRecordCount
            lngPosMark = InStr(lngPosMark, strData, "||")
            If lngPosMark <> 0 Then
                intRec = intRec + 1
                If intRec = intLine Then
                    Exit For
                End If
                lngPosMark = lngPosMark + 1
            End If
        Next
        `
        If intRec <> 0 Then
           lngRecEnd = InStr(lngPosMark, strData, vbCrLf)
           strLine = Mid(strData, lngPosMark + 2, lngRecEnd - (lngPosMark + 2))
        End If
        `
        ` now get fields
        If strLine <> "" Then
            For intLoop = 1 To NumCols
                frmTemp.sqlfield(intLoop - 1) = GetSQLField((strLine), intLoop) `frmTemp.sqlfield(intLoop) = GetSQLField(strRecLine, intLoop)
            Next
        End If

    End Sub

Note that the code in Listing 19.17 requires a few parameters. The first is the number of the rows requested. The second parameter is the data row returned, and the last is the caller's data form that contains the input controls. Notice that the routine assumes that the controls on the caller's form are called sqlField and are part of a control array. This is a requirement for all forms that call this library.

Next, you need the `GetSQLField` method. Add this from the code in Listing 19.18.

### Listing 19.18. Coding the GetSQLField method.

    Private Function GetSQLField(strLine As String, intField As Integer)
        `
        ` get a field from the line
        `
        Dim intLoop As Integer
        Dim lngPosMark As Long
        Dim lngPosEnd As Long
        Dim intCol As Integer
        `
        strLine = "|" & strLine & "|"
        lngPosMark = 1
        lngPosEnd = 0
        intCol = 0
        `
        For intLoop = 1 To NumCols
            lngPosMark = InStr(lngPosMark, strLine, "|")
            If lngPosMark <> 0 Then
                intCol = intCol + 1
                If intCol = intField Then
                    lngPosEnd = InStr(lngPosMark + 1, strLine, "|")
                    Exit For
                End If
                lngPosMark = lngPosMark + 1
            End If
        Next
        `
        If lngPosEnd <> 0 Then
            GetSQLField = Mid(strLine, lngPosMark + 1, lngPosEnd - (lngPosMark + 1))
        Else
            GetSQLField = ""
        End If
        `
    End Function

The routine in Listing 19.18 asks for the control to read the column number and the character used to delimit the columns in the list control. It takes this information and returns a string that can be used to populate a text control (or any other control) on a data-entry form. You learn how to use this in your data-entry forms in the next section.

Now that you have a method for retrieving a row and each field, you're ready to build the routines that handle moving the record pointer and loading the caller's form. Listing 19.19 shows all the code for the `MoveFirst`, `MoveNext`, `MovePrevious`, and `MoveLast` methods of the class. Add these methods to your project.

### Listing 19.19. Coding the Move methods for the class.

    Public Sub MoveFirst(frmTemp As Object)
        `
        ` move items from rtb into form controls
        `
        Dim strRecLine As String
        `
        ` position record pointer
        intRecNum = 1
        `
        GetSQLRec intRecNum, strRecLine, frmTemp
        `
    End Sub

    Public Sub MoveLast(frmTemp As Object)
        `
        ` move items from rtb into form controls
        `
        Dim strRecLine As String
        `
        ` position record pointer
        intRecNum = intRecordCount
        `
        GetSQLRec intRecNum, strRecLine, frmTemp
        `
    End Sub

    Public Sub MovePrevious(frmTemp As Object)
        `
        ` move items from rtb into form controls
        `
        Dim strRecLine As String
        `
        ` position record pointer
        If intRecNum > 1 Then
            intRecNum = intRecNum - 1
        Else
            intRecNum = 1
        End If
        `
        GetSQLRec intRecNum, strRecLine, frmTemp
        `
    End Sub

    Public Sub MoveNext(frmTemp As Object)
        `
        ` move item from rtb into form controls
        `
        Dim strRecLine As String
        `
        ` position record pointer
        If intRecNum < intRecordCount Then
            intRecNum = intRecNum + 1
        End If
        `
        GetSQLRec intRecNum, strRecLine, frmTemp
        `
    End Sub

You need only three more library functions before you have a complete ODBC database kit. You need routines that can write an updated existing record, add a new record, and delete an existing record from the dataset. These three routines can be called from your data-entry form and look much like the standard add, edit, and delete operations used with data-bound controls.

First, create the `DelRow` method and enter the code in Listing 19.20.

### Listing 19.20. Coding the DelRow method.

    Public Sub DelRow(frmTemp As Object)
        `
        ` remove a row from the table
        `
        Dim intResult As Integer
        Dim strSQL As String
        `
        ` make statement
        strSQL = "DELETE * FROM " & strTable & " WHERE "
        strSQL = strSQL & strKey & "='" & frmTemp.sqlfield(0) & "`"
        `
        ` make the call
        intResult = SQLExecDirect(hStmt, strSQL, Len(strSQL))
        If intResult <> sqlSuccess Then
            sqlErrorMsg "Unable to delete row"
            intResult = SQLFreeStmt(hStmt, sqlClose)
        End If
        `
    End Sub

The routine in Listing 19.20 is designed to delete the current record loaded into the text controls on the form, and it requires only a pointer to the form as a parameter. The name of the data table you are updating and name of the key field are pulled from properties already set by the user. For all your ODBC datasets, you are assuming that the first field in the list is the primary key field.

> NOTE: Assuming that the primary key field is always the first physical field in the dataset can be a limitation when you're dealing with secondary tables and other non-normalized datasets. For now, however, this assumption handles most of your data-entry needs. As you develop more skill with ODBC routines, you can modify these routines or add others that give you more flexibility in sharing data over ODBC connections.

The routine in Listing 19.20 builds a standard `DELETE` query using the parameters you supplied to it and then executes the SQL `DELETE`, returning any error messages that might result.

Now you build the `AddRow` method. This routine builds a standard `APPEND` query using the `INSERT INTO` syntax. Create the new function and add the code in Listing 19.21.

### Listing 19.21. Coding the AddRow method.

    Public Sub AddRow(frmTemp As Object)
        `
        ` add a new row of data to the table
        `
        Dim intResult As Integer
        Dim strSQL As String
        Dim intLoop As Integer
        `
        strSQL = "INSERT INTO " & strTable & " VALUES("
        `
        For intLoop = 1 To NumCols
            strSQL = strSQL & "`" & frmTemp.sqlfield(intLoop - 1) & "`"
            If intLoop <> NumCols Then
                strSQL = strSQL & ","
            End If
        Next
        strSQL = strSQL & ")"
        `
        ` now make the call
        intResult = SQLExecDirect(hStmt, strSQL, Len(strSQL))
        If intResult <> sqlSuccess Then
            sqlErrorMsg "Unable to add row"
            intResult = SQLFreeStmt(hStmt, sqlClose)
        End If
        `
    End Sub

The last routine in your library performs an update of an existing record. The simplest way to accomplish this is to delete the existing record and replace it with the new updated version. This can be done with two SQL statements: a `DELETE` query, followed by an `INSERT INTO` statement. A more sophisticated approach would be to build a series of `UPDATE` statements that update each field of the row, one at a time. For the example here, you use the `DELETE/INSERT` method, because it takes less code and is easier to understand.

> WARNING: In certain situations, you do not want to perform updates by using the `DELETE/INSERT` method. If you have defined a delete cascade in a relationship between two tables, performing a `DELETE/INSERT` on the one side of the one-to-many relationship results in the deletion of all the related records on the many side of the relationship. In cases where yot?might define delete cascades, you should use only the `UDPATE` method.

Create the `UpdateRow` method and add the code in Listing 19.22.

### Listing 19.22. Coding the UpdateRow method.

    Public Sub UpdateRow(frmTemp As Object)
        `
        ` replace an existing row
        `
        Dim intResult As Integer
        Dim strDelSQL As String
        Dim strAddSQL As String
        Dim intLoop As Integer
        `
        strDelSQL = "DELETE * FROM " & strTable & " WHERE "
        strDelSQL = strDelSQL & strKey & "='" & frmTemp.sqlfield(0) & "`"
        `
        strAddSQL = "INSERT INTO " & strTable & " VALUES("
        `
        For intLoop = 1 To NumCols
            strAddSQL = strAddSQL & "`" & frmTemp.sqlfield(intLoop - 1) & "`"
            If intLoop <> NumCols Then
                strAddSQL = strAddSQL & ","
            End If
        Next
        strAddSQL = strAddSQL & ")"
        `
        ` make the calls
        intResult = SQLExecDirect(hStmt, strDelSQL, Len(strDelSQL))
        If intResult <> sqlSuccess Then
            sqlErrorMsg "Unable to delete row"
            intResult = SQLFreeStmt(hStmt, sqlClose)
        End If
        `
        intResult = SQLExecDirect(hStmt, strAddSQL, Len(strAddSQL))
        If intResult <> sqlSuccess Then
            sqlErrorMsg "Unable to add row"
            intResult = SQLFreeStmt(hStmt, sqlClose)
        End If
        `
    End Sub

As you can see, this routine first executes a `DELETE` query, and then it executes an `INSERT` statement.

Save the class module. You now have completed the ODBC library routines. The next step is to build a simple data-entry form that uses the ODBC library to open a dataset and pass information to and from the data via the ODBC interface.

### Using the ODBC Library to Create a Data-Entry Form

Now that you have your ODBC library, you are ready to build a data-entry form that uses the ODBC interface for database access. For this example, you build a simple data-entry form that has the usual navigation buttons (First, Next, Back, and Last) and the record buttons (Add, Delete, and Update).

You write this form by using the new class library in a way that is almost identical to the way you create a data-entry form using the standard data control. You add controls, set a few properties, and then execute a few methods.

#### Building the ODBC Test Data-Entry Form

Add a new Standard EXE project to the current project group (the one with the prjODBC project). Use Figure 19.10 and Table 19.1 as a guide for laying out the test form.
Figure 19.10. Laying out the SQLTest form.
Table 19.1. Controls for the SQLTest form.

Control | Property | Setting
VB.Form | Name | frmSQLTest
| Caption | "SQL ODBC API Test Form"
| ClientHeight | 1515
| ClientLeft | 60
| ClientTop | 345
| ClientWidth | 4890
VB.CommandButton | Name | cmdAction
| Caption | "&Close"
| Height | 300
| Index | 3
| Left | 3900
| Top | 1140
| Width | 900
VB.CommandButton | Name | cmdAction
| Caption | "&Update"
| Height | 300
| Index | 2
| Left | 3900
| Top | 780
| Width | 900
VB.CommandButton | Name | cmdAction
| Caption | "&Add"
| Height | 300
| Index | 0
| Left | 3900
| Top | 60
| Width | 900
VB.CommandButton | Name | cmdMove
| Caption | "&Last"
| Height | 300
| Index | 3
| Left | 2940
| Top | 1140
| Width | 840
VB.CommandButton | Name | cmdMove
| Caption | "&Next"
| Height | 300
| Index | 2
| Left | 1980
| Top | 1140
| Width | 900
VB.CommandButton | Name | cmdMove
| Caption | "&Back"
| Height | 300
| Index | 1
| Left | 1020
| Top | 1140
| Width | 900
VB.CommandButton | Name | cmdMove
| Caption | "&Top"
| Height | 300
| Index | 0
| Left | 60
| Top | 1140
| Width | 900
VB.TextBox | Name | sqlField
| Height | 300
| Index | 1
| Left | 1320
| Top | 720
| Width | 2400
VB.TextBox | Name | sqlField
| Height | 300
| Index | 0
| Left | 1320
| Top | 360
| Width | 1200
VB.CommandButton | Name | cmdAction
| Caption | "&Delete"
| Height | 300
| Index | 1
| Left | 3900
| Top | 420
| Width | 900
VB.Label | Name | Label2
| Caption | "EMail:"
| Height | 300
| Left | 60
| Top | 780
| Width | 1200
VB.Label | Name | Label1
| Caption | "Name:"
| Height | 300
| Left | 60
| Top | 420
| Width | 1200

Save this form as `FRMSQLTEST.FRM` and the project as `PRJSQLTEST.VBP`. Now you're ready to add the code to the form.

#### Coding the ODBC Data-Entry Form

You need to add code in just a few places on the form. First, you need to add two form-level variables, as shown in Listing 19.23. These variables are used throughout the form.

### Listing 19.23. Coding the general declaration section of the form.

    Option Explicit

    `
    ` form-level vars
    Dim objSQL As Object
    Dim blnAdding As Boolean

Next, you need to create a custom routine that sets the new class properties and initializes the data connection. Create a new `Public Sub` called `StartDB` and enter the code in Listing 19.24.

### Listing 19.24. Coding the StartDB routine.

    Public Sub StartDB()
        `
        ` handle chores of connecting and getting data
        `

        ` create reference to ODBC object
        Set objSQL = New objODBC
        `
        ` populate properties
        objSQL.DataSource = "ODBC API Test"
        objSQL.UserID = "admin"
        objSQL.Password = ""
        objSQL.SQL = "SELECT * FROM TestTable"
        objSQL.Table = "TestTable"
        objSQL.Key = "Name"
        objSQL.ResultSetType = sqlStatic
        objSQL.CursorDriver = sqlUseODBC
        objSQL.LockType = sqlValues
        `
        ` do real work
        objSQL.Connect ` establish connection
        objSQL.Refresh ` build dataset
        objSQL.MoveFirst Me ` display first row
        `
    End Sub

Most of the material here should look familiar. After creating a reference to the class and setting several properties, the `Connect`, `Refresh`, and `MoveFirst` methods are executed to fill the form with data.

> NOTE: The values used to initialize the variables are related to the ODBC data source you defined in the "Registering an ODBC Data Source" section of this chapter. If you have not completed the first part of this chapter, you cannot run this program with these variables.

Now add the following lines to the `Form_Load` event of the form:

    Private Sub Form_Load()
        `
        ` startup connection
        `
        StartDB
        `
    End Sub

You need to add the code that handles all the user actions behind the cmdMove command button array. This button array handles the navigation chores (First, Last, Next, and Back). Enter the code in Listing 19.25 into the `Click` event of the cmdMove button.

### Listing 19.25. Coding the cmdMove_Click event.

    Private Sub cmdMove_Click(Index As Integer)
        `
        ` move record pointer
        `
        Select Case Index
            Case 0 `
                objSQL.MoveFirst Me
            Case 1
                objSQL.MovePrevious Me
            Case 2
                objSQL.MoveNext Me
            Case 3
                objSQL.MoveLast Me
        End Select
        `
    End Sub

Finally, you need to code the `Click` event of the cmdAction button. This handles the record-modification chores (Add, Update, Delete, and Refresh). Add the code in Listing 19.26 to the project.

### Listing 19.26. Coding the cmdAction_Click event.

    Private Sub cmdAction_Click(Index As Integer)
        `
        ` handle action selections
        `
        Select Case Index
            Case 0 ` add
                sqlField(0) = ""
                sqlField(1) = ""
                blnAdding = True
            Case 1 ` delete
                If blnAdding = True Then
                    blnAdding = False
                End If
                objSQL.DelRow Me
            Case 2 ` udpate
                If blnAdding = True Then
                    objSQL.AddRow Me
                    blnAdding = False
                Else
                    objSQL.UpdateRow Me
                End If
            Case 3 ` close
                objSQL.Disconnect
                Unload Me
        End Select
        `
    End Sub

> NOTE: The code in Listing 19.26 is only the most basic code needed to add, delete, and update a record. If you want to use this form in a production setting, you should add code to confirm deletes and enable users to cancel updates or adds.

Now save the project as `PRJTEST.VBP`. You now are ready to run the ODBC data-entry form.

#### Running the ODBC Data-Entry Form

Now that both the library and the form routines are completed, you are ready to run the program. When you first run the program, you see the data-entry form with the first record displayed, as shown in Figure 19.11.

You now can use this screen to walk through the dataset by using the command buttons (First, Last, Next, and Back). You also can add, edit, and delete records in the dataset by using the appropriate buttons.
Figure 19.11. Running the SQLTest project.

You now have a fully functional data-entry screen for ODBC data sources. You can improve this form by adding other routines that improve the error handling and increase the user friendliness of the form. You even can use this form as a basis for your own ODBC data-entry forms.

### Looking at Other ODBC Considerations

Now that you know how to build ODBC data-entry forms, you should keep in mind a few ODBC-related items as you build ODBC-enabled Visual Basic applications:

- ODBC connection usage: With some RDMBS systems, each connection you make counts as a user connection to the back-end data source. If your client has a 10-user license and your Visual Basic application opens three ODBC datasets, only seven connections are left for the entire network. If you run three versions of the same program at the same time, you are using nine connections. SQL Server is able to use smart caching to eliminate this problem, but some systems do not. It is a good idea to minimize the number of open connections your Visual Basic programs require.

- Install files: If you are using ODBC to connect to data, you need to include the ODBC setup files with your Visual Basic program setup kit. See the Visual Basic documentation on the Setup Wizard for more information on the required files to include for ODBC-enabled applications.

- .INI and Registry settings: A number of ODBC-related variables can affect performance. The 16-bit ODBC interface uses `ODBC.INI` (the list of defined data sources), `ODBCINST.INI` (the list of installed ODBC drivers available), and `ODBCISAM.INI` and `ODBCDDP.INI` (which deal with the ISAM-type interfaces, such as Microsoft Access, Excel, FoxPro, and so on). In 32-bit systems, this information is stored in the system Registry. Although you should not edit these files directly, you should know where these values are stored when you're debugging your ODBC applications.

- Tracing ODBC activity: You can turn on ODBC trace files from the ODBC Administrator program. This enables you to watch the message activity between your application and the ODBC interface, which can be very informative when you're attempting to locate bugs or performance problems. The Trace log does take up a lot of disk space after a short time, though. You should turn on the trace capability only when you absolutely need it.

- Remote data control alternative: If your Visual Basic program will be operating only in a 32-bit environment and you have the Enterprise Edition of Visual Basic, you can use the RDC and the RDOs to connect to the ODBC data source. The RDC/RDOs platform is a replacement for the ODBC API you learned today. Although it is easier to deal with the RDC/RDOs platform, you must use the 32-bit version of Visual Basic, and your program must run on a 32-bit operating system. If your program must run on a 16-bit operating system, you still can use the ODBC API and the examples from this chapter.

### Summary

Today you learned how to use the ODBC API set to directly link your Visual Basic program to target data sources via the ODBC interface. The ODBC interface generally is faster than Microsoft Jet when it comes to linking to ODBC-defined data sources.

You also looked at installing the ODBC interface on your workstation and using the ODBC Administrator program to install ODBC driver sets and to define data sources for ODBC connections.

You learned how to build a program library that uses a minimum set of ODBC API calls along with several Visual Basic wrapper routines. This library set provides the basic functions necessary to read and write data to and from a defined ODBC data source. You can use these routines to create fully functional data-entry forms for ODBC data sources.

Finally, you used the library routines to build a data-entry form that opens a link to a defined ODBC data source and enables users to read and write data records for the ODBC data source.

### Quiz

  1. What do the letters ODBC stand for?
  2. Why is the ODBC API interface faster than the Microsoft Jet interface when connecting to defined ODBC data sources?
  3. What are some of the drawbacks to using the ODBC API to link to databases?
  4. What program do you use to define an ODBC data source for the workstation?
  5. Can you use the ODBC interface to connect to nondatabase files, such as spreadsheets or text files?
  6. When you write ODBC-enabled Visual Basic applications, can you use the same set of API declarations for the 16-bit version of Visual Basic 5 that you use for the 32-bit version of Visual Basic 5?
  7. What are the four preliminary steps you must complete before you can pass an SQL `SELECT` statement to the newly opened ODBC data source?

### Exercises

Suppose that you have been given the assignment of creating a remote data-entry form for reviewing and updating data in a centrally located data file. The data currently is stored in a Microsoft Access database on the central file server, but it might soon be converted to an SQL Server database in another location.

You cannot always know the actual columns that exist in the data table, because the layout of the table changes based on information entered each month. The form should be flexible enough to determine the columns available and present those columns to the user for data entry. The program also should be flexible enough to allow for minimum disruption of the file even when the database is converted from Microsoft Access to SQL Server database format.

Your first task is to define an ODBC data source at your workstation that has the Microsoft Access data file `C:\TYSDBVB\CHAP19\EXER19.MDB` as its data source name. You want to access the Transactions table that exists in the `EXER19.MDB` database. The key field of the Transactions table is called OrderNbr.

Then, modify the `TYSODBC.VBP` project to open this data source and enable users to review and update data in the spreadsheet.

| Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details.