Day 19

ODBC Data Access Via the ODBC API Interface

Today, you'll 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 interface provides an alternative to using the Microsoft JET to access data. The ODBC interface is usually faster than Microsoft JET and uses up less workstation memory than Microsoft JET, too. 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 only work with snapshot-type data sets. All data access is done 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.

In today's lesson, you'll learn how to install the ODBC Administrator on your system and how to use the administrator program to define and register an ODBC data source for use with the ODBC API interface. We'll also briefly discuss the ODBC operational model and show you the minimum ODBC APIs you'll need to create your own database programs using the ODBC interface.

You will then use your knowledge of the ODBC API to construct a code library that will contain 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'll build a Visual Basic data entry form that will call the library routines and show you how to implement a simple subform using standard Visual Basic controls.

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


NOTE:

Throughout today's lesson, you will be working exclusively in the 16-bit version of Visual Basic 4.0. The 16-bit version has a slightly different ODBC Administrator program and uses different API calls than the 32-bit version. If you have been using the 32-bit version of Visual Basic 4.0 for the other chapters, switch to the 16-bit version for today. The information you learn here can easily be transported to the 32-bit version later. After you learn how to use the 16-bit version of the ODBC API, you can modify the API calls to use the ODBC32.DLL to gain access to the 32-bit ODBC drivers installed on your workstation.


What Is the ODBC Interface?

The Open Database Connectivity (ODBC) interface is a direct interface between your Visual Basic program and the target database. This interface has been 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 is now understood to be 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 is usually faster than using the Microsoft JET database engine.

When you use the Microsoft JET interface to access an ODBC data source, the Microsoft JET 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 looks on paper.

Figure 19.

The difference between ODBC and Microsoft JET interfaces.

The ODBC interface doesn't really talk to databases. Instead, it links your Visual Basic program to defined data sources. These sources of data could 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. For example, Microsoft provides an ODBC interface driver for Excel spreadsheets and even delimited text files. As long as a driver is available, you can use ODBC to access the data.

Even more importantly, 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 can easily 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 can then 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 ODBC API to select and retrieve data, you are actually dealing with Snapshot-type data objects. You collect a set of data, bring it to your machine, make additions or modifications to the data set, and send those changes back to the data source. Although this is fast, it can be a bit cumbersome. Also, when you use the ODBC API interface, 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 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 4.0 installation files. If you did not install the ODBC interface when you first installed Visual Basic 4.0, you need to do it now in order to continue the lesson. If you have already installed the ODBC interface, you can skip this section and move on to the section on how to define and register your own ODBC data sources.


NOTE:

You might also have other software packages that installed the ODBC interface on your system. Look for a program called ODBCADM.EXE. If you do not find this program, refer to the Visual Basic 4.0 install disks or CD to install the ODBC interface.


For your lesson today, you will only use the 16-bit version of the ODBC interface. This version works on both 16-bit and 32-bit systems. If you are using Window NT or Windows 95, be sure you are using the ODBCADM.EXE administrator.

The ODBC kit that ships with Visual Basic 4.0 contains drivers for SQL Server and the Microsoft Code Page Translator. Installing these drivers allows your Visual Basic 4.0 apps to access data stored in SQL Server databases. However, there are also drivers available for accessing desktop file formats such as dBASE, FoxPro, Microsoft Access, and Excel spreadsheets.

One of the best collections of ODBC drivers for desktop databases is included in Microsoft Office. If you have Microsoft Office, you probably already have these drivers on file. If not, you can run the SETUP.EXE program in the Microsoft Office SETUP directory to install the desktop ODBC drivers (see Figure 19.2).

Figure 19.2

Installing the desktop ODBC drivers with Microsoft Office.

If you do not have the Microsoft Office ODBC kit, you can still install the ODBC drivers that are shipped with Visual Basic 4.0. Run the SETUP.EXE program in the ODBC subdirectory of the main Visual Basic 4.0 directory. This will allow you to install the ODBC administrator and any drivers currently available (see Figure 19.3).


NOTE:

If you cannot find the ODBC subdirectory under the Visual Basic 4.0 main directory, make sure you are running the 16-bit version of Visual Basic 4.0. You must be running this project under the 16-bit version of Visual Basic 4.0 to make sure the API calls and the ODBC interface work properly.


Figure 19.3

Installing the ODBC drivers from Visual Basic 4.0.

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 4.0 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 using REGEDIT.EXE, we 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 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. As an illustration, create an ODBC data source that you can use later in this lesson.

First, load the ODBC Administrator program. To do this, locate and execute the ODBCADM.EXE program.


NOTE:

Throughout the lesson today, you will use the 16-bit version of the ODBC driver kit. There are slight differences between the 16-bit and 32-bit ODBC administrators. Even if you are running on a 32-bit operating system (NT or Windows 95), you will still be able to use the 16-bit ODBC administrator. After you learn how to use the 16-bit ODBC API, you can modify the API routines to use the ODBC32.DLL and access the 32-bit ODBC drivers installed on your machine.


When you first start the ODBC Administrator, you see a dialog box that lists all the data sources that are currently registered for your workstation (see Figure 19.4).

Figure 19.4

Viewing the registered ODBC data sources.

To define a new ODBC data source, click the Add button to bring up the Add Data Source dialog box. Select the Access 2.0 for Microsoft Office (*.MDB) driver and click the OK button. You then see the data entry dialog for creating a new ODBC data source (see Figure 19.5).

Figure 19.5

Adding a new data source.

Enter TYSODBC in the Data Source Name field and Test ODBC/MDB Interface in the Description field. The Data Source name is the string you will use when you call the ODBC connection from your Visual Basic 4.0 program. The description is just a comment to remind you of the contents of the data source.

Now click the Select Database command button to bring up a File dialog box. Locate and select the C:\TYSDBVB\CHAP19\TYSODBC.MDB database. This is the database that your program will connect to each time it calls the ODBC data source name TYSODBC. Your screen should now resemble the one in Figure 19.6.

Figure 19.6

The completed ODBC data source registration.

Click the OK button to store the new Data Source definition to the ODBC .INI file. You should now be able to see the TYSODBC data source in the list box in the first ODBC dialog form.

As a source of reference, the following code shows the entries in the ODBC.INI file that were created when you added the TYSODBC data source. Your entries might vary slightly.





 [TYSODBC]



Driver=C:\WINDOWS\SYSTEM\ODBCJT16.DLL



DBQ=C:\TYSDBVB\CHAP19\TYSODBC.MDB



DefaultDir=C:\ABC\CH19



Description=Test ODBC/MDB Interface



DriverId=25



FIL=MS Access;



JetIniPath=odbcddp.ini



UID=admin

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


TIP:

Review the ODBC Administrator online help file for more on how to configure 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 4.0 program that uses the ODBC interface to read and write data. In order to build your ODBC application, you will need to declare several Windows API (Application Programming Interface) calls. These calls, along with a handful of predefined constants are the heart of creating an ODBC capable database program. We won't review all the ODBC API calls in this chapter—only the essential ones you'll need to get your ODBC application working.


TIP:

Visual Basic 4.0 ships with an API viewer that lets you search for a particular API call and then copy and paste the information from the viewer directly to your Visual Basic 4.0 application. Two other files also ship with Visual Basic 4.0 and contain all the ODBC API declarations and constants. Search for the files ODBC16.TXT and ODBC32.TXT. These two files contain more than you'll ever want to see on ODBC APIs.


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 that are needed to execute ODBC commands from Visual Basic. After the low-level routines, you'll write a few mid-level functions that hide most of the grittier features of API programming. Finally, you'll create a few high-level routines that you can use from any Visual Basic data entry form to start off and maintain your ODBC connections.

ODBC API Crash Course

There are dozens of possible API calls for the ODBC interface. You can write calls that enable you to inspect the type of ODBC driver you are using, calls to inspect the various details of the data source (database name, format, and so on), calls to gather information about the data set (column names, data types for each field, length of each field, and so on), and calls to actually connect to the data source and move data to and from the ODBC data source. For this lesson, you will focus only on those routines that you need in order 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 that are required to connect to and share data with a registered ODBC data source. There are several preliminary steps involved before you can actually 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. Most of the preliminary work for establishing an ODBC connection involves creating the handles you will use throughout your program. Figure 19.7 shows these operations.

Figure 19.7

The preliminary steps to establish an ODBC data source connection.

When the connection is established, you can easily share data with the target data source using standard SQL statements. You can select a set of rows using the SELECT...FROM statement. Whenever you request a data set 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, in order to receive the data set, you must determine the number of columns to receive, and then 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 data set. Figure 19.8 illustrates the process of executing the SELECT statement and collecting the resulting data.

Figure 19.8

Collecting results of a SELECT query from an ODBC data source.

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

The last set of ODBC routines that 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 either 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 can also call an ODBC routine that returns the detailed error message generated by the data source. When you build your ODBC library, you'll 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'll declare only a subset of the total ODBC API calls. These are the ones that are essential for creating a basic data entry form. You also need a handful of Visual Basic constants that you'll use throughout the library.

Load the 16-bit Visual Basic 4.0 and start a new project. Add a .BAS module to the project and set its Name property to APIODBC. 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 APIODBC.BAS file in the C:\TYSDBVB directory. You can load this file into your project using File | Add File from the main menu.


Listing 19.1. The ODBC API declarations.





Option Explicit



'



' 16 bit ODBC Declares



Declare Function SQLAllocEnv Lib "odbc.dll" (env As Long) As Integer



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



Declare Function SQLAllocConnect Lib "odbc.dll" (ByVal env As Long,



hdbc As Long) As Integer



Declare Function SQLConnect Lib "odbc.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 "odbc.dll" (ByVal hdbc As Long) As Integer



Declare Function SQLDisconnect Lib "odbc.dll" (ByVal hdbc As Long) As Integer



Declare Function SQLAllocStmt Lib "odbc.dll" (ByVal hdbc As Long,



hstmt As Long) As Integer



Declare Function SQLFreeStmt Lib "odbc.dll" (ByVal hstmt As Long,



ByVal EndOption As Integer) As Integer



Declare Function SQLExecDirect Lib "odbc.dll" (ByVal hstmt As Long,



ByVal sqlString As String, ByVal sqlstrlen As Long) As Integer



Declare Function SQLNumResultCols Lib "odbc.dll" (ByVal hstmt As Long,



NumCols As Integer) As Integer



Declare Function SQLFetch Lib "odbc.dll" (ByVal hstmt As Long) As Integer



Declare Function SQLGetData Lib "odbc.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 "odbc.dll" (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

These are the ODBC API calls needed to implement basic connect, data transfer, and disconnect. Now add the constants in Listing 19.2 to the module.

Listing 19.2. The ODBC constant declarations.





' SQL/ODBC Constants



Global Const gSQLSuccess = 0



Global Const gSQLSuccessWithInfo = 1



Global Const gSQLError = -1



Global Const gSQLNoDataFound = 100



Global Const gSQLClose = 0



Global Const gSQLDrop = 1



Global Const gSQLMaxMsgLen = 512



Global Const gSQLChar = 1

Save the module as APIODBC.BAS, and save the project as TYSODBC.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. For example, if you want to use the 32-bit version of the ODBC, you only need to change the ODBC.DLL in each of the API calls to ODBC32.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 32-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'll use throughout the library. Add a new .BAS module to the project and set its Name property to LIBODBC. Now add the declarations in Listing 19.3 to the file.

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





Option Explicit



' Local ODBC Vars



Global Const ODBCBuffer = 256   ' for fixed length vars



Global gblHenv As Long          ' environment handle



Global gblHdbc As Long          ' database connection



Global gblHstmt As Long         ' statement handle



Global gblNumCols As Integer    ' result set columns



Global ODBCDataSource As String ' data source name



Global ODBCUserID As String     ' user id



Global ODBCPassword As String   ' user password



Global ODBCQuery As String      ' initial startup SQL

Now you're ready for the first set of Visual Basic routines.

Mid-Level Routines

These 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 ODBCEnvironment and add the code in Listing 19.4.

Listing 19.4. Coding the ODBCEnvironment function.





Function ODBCEnvironment(hEnv As Long)



    '



    ' establish an ODBC environment



    '



    ' inputs:



    '   hEnv    var to hold returned value



    '



    ' output:



    '   hEnv    set to unique handle value



    '



    ' returns:  gSQLSuccess if OK, oer errcode



    '



    Dim nResult As Integer



    Dim Temp As Integer



    '



    nResult = SQLAllocEnv(hEnv)



    If nResult <> gSQLSuccess Then



        MsgBox "Cannot allocate environment handle.", vbCritical,



"ODBCEnvironment"



        Screen.MousePointer = vbDefault



    End If



    ODBCEnvironment = nResult



    '



End Function

This routine calls the SQLAllocEnv API and checks for any errors. The SQLAllocEnv API establishes an environment for all ODBC transactions for this session. The hEnv parameter that you pass to the function is a variable of the LONG data type. This will hold a unique number that identifies all transactions that pass from your Visual Basic program to the ODBC interface.

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

Listing 19.5. Coding the ODBCConnect function.





Function ODBCConnect(hEnv, hdbc As Long, hstmt As Long, cDataSource As String,



cUserID As String, cPassword As String) As Integer



    '



    ' connect to remote data source



    '



    ' inputs:



    '   hEnv        environment handle from ODBCEnvironment



    '   hdbc        database connect var (will be set)



    '   hstmt       statement var (will be set)



    '   cDataSource name of ODBC data source



    '   cUserID     ODBC user login ID



    '   cPassword   ODBC login password



    '



    ' outputs:



    '   hdbc        database connection handle



    '   hstmt       statement handle



    '



    ' returns       gSQLSuccess or error code



    '



    Dim nResult As Integer



    Dim Temp As Integer



    '



    ODBCConnect = gSQLSuccess



    ' get a connection handle



    nResult = SQLAllocConnect(hEnv, hdbc)



    If nResult <> gSQLSuccess Then



        MsgBox "Unable to allocate connection handle.", vbCritical,



        "ODBCConnect.SQLAllocConnect"



        Screen.MousePointer = vbDefault



        ODBCConnect = nResult



        Exit Function



    End If



    ' now attempt to connect to database



    nResult = SQLConnect(hdbc, cDataSource, Len(cDataSource), cUserID,



    Len(cUserID), cPassword, Len(cPassword))



    If nResult <> gSQLSuccess And nResult <> gSQLSuccessWithInfo Then



        MsgBox "Unable to establish DataSource connnection.", vbCritical,



        "ODBCConnect.SQLConnect"



        Screen.MousePointer = vbDefault



        ODBCConnect = nResult



        Exit Function



    End If



    ' now get handle for all future statements



    nResult = SQLAllocStmt(hdbc, hstmt)



    If nResult <> gSQLSuccess Then



        MsgBox "Unable to allocate statement handle.", vbCritical,



        "ODBCConnect.AllocStmt"



        Screen.MousePointer = vbDefault



        ODBCConnect = nResult



        Exit Function



    End If



    '



End Function

The routine in Listing 19.5 takes several parameters and uses them to perform three basic ODBC operations. The first operation is establishing a data source connection handle. The second operation is the actual attempt to connect to the data source. The cDataSource, cUserID, and cPassword parameters are used for this. You'll see how to initialize these parameters later in this chapter. The final ODBC operation is to establish an ODBC statement handle. This handle will be used as the unique identifier whenever you want to share data with the ODBC data source.

You will also need to disconnect the ODBC link when you exit the program. Create a new function called ODBCDisconnect and add the code Listing 19.6.

Listing 19.6. Coding the ODBCDisconnect function.





Function ODBCDisconnect(hdbc As Long, hstmt As Long) As Integer



    '



    ' disconnect from data source



    '



    ' inputs:



    '   hdbc    database connection handle



    '   hstmt   statement handle



    '



    ' outputs:



    '   none



    '



    ' returns:  True if ok, False if error



    '



    Dim nResult As Integer



    '



    ODBCDisconnect = True



    '



    ' close statement handle



    If hstmt <> 0 Then



        nResult = SQLFreeStmt(hstmt, gSQLDrop)



        If nResult <> gSQLSuccess Then



            ODBCDisconnect = False



        End If



    End If



    '



    ' disconnect from ds



    If hdbc <> 0 Then



        nResult = SQLDisconnect(hdbc)



        If nResult <> gSQLSuccess Then



            ODBCDisconnect = False



        End If



    End If



    '



    ' close connection



    If hdbc <> 0 Then



        nResult = SQLFreeConnect(hdbc)



        If nResult <> gSQLSuccess Then



            ODBCDisconnect = False



        End If



    End If



    '



End Function

You can see that Listing 19.6 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'll need a routine to release the environment handle, too. Create the ODBCFreeHandle function and enter the code in Listing 19.7.

Listing 19.7. Coding the ODBCFreeHandle function.





Function ODBCFreeHandle(hEnv As Long) As Integer



    '



    ' release environment handle



    '



    ' inputs:



    '   hEnv    environment handle var



    '



    ' outputs:



    '   none



    '



    ' returns   True if OK, False if error



    '



    Dim nResult As Integer



    '



    ODBCFreeHandle = True



    '



    If hEnv <> 0 Then



        nResult = SQLFreeEnv(hEnv)



        If nResult <> gSQLSuccess Then



            ODBCFreeHandle = False



        End If



    End If



    '



End Function

This is a simple routine. It simply 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 will gather 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'll write the routine as a loop that continues to ask for error messages until there are none to be found.

Create a new function called ODBCErrMsg and enter the code in Listing 19.8.

Listing 19.8. Coding the ODBCErrorMsg function.





Sub ODBCErrorMsg(hdbc As Long, hstmt As Long, cTitle As String)



    '



    ' return detailed SQL Error



    '



    ' inputs:



    '   hdbc    database connection handle



    '   hstmt   statement handle



    '   cTitle  error message title



    '



    Dim SQLState As String * 16



    Dim ErrorMsg As String * gSQLMaxMsgLen



    Dim ErrMsgSize As Integer



    '



    Dim ErrorCode As Long



    Dim ErrorCodeStr As String



    Dim nResult As Integer



    Dim Temp As Integer



    '



    SQLState = String$(16, 0)



    ErrorMsg = String$(gSQLMaxMsgLen - 1, 0)



    '



    Do



    '



        nResult = SQLError(0, hdbc, hstmt, SQLState, ErrorCode, ErrorMsg,



        Len(ErrorMsg), ErrMsgSize)



        Screen.MousePointer = vbDefault



        If nResult = gSQLSuccess Or nResult = gSQLSuccessWithInfo Then



            If ErrMsgSize = 0 Then



                Temp = MsgBox("gSQLSuccess Or gSQLSuccessWithInfo Error



                -- No additional information available.",



                vbExclamation, cTitle)



            Else



                If ErrorCode = 0 Then



                    ErrorCodeStr = ""



                Else



                    ErrorCodeStr = Trim$(Str(ErrorCode)) & "   "



                End If



                Temp = MsgBox(ErrorCodeStr & Left$(ErrorMsg, ErrMsgSize),



                vbExclamation, cTitle)



            End If



        End If



        '



    Loop Until nResult <> gSQLSuccess



    '



End Sub

This routine 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 module as LIBODBC.BAS before you continue on 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 data sets returned by ODBC and store them in Visual Basic list and grid controls. These controls are then used as holding areas by your Visual Basic program for filling and updating text boxes 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 data sets. If your ODBC interface requires the passing of very large data sets, you need to develop more sophisticated methods for storing and retrieving the resulting data sets. However, it is always a good idea to limit the size of the result set as much as possible, 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'll build actually creates a data set for your Visual Basic program. This routine handles the creation of the environment handle, the completion of the ODBC connection to the data source, and the passing of the initial SQL SELECT statement that creates the data set.

Create a new function called ODBCDataSet and add the code in Listing 19.9.

Listing 19.9. Coding the ODBCDataSet function.





Function ODBCDataSet(frmName As Form) As Integer



    '



    ' get data from source



    '



    Dim nResult As Integer



    '



    ' declare an enviforment handle



    nResult = ODBCEnvironment(gblHenv)



    If nResult = gSQLSuccess Then



        ' connect to data source



        nResult = ODBCConnect(gblHenv, gblHdbc, gblHstmt, ODBCDataSource,



        ODBCUserID, ODBCPassword)



        If nResult = gSQLSuccess Then



            ' build data set for list box



            nResult = ODBCLoadCtl(frmName.LstODBC, ODBCQuery, gblHstmt,



            False, "*")



            ' build data set for grid control



            nResult = ODBCLoadCtl(frmName.GrdODBC, ODBCQuery, gblHstmt,



            False, "*")



        End If



    End If



    '



    ODBCDataSet = nResult



    '



End Function

The routine in Listing 19.9 expects you to pass it a form variable. This form must contain a list box and a grid control. These controls are filled with the records from the data set created by the SQL SELECT Statement. This routine calls a new function called ODBCLoadCtl that you have not yet defined. You'll get to that a bit later in this section.

Before you look at the ODBCLoadCtl function, you need to add the function that sets the initial data source name, user ID and password, and initial SQL statement. Create a new function called ODBCStart and enter the code in Listing 19.10.

Listing 19.10. Coding the ODBCStart function.





Function ODBCStart(frmName As Form, Optional cDSN, Optional cUser,



Optional cPW, Optional cSQL) As Integer



    '



    ' main wrapper to launch ODBC



    '



    ' inputs:



    '   frmName name of data entry form



    '   cDSN    data source name



    '   cUser   data source user login ID



    '   cPW     data source password



    '   cSQL    initial SELECT statement



    '



    ' check for passed parms



    If IsMissing(cDSN) Then



        cDSN = ""



    End If



    '



    If IsMissing(cUser) Then



        cUser = ""



    End If



    '



    If IsMissing(cPW) Then



        cPW = ""



    End If



    '



    If IsMissing(cSQL) Then



        cSQL = ""



    End If



    ' check for needed inputs



    If cDSN = "" Then



        cDSN = InputBox("Enter Data Set Name to Open:", "ODBC DataSource Name")



    End If



    '



    If cUser = "" Then



        cUser = InputBox("Enter UserID:", "ODBC UserID")



    End If



    '



    If cPW = "" Then



        cPW = InputBox("Enter Password:", "ODBC Password")



    End If



    '



    If cSQL = "" Then



        cSQL = InputBox("Enter Intitial SQL Statement", "ODBC SQL Statement")



    End If



    ' now load global vars



    ODBCDataSource = cDSN



    ODBCUserID = cUser



    ODBCPassword = cPW



    ODBCQuery = cSQL



    ' now try to connect and load



    ODBCStart = ODBCDataSet(frmName)



End Function

As you can see, this routine has one required parameter and four optional ones. You must pass the form that contains the list and grid controls. You have the option of omitting the data source and other parameters. If you leave them out, the routine prompts the user for the necessary values. If you include them, the routine just stores these passed values for later use.

Now you can build the heart of the high-level library routines. The ODBCLoadCtl routine reads each row and column of data in the data set returned by the ODBC data source and stores that data into two Visual Basic controls. In effect, you are creating your own set of bound data controls for the ODBC interface. After these two controls are filled, you'll write another routine to move data from the list control to a predefined set of text boxes for user input.

Create a new function called ODBCLoadCtl and enter the code in Listing 19.11.

Listing 19.11. Coding the ODBCLoadCtl function.





Function ODBCLoadCtl(ctlName As Control, cSQL As String, hstmt As Long,



lFill As Boolean, cDelim As String) As Integer



    '



    ' perform query and



    ' load results into control



    '



    ' inputs:



    '   CtlName     name of control to load



    '   cSQL        SQL query to perform



    '   hstmt       statement handle for SQL calls



    '   lFill       flag to pad info



    '   cDelim      char value that separates fields



    '



    ' outputs:



    '   CtlName     loaded with data



    '



    ' returns:      gSQLSucess if OK or error code



    '



    Dim nResult As Integer  ' error var



    Dim Temp As Integer     ' local stuff



    Dim nRows As Integer    ' row counter



    Dim nCols As Integer    ' column counter



    Dim cBuffer As String * ODBCBuffer   ' receive buffer



    Dim cItem As String     ' output buffer



    Dim cData As String     ' output line



    Dim cOutLen As Long     ' local counter



    Dim nColWide As Integer ' width of grid column



    '



    ODBCLoadCtl = gSQLSuccess



    nColWide = 1500



    '



    ' Make sure referenced control is a list box, combo box, or grid



    If TypeOf ctlName Is ListBox Then



        ElseIf TypeOf ctlName Is ComboBox Then



        ElseIf TypeOf ctlName Is Grid Then



    Else



        ODBCLoadCtl = -3



        Exit Function



    End If



    ' Do the initial query



    nResult = SQLExecDirect(hstmt, cSQL, Len(cSQL))



    If nResult <> gSQLSuccess Then



        Call ODBCErrorMsg(gblHdbc, gblHstmt,



        "SQL Statement Error During ODBCLoadCtl")



        Temp = SQLFreeStmt(hstmt, gSQLClose)



        Screen.MousePointer = vbDefault



        ODBCLoadCtl = nResult



        Exit Function



    End If



    ' get column count



    nResult = SQLNumResultCols(hstmt, gblNumCols)



    If nResult <> gSQLSuccess Then



        Temp = SQLFreeStmt(hstmt, gSQLClose)



        Screen.MousePointer = vbDefault



        ODBCLoadCtl = nResult



        Exit Function



     End If



    ' must not have found data!



    If gblNumCols = 0 Then



        Temp = SQLFreeStmt(hstmt, gSQLClose)



        Screen.MousePointer = vbDefault



        ODBCLoadCtl = gSQLNoDataFound



        Exit Function



    End If



    ' initialize grid



    If TypeOf ctlName Is Grid Then



        ctlName.Cols = gblNumCols + 1



        ctlName.Rows = 2



    End If



    ' clear list



    If TypeOf ctlName Is ListBox Then



        ctlName.Clear



    End If



    ' clear combo box



    If TypeOf ctlName Is ComboBox Then



        ctlName.Clear



    End If



    ' initialize receive buffer



    cBuffer = String$(ODBCBuffer, 0)



    'Now load get rows and put into control



    nRows = 0



    Do



        nResult = SQLFetch(hstmt)    ' get a row



        If nResult <> gSQLSuccess Then



            If nResult = gSQLNoDataFound Then



                Temp = SQLFreeStmt(hstmt, gSQLClose)



                Screen.MousePointer = vbDefault



                If nRows > 0 Then



                    Exit Do         ' we're all done



                Else



                    ODBCLoadCtl = nResult    ' error!



                    Exit Function



                End If



            Else



                Temp = SQLFreeStmt(hstmt, gSQLClose)



                Screen.MousePointer = vbDefault



                ODBCLoadCtl = nResult



                Exit Function



            End If



        End If



        '



        ' update grid row count



        nRows = nRows + 1



        If TypeOf ctlName Is Grid Then



            ctlName.Row = nRows



        End If



        '



        ' now get each column



        cItem = ""



        cData = ""



        For nCols = 1 To gblNumCols



            nResult = SQLGetData(hstmt, nCols, gSQLChar, cBuffer,



            ODBCBuffer, cOutLen)



            If nResult <> gSQLSuccess Then



                Temp = SQLFreeStmt(hstmt, gSQLClose)



                Screen.MousePointer = vbDefault



                ODBCLoadCtl = nResult



                Exit Function



            End If



            '



            If TypeOf ctlName Is Grid Then



                ' load grid column



                ctlName.Col = nCols



                If cOutLen > 0 Then



                    ctlName.Text = Left$(cBuffer, cOutLen)



                    ctlName.ColWidth(nCols) = nColWide



                End If



            Else



                ' build single string for list/combo box



                If lFill And nCols = 1 Then



                    If cOutLen > 0 Then



                        cData = Left$(cBuffer, cOutLen)



                    Else



                        cData = ""



                    End If



                Else



                    If cOutLen > 0 Then



                        If cItem = "" Then



                            cItem = Left$(cBuffer, cOutLen)



                        Else



                            cItem = cItem & cDelim & Left$(cBuffer, cOutLen)



                        End If



                    Else



                        cItem = cItem & cDelim



                    End If



                End If



            End If



        Next nCols



        '



        ' now move string data into



        ' list/combo control.



        If cItem <> "" Then



            On Error Resume Next



            ctlName.AddItem cItem



            If Err = 0 Then



                If cData <> "" Then



                    ctlName.ItemData(ctlName.NewIndex) = Val(cData)



                End If



            Else



                MsgBox "Result Set too large to fit in control",



                vbExclamation, "ODBCLoadCtl"



                Temp = SQLFreeStmt(hstmt, gSQLClose)



                Exit Do



            End If



            On Error GoTo 0



        End If



        ' increment grid row



        If TypeOf ctlName Is Grid Then



            ctlName.Rows = ctlName.Rows + 1



        End If



    Loop



    ' fix final grid row count



    If TypeOf ctlName Is Grid Then



        ctlName.Rows = ctlName.Rows - 1



    End If



    '



    ODBCLoadCtl = gSQLSuccess



    Screen.MousePointer = vbDefault



    '



End Function

This routine does a number of things. First, you pass it several parameters that are used to create the data set and a few to control how the Visual Basic controls are loaded. You wrote the routine to be used with more than one type of data control, so there are several lines of code that check and verify the type of control you are dealing with.

Because Listing 19.11 is a rather long routine, let's break the major sections down and inspect the operations that are taking place here. The main operations of this routine are as follows:

Now that you have loaded the list and grid controls, you need a routine that moves the requested record from the list control into a set of text boxes for user input on the form. Create the new function called ODBCGetFld, and enter the code in Listing 19.12.

Listing 19.12. Coding the ODBCGetFld function.





Function ODBCGetFld(ctlName As Control, nFld As Integer, cFldDelim As String)



As String



    '



    ' get info from list/combo control



    ' into text control for editing



    '



    ' inputs:



    '   ctlName     control that has data set



    '   nFld number of field to retrieve



    '   cFldDelim   field delimeter



    '



    ' outputs:



    '   none



    '



    ' returns:      resulting column of data



    '



    Dim x As Integer



    Dim nPos1 As Integer



    Dim nPos2 As Integer



    Dim cSearch As String



    '



    ODBCGetFld = ""



    cSearch = cFldDelim & ctlName.List(ctlName.ListIndex) & cFldDelim



    '



    ' make sure we have the right control



    If TypeOf ctlName Is ListBox Then



    Else



        If TypeOf ctlName Is ComboBox Then



        Else



            Exit Function



        End If



    End If



    ' look for first delimiter for field nfld



    nPos1 = 0



    For x = 1 To nFld



        nPos1 = InStr(nPos1 + 1, cSearch, cFldDelim)



        If nPos1 = 0 Then



            nPos1 = -1



            Exit For



        End If



    Next x



    '



    ' get second delimeter for nFld



    If nPos1 <> -1 Then



        nPos2 = InStr(nPos1 + 1, cSearch, cFldDelim)



    End If



    '



    ' ok, we got a column of data!



    If nPos2 > nPos1 And nPos2 <> 0 Then



        ODBCGetFld = Mid$(cSearch, nPos1 + 1, nPos2 - (nPos1 + 1))



    End If



    '



End Function

The routine in Listing 19.12 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'll see how to use this in your data entry forms in the next section.

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 data set. 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 ODBCRowDel function and enter the code in Listing 19.13.

Listing 19.13. Coding the ODBCRowDel function.





Public Function ODBCRowDel(frmName As Form, cTable As String, cKey As String)



As Integer



    '



    '  inputs:



    '     frmName   form that holds the controls



    '     cTable    table name of data



    '     cKey      index key for table



    ' 



    ' outputs:



    '     modifies data set



    ' 



    ' returns:  <>0 if an error occurs



    '     ' delete a row



    '



    Dim nResult As Integer



    Dim Temp As Integer



    Dim cSQL As String



    '



    ODBCRowDel = gSQLSuccess



    ' create delete query



    cSQL = "DELETE * FROM " + cTable + " WHERE " + cKey + "='" +



    Trim(frmODBC.Text1(0)) + "'"



    ' Do the delete query:



    nResult = SQLExecDirect(gblHstmt, cSQL, Len(cSQL))



    If nResult <> gSQLSuccess Then



        Call ODBCErrorMsg(gblHdbc, gblHstmt, "SQL Statement Error



        During ODBCRowDel")



        Temp = SQLFreeStmt(gblHstmt, gSQLClose)



        Screen.MousePointer = vbDefault



        ODBCRowDel = nResult



        Exit Function



    End If



    ' set values and exit



    ODBCRowDel = gSQLSuccess



    Screen.MousePointer = vbDefault



    '



End Function

This routine is designed to delete the current record loaded into the text controls on the form, and it requires three parameters. The first is the name of the data entry form, the second is the name of the table you are updating, and the third parameter is the name of the key field. For all your ODBC data sets, 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 data set can be a limitation when you're dealing with secondary tables and other non-normalized data sets. For now, however, this 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.13 builds a standard DELETE query using the parameters you supplied it, and then executes the SQL DELETE returning any error messages that might result.

Now you'll build the ODBCRowAdd function. The add routine requires the table name and the form name as parameters. The routine builds a standard APPEND query using the INSERT INTO syntax. Create the new function and add the code in Listing 19.14.

Listing 19.14. Coding the ODBCRowAdd function.





Public Function ODBCRowAdd(frmName As Form, cTable As String) As Integer



    '



    ' add a new row to the table



    '



    '  inputs: 



    '    frnName   form that holds the controls



    '    cTable    table name for data



    ' 



    ' outputs:  modifies data set



    '



    ' returns:  <>0 if error occurs



    '



    Dim nResult As Integer



    Dim Temp As Integer



    Dim cSQL As String



    Dim x As Integer



    '



    ODBCRowAdd = gSQLSuccess



    '



    ' create SQL insert query



    cSQL = "INSERT INTO " + cTable + " VALUES("



    For x = 1 To gblNumCols



        cSQL = cSQL + "'" + frmName.Text1(x - 1) + "'"



        If x < gblNumCols Then



            cSQL = cSQL + ","



        End If



    Next x



    cSQL = cSQL + ")"



    '



    ' Do the insert query:



    nResult = SQLExecDirect(gblHstmt, cSQL, Len(cSQL))



    If nResult <> gSQLSuccess Then



        Call ODBCErrorMsg(gblHdbc, gblHstmt, "SQL Statement Error



        During ODBCRowAdd")



        Temp = SQLFreeStmt(gblHstmt, gSQLClose)



        Screen.MousePointer = vbDefault



        ODBCRowAdd = nResult



        Exit Function



    End If



    '



    ' set values and exit



    ODBCRowAdd = gSQLSuccess



    Screen.MousePointer = vbDefault



    '



End Function

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'll use the DELETE/INSERT method because it takes less code and is easier to understand.


NOTE:

In certain situations, you will not want to perform updates 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 you might define delete cascades, you should only use the UDPATE method.


Create the ODBCRowUdpate function and add the code in Listing 19.15.

Listing 19.15. Coding the ODBCRowUpdate function.





Function ODBCRowUpdate(frmName As Form, cTable As String,



cKey As String) As Integer



    '



    ' update row of data set



    '



    ' inputs: 



    '    frmName   for that holds controls



    '    cTable    name of data table



    '    cKey      index key of table



    '



    ' outpus:       modifies data set



    '



    '  returns      <>0 if error occurs



    '    Dim nResult As Integer



    Dim Temp As Integer



    '



    Dim cSQLInsert As String



    Dim cSQLDelete As String



    Dim x As Integer



    ODBCRowUpdate = gSQLSuccess



    ' create delete query



    cSQLDelete = "DELETE * FROM " + cTable + " WHERE " + cKey + "='" +



    Trim(frmName.Text1(0)) + "'"



    ' create SQL update query



    cSQLInsert = "INSERT INTO " + cTable + " VALUES("



    For x = 1 To gblNumCols



        cSQLInsert = cSQLInsert + "'" + frmName.Text1(x - 1) + "'"



        If x < gblNumCols Then



            cSQLInsert = cSQLInsert + ","



        End If



    Next x



    cSQLInsert = cSQLInsert + ")"



    ' do delete query



    nResult = SQLExecDirect(gblHstmt, cSQLDelete, Len(cSQLDelete))



    If nResult <> gSQLSuccess Then



         Call ODBCErrorMsg(gblHdbc, gblHstmt, "SQL Statement Error During0



         ODBCRowUpdate.Delete")



        Temp = SQLFreeStmt(gblHstmt, gSQLClose)



        Screen.MousePointer = vbDefault



        ODBCRowUpdate = nResult



        Exit Function



    End If



    ' Do the insert query:



    nResult = SQLExecDirect(gblHstmt, cSQLInsert, Len(cSQLInsert))



    If nResult <> gSQLSuccess Then



        Call ODBCErrorMsg(gblHdbc, gblHstmt, "SQL Statement Error During



        ODBCRowUpdate.Insert")



        Temp = SQLFreeStmt(gblHstmt, gSQLClose)



        Screen.MousePointer = vbDefault



        ODBCRowUpdate = nResult



        Exit Function



    End If



    ' set values and exit



    ODBCRowUpdate = gSQLSuccess



    Screen.MousePointer = vbDefault



    '



End Function

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

Save this module as LIBODBC.BAS. You have now completed the ODBC library routines. The next step is to build a simple data entry form that uses the ODBC library to open a data set 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'll build a form that is self-configuring. This form reads the number of fields in the data set and presents the correct number of text boxes for the data set. The form also displays a grid that shows the table view of the same data presented in the text boxes. Users are able to click on the grid line to bring up the data record in the text boxes. You'll also have the usual set of navigation buttons (First, Last, Next, and Back) and data table modification buttons (Add, Update, Delete, and Refresh).

You'll write the form in a way that lets you easily modify it for future Visual Basic projects. In fact, this form can be used with different data sets without any additional modification because it is able to read the data set columns and "construct itself" to create a simple data entry form.

Building the Dynamic Data Entry Form

Because you plan to make this form self-constructing, you have very little to do in the way of form layout. You need to add a handful of controls (some of them are control arrays), and then add some code to make sure the form can make its own decisions on how the controls should appear on the screen. You need five controls and one form for this project.

Start Visual Basic and create a new project. Set the Name and the Caption properties of the form to FRMODBC. Add a command button control to the form. Set its Name property to cmdExit and its Caption property to E&xit. Add a grid control to the form (not the data-bound grid, just the standard grid control). Set its Name property to grdODBC. Place a list control on the form and set its Name property to LSTODBC. Also set the list control's Visible property to False.

Now you need to add two control arrays to the form. First, add a single command button to the form. Set its Name and caption properties to cmdODBC. Also set its Font properties to Microsoft Sans Serif, eight point regular. Use the Copy and Paste operations from the Visual Basic Edit menu to add seven more cmdODBC buttons to the form. You should now have a total of eight command buttons called cmdODBC.


NOTE:

All forms and controls in this project should have their Font properties set to Microsoft Sans Serif, eight point, Regular.


The second control array that you need is a set of text boxes. Add a single text box to the form. Set its Name and Caption properties to txtODBC. Make sure the Font properties are set to Microsoft Sans Serif, eight point, non-Bold. Use Copy and Paste to add 11 more text boxes to the form. You should now have a total of 12 text boxes called txtODBC.

The placement of all of these controls does not matter; you'll add code to the form to make sure all controls are sized and placed properly. However, as a reference, your form should look similar to the one in Figure 19.9.

Figure 19.9

Adding controls to the ODBC form.

Save this form as FRMODBC.FRM and the project as CH19.VBP. Remember that this project should also contain the APIODBC.BAS and the LIBODBC.BAS modules, too.

Now you're ready to add the code to the form.

Coding the ODBC Data Entry Form

You need to add code to the form to make sure all the controls respond accordingly and that the form builds itself at startup time. We'll save the form-building code for the end of this section. First, let's add some of the basic stuff. Add the form-level variables in Listing 19.16 to the declaration section of the form. You'll use these variables throughout the form.

Listing 19.16. Coding the form-level variables.





'



' form-level vars



'



Dim cDSN As String          ' data source



Dim cUser As String         ' userid



Dim cPW As String           ' password



Dim cRefresh As String      ' select query



Dim cTable As String        ' base table name



Dim cKeyFld As String       ' key field name



Dim cFormTitle As String    ' title for data form

Add the code in Listing 19.17 to the Form_Load event. This is where you set the form-level variables and then begin the process of connecting to the ODBC data source and formatting the data form.

Listing 19.17. Coding the Form_Load event.





Private Sub Form_Load()



    '



    Dim nResult As Integer  ' for errors



    '



    ' set form-level vars



    cDSN = "TYSODBC"         ' ODBC data source



    cUser = "Admin"         ' login ID



    cPW = " "               ' empty password



    cTable = "Master"       ' default base table



    cKeyFld = "CustID"      ' default key field



    cRefresh = "SELECT * FROM Master ORDER BY CustID"   ' default SQL



    cFormTitle = "ODBC/Access Interface Demo"   ' form title



    ' attempt to connect



    nResult = ODBCStart(Me, cDSN, cUser, cPW, cRefresh)



    ' check results



    If nResult <> gSQLSuccess Then



        Unload Me               ' exit



        End                     ' end program



    Else



        FixForm                 ' fix up form



        lstODBC.ListIndex = 0   ' point to first rec



    End If



    ' center the form



    Me.Left = (Screen.Width - Me.Width) / 2



    Me.Top = (Screen.Height - Me.Height) / 2



End Sub

In this routine, you first initialize the variables you need for the ODBC data connection (cDSN, cUser, cPW, cRefresh), and then you initialize two more variables needed to make the ODBCRowUpdate and ODBCRowDel functions work.


WARNING:

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 will not be able to run this program with these variables.


In the next part of Listing 19.17, you attempt the ODBC connection (ODBCStart). If an error occurs, you close the form and end the program; otherwise, you execute the routine to format the data entry form (FixForm), position the record pointer to the first record in the data set, and finally center the form on the screen.

Now let's add the code for the Form_Unload event. In Listing 19.18, you'll execute the ODBCDisconnect and ODBCFreeHandle routines.

Listing 19.18. Coding the Form_Unload event.





Private Sub Form_Unload(Cancel As Integer)



    '



    ' close out safely



    '



    Dim nResult As Integer



    '



    nResult = ODBCDisconnect(gblHdbc, gblHstmt)



    nResult = ODBCFreeHandle(gblHenv)



    '



End Sub

Now add the code in Listing 19.19 behind the cmdExit_Click event. This starts the Form_Unload routine.

Listing 19.19. Coding the cmdExit_Click event.





Private Sub cmdExit_Click()



    Unload Me   ' exit this form



End Sub

You also need code that will transfer a line of data from the list control into the array of text controls. This is your own version of moving data from a Snapshot-object to a set of data-bound controls. Add the code in Listing 19.20 to the lstODBC_Click event.

Listing 19.20. Coding the lstODBC_Click event.





Private Sub lstODBC_Click()



    '



    ' load text controls from listbox



    '



    Dim x As Integer



    '



    For x = 1 To gblNumCols



        txtODBC(x - 1).Text = ODBCGetFld(lstODBC, x, "*")



    Next



End Sub

Here, you simulate the click of the list by moving the pointer of the list box. You want your data form to have a grid that the user can browse through and select a record from. The code line in Listing 19.21 fires off a list click each time the user clicks on a grid line. Add it to the grdODBC_RowColChange event.

Listing 19.21. Coding the grdODBC_RowColChange event.





Private Sub grdODBC_RowColChange()



    '



    ' when user clicks row, update other controls



    '



    lstODBC.ListIndex = grdODBC.Row - 1



End Sub

You need to add the code that handles all the user actions behind the command button array. This eight-button array handles the navigation chores (First, Last, Next, and Back) and the record modification chores (Add, Update, Delete, and Refresh). Listing 19.22 is similar to the code you wrote for the LIBRECS.BAS code library last week. This time, you keep the code a bit more basic so that you can focus on the ODBC aspects of the project. If you plan to use this ODBC code library in production applications, you might want to spruce up this code section with some error checking and other extras.

Listing 19.22. Coding the cmdODBC_Click event.





Private Sub cmdODBC_Click(Index As Integer)



    '



    ' handle user clicks



    '



    Dim nResult As Integer



    Dim x As Integer



    '



    Select Case Index



        Case Is = 0



            ' first rec



            nResult = ODBCDataSet(Me)



            lstODBC.ListIndex = 0



        Case Is = 1



            ' next rec



            If lstODBC.ListIndex < (lstODBC.ListCount - 1) Then



                lstODBC.ListIndex = lstODBC.ListIndex + 1



            End If



        Case Is = 2



            ' previous rec



            If lstODBC.ListIndex > 0 Then



                lstODBC.ListIndex = lstODBC.ListIndex - 1



            End If



        Case Is = 3



            ' last rec



            nResult = ODBCDataSet(Me)



            lstODBC.ListIndex = lstODBC.ListCount - 1



        Case Is = 4



            ' new rec



            If cmdODBC(Index).Caption = "&Add" Then



                nResult = MsgBox("Add a New Record?", vbInformation +



                vbYesNo, "Add Record")



                If nResult = vbYes Then



                    For x = 0 To gblNumCols - 1



                        txtODBC(x) = ""



                    Next x



                    cmdODBC(Index).Caption = "&Save"



                    txtODBC(0).SetFocus



                End If



            Else



                nResult = ODBCRowAdd(Me, cTable)



                cmdODBC(Index).Caption = "&Add"



                nResult = ODBCDataSet(Me)



                lstODBC.ListIndex = 0



            End If



        Case Is = 5



            ' update rec



            nResult = ODBCRowUpdate(Me, cTable, cKeyFld)



            nResult = ODBCDataSet(Me)



            lstODBC.ListIndex = 0



        Case Is = 6



            ' delete rec



            nResult = MsgBox("Delete the Current Record?", vbInformation +



            vbYesNo, "Delete Record")



            If nResult = vbYes Then



                nResult = ODBCRowDel(Me, cTable, cKeyFld)



                nResult = ODBCDataSet(Me)



                lstODBC.ListIndex = 0



            End If



        Case Is = 7



            ' refresh set



            nResult = ODBCDataSet(Me)



            lstODBC.ListIndex = 0



    End Select



    '



End Sub

Notice that you added confirmation messages for the Add and Delete buttons. Notice also that you made the Add button play a dual role. When you're in standard mode, the button displays the Add characteristics. When the Add mode is invoked, the same button turns into the Save button. Although you did not do it here, it's a good idea to disable all the other buttons during the add process. See the code for the LIBRECS.BAS routines (on Day 11, "Creating Database Programs with Visual Basic Code") to get an example of how to implement such a feature.

Now for the last bit of code for this chapter. The FixForm routine gets information from the data set and sizes and positions all the command buttons, text boxes, grids, and exit button. Finally, this routine makes sure the data form itself is the proper size to hold all the controls.

Create a new Sub procedure called FixForm and end the code using Listing 19.23.

Listing 19.23. Coding the FixForm routine.





Sub FixForm()



    '



    ' position buttons, text boxes and grid



    '



    Dim nWidth As Integer



    Dim x As Integer



    ' minimum form width



    If Me.Width < 6000 Then



        Me.Width = 6000



    End If



    Me.Caption = cFormTitle ' set form title



    ' command buttons



    nWidth = (Me.Width - 360) / 8



    cmdODBC(0).Caption = "&First"



    cmdODBC(1).Caption = "&Next"



    cmdODBC(2).Caption = "&Back"



    cmdODBC(3).Caption = "&Last"



    cmdODBC(4).Caption = "&Add"



    cmdODBC(5).Caption = "&Update"



    cmdODBC(6).Caption = "&Delete"



    cmdODBC(7).Caption = "&Refresh"



    '



    For x = 0 To 7



        cmdODBC(x).Width = nWidth



        cmdODBC(x).Left = x * nWidth + 120



        cmdODBC(x).Height = 300



        cmdODBC(x).Top = 120



    Next x



    ' text boxes



    For x = 1 To gblNumCols



        txtODBC(x - 1).Left = 120



        txtODBC(x - 1).Top = (360 * x) + 240



        txtODBC(x - 1).Width = 3600



        txtODBC(x - 1).Visible = True



    Next x



    '



    ' grid



    grdODBC.Left = 120



    grdODBC.Width = Me.Width - 360



    grdODBC.Top = (360 * x) + 360



    grdODBC.Height = 2400



    '



    ' other text boxes



    For x = gblNumCols + 1 To 12



        txtODBC(x - 1).Visible = False



    Next x



    ' adjust form length



    Me.Height = grdODBC.Top + grdODBC.Height + 960



    ' position exit button



    cmdExit.Width = 1200



    cmdExit.Height = 300



    cmdExit.Left = Me.Width - (240 + cmdExit.Width)



    cmdExit.Top = Me.Height - (480 + cmdExit.Height)



    '



End Sub

The routine in Listing 19.23 first sets a minimum width for the form and then sets the form title. Then the command button captions and locations are set. Next, the routine sets the number of text boxes needed to show the data from the associated ODBC data set. After the grid is sized and located, the routine makes any remaining text boxes invisible. Then, after adjusting the form height (based on the number and location of input controls), the exit button is sized and placed at the lower right corner of the form.

Save the project. You are now ready to run the ODBC data entry form.

Running the ODBC Data Entry Form

Now that both the library and the form routines have been completed, you are ready to run the program. When you first run the program, you'll see the data entry form appear (see Figure 19.10).

Figure 19.10. Running the ODBC data entry form.

Notice that all the proper text boxes appear in order on the form and that the grid and Exit button are properly positioned. This is all handled by the FixForm routine.

You can now use this screen to walk through the data set by using the command buttons (First, Last, Next, and Back) or by clicking the desired row on the grid at the bottom of the form. You can also add, edit, and delete records in the data set using the appropriate buttons. The Refresh button will requery the ODBC data source to get updated information.

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

Other ODBC Considerations

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

Summary

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

You also learned how to install the ODBC interface on your workstation and how to use the ODBC Administrator program to install ODBC driver sets and 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 allows the user the 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 4.0 as you do for the 32-bit version of Visual Basic 4.0?
  7. Name the four preliminary steps you must complete before you can pass an SQL SELECT statement to the newly opened ODBC data source.

Exercises

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 entry program runs on 16-bit and 32-bit workstations throughout the headquarters building. The data is currently 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 should also 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 2.0 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 allow users to review and update data in the spreadsheet.