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.
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.
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.
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.
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.
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.
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.
` ` 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 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 |
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.
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.
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.
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.
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.
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.
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:
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.
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.
