Visual Basic Expert Solutions

book coverQUE

Chapter 6

Working with ODBC

By James A. Dooley


In some ways, building a client/server application can be very similar to building a desktop application: most of the user interface development is the same, database design has to be done, and so on. Differences start to crop up when you consider the data access component of the application, the data is physically located on another machine, and, as a result, it will require a different data access strategy than that of a desktop application. The key to developing a successful client/server application is to recognize that there are two cooperative processes, the client and the server, involved in the delivery of a such an application. Accordingly, you design your application to take advantage of both features.

This chapter adopts a hands-on approach to working with ODBC. Where examples are used to demonstrate a point, they are done in the context of moving the now famous Northwind Traders database from MS Access to client/server using Visual Basic and MS SQL Server. In designing our solutions, we will aim to provide a reasonable performance on an overloaded network. We do this because more often than not, this is the type of environment that companies use to take a look at this "client/server stuff."

This chapter will cover the following information:

After completing this chapter, you will be able to design and implement client/server applications using Visual Basic and a Database Management System (DBMS) that supports an ODBC connection.

Client/Server Application Design

Broadly speaking, most business applications consist of three layers, as depicted in figure 6.1.

Fig. 6.1 The three application layers.

While most applications consist of these three layers, the architecture used to implement them can vary greatly ranging from a dumb terminal to a complex cooperative processing system. Figure 6.2 has been adopted from work done by the Gartner Group and depicts a wide range of architectures used to implement this three-layered model. In this figure, H represents a host system, and W represents a workstation or PC.

Fig. 6.2 This application architecture depicts a wide range of architectures.

On the left of this diagram, you see what might be described as a typical mainframe application, with the host managing the data, logic, and presentation. On the extreme right, we have a cooperative processing application where the work station is responsible for the presentation and also contains some for the processing logic or business rules. Between these two extremes we have various systems that represent transitions from one to the other. The first, titled UI, recognizes that the power of the workstation can be used to manage the user interface. C/S I is what is commonly termed client/server today. The workstation carries out the presentation and logic, and the back-end, or server, carries out data management. C/S II is almost a reversal of C/S I, whereby the logic and data is located on the server, and the workstation does the presentation and some light processing. What distinguishes C/S II from UI is the light processing being done on the workstation, such as calculating totals.

Among the key issues facing the designer of a client/server application is deciding how to split the processing and how to locate the data. The basic idea is for the Client and the Server to do the work that they are best suited to do. Thus, the client should concentrate on the user interface, whereas the server handles the data integrity, transaction management, and so on. We'll come back to this issue later, in the section entitled "Using Remote data", as we consider how to implement various aspects of the Northwind Traders System.

Understanding ODBC

Over the years, corporations have invested in diverse platforms for strategic, technical, financial, and-dare we say-political reasons. Thus, we find in most cases a combination of mainframes, mid-range, and PC systems are being used to manage the corporation. Each system brings it's own method of data storage, ranging from VSAM files to hierarchical and relational databases. These systems store critical data for their corporations, and they can't be tossed aside in the race to client/server. Instead, they must be integrated into the new environment. But how? Each platform provides its own means of data access, and thus to integrate them would require considerable effort on our part.

This was the challenge that Microsoft set out to solve with it's Open Database Connectivity (ODBC) standard. In developing ODBC, Microsoft wanted to provide a single Application Programming Interface (API) that could be used to access data on a variety of database management systems. Two of the main advantages of using ODBC are as follows:

The documentation on ODBC runs to some 800 pages, most of which you need not concern yourself with as a Visual Basic developer. However, you should understand the basic principles of ODBC because it is the critical element enabling you to use Visual Basic for developing client/server applications.

ODBC Architecture

The architect consists of four major components, as shown in figure 6.3.

Fig. 6.3 ODBC architecture consists of these four major components.

Conformity Levels

Unfortunately, the ODBC standard is not as straightforward as we would like. Instead of providing a single standard for drivers, it classifies drivers conformity along two lines, their support for the ODBC API and their support for the SQL standard. An understanding of this issue is critical because it determines whether you can use a particular driver with your Visual Basic application.

API Conformance Levels

For API purposes, drivers are classified into three categories: Core, Level 1, and Level 2. Categories are incremental; thus, Level 1 supports Core plus Level 1, and Level 2 supports Core plus Level 1 and Level 2. As the name suggests, Core provides a very basic level of functionality, such as the capability to do the following:

In addition to the previous items, a Level 1 driver provides the following:

A Level 2 driver represents a top of the range ODBC driver. In addition to the previous items, it can do the following:

Note: Most applications that provide access to data via ODBC require a driver that supports the full Level 1 API.

SQL Conformity

As with API conformity, ODBC defines three levels for SQL conformity: Minimum, Core, and Extended. The differences between these three are defined in terms of the Data Definition Language (DDL), the Data Manipulation Language (DML), expressions, and data types they support. Table 6.1 highlights the differences.

Table 6.1 Table 6.1 SQL Conformity

Single Tier and Multi-Tier Drivers

In addition to classifying drivers based on their conformity to the ODBC standard, you can define drivers as single-tier or as multi-tier.

Configuring ODBC

Having seen an overview of ODBC in the previous section, we'll look at how ODBC is implemented and how to configure it for use with our applications.

ODBC is implemented as a series of executable files, Dynamic Link Libraries (DLLs) and INI files, as follows:

Before we can access a database via ODBC, we must define the Data Source so that the ODBC knows how to establish the connection for us when we make the request. In our case, you can do this in the following two ways:

Regardless of which method you use, you'll need to know several things about the database before you can define the Data Source. The exact information required depends on the ODBC driver, so you'll need to refer to the vendor's documentation. In our case, because we're working with MS SQL Server, we need to know the following:

TB7 The name of the database.

In the following two subsections, we'll examine how to define the Data Source required to access the Northwind database. For our examples, the information required to define the Data Source is as follows:

Using the ODBC Administrator

To define the Data Source using the ODBC Administrator do the following:

1. Launch the ODBC Administrator applet by double-clicking it from the Control Panel.

2. From the ODBC Administrator main window (see fig. 6.4) click Add to add the new Data Source.

Fig. 6.4 The ODBC Administrator is used to add data sources.

1. The ODBC manager provides you with the list of installed drivers, which you can use to connect to various databases (see fig. 6.5). Because we want to connect to MS SQL Server, select SQL Server and click OK.

Fig. 6.5 A list of installed drivers.

1. You are now asked to provide driver-specific information via the dialog box shown in figure 6.6. Complete this dialog box as shown and click OK to complete the task.

Fig. 6.6 Specify your ODBC driver information here.

After completing the task, you return to the main window, where you can see your Data Source displayed in the list box (see fig. 6.7).

Fig. 6.7 The SQL Server Data Source is defined in the ODBC Administrator main window.

Using the RegisterDatabase Function

Visual Basic provides the RegisterDatabase method so that developers can programmatically define Data Sources. The RegisterDatabase method takes the following four parameters:

The easiest way to determine all the values for the various parameters is to define the Data Source first using the ODBC Administrator and then examine the ODBC.INI file. Examining the INI file for our NWIND Data Source, you'll see the following entries:

[NWIND]
Driver=C:\WIN31\SYSTEM\sqlsrvr.dll
Description=Northwind Database
Server=QUE_1
FastConnectOption=No
UseProcForPrepare=Yes
Database=Northwind
OEMTOANSI=No

From this, you can see that the Data Source name is NWIND, the driver is C:\WIN31\SYSTEM\sqlsrvr.dll, and that the remaining attributes are Description, Server, FastConnectOption, UseProcForPrepare, Database, and OEMTOANSI. Having obtained this information, you are now ready to create the code required to define the Data Source (see Listing 6.1).

Listing 6.1 REGDB.BAS Registering the Database

Private Sub cmdRegDatabase_Click()
Dim Attribs As String
' Build keywords string.
Attribs = "Description=Northwind Database" & Chr$(13)
Attribs = Attribs & "Server=QUE_1" & Chr$(13)
Attribs = Attribs & "FastConnectOption=No" & Chr$(13)
Attribs = Attribs & "UseProcForPerpare=Yes" & Chr$(13)
Attribs = Attribs & "OEMTOANSI=No"
Attribs = Attribs & "Database=Northwind"
' Update ODBC.INI.
DBEngine.RegisterDatabase "NWIND", "SQL Server", True, Attribs
End Sub

Note: In the previous code, you will notice the driver name being passed to the RegisterDatabase function is "SQL Server" and not sqlsrvr.dll. The driver is known to ODBC as "SQL Server", which is responsible for determining the DLL that matches the logical name. To obtain this entry, I had to examine the ODBCINST.INI file, where a list of installed drivers are maintained. The relevant entries are shown as follows:

[SQL Server]
Driver=C:\WIN31\SYSTEM\sqlsrvr.dll
Setup=C:\WIN31\SYSTEM\sqlsrvr.dll

Attaching ODBC Tables

The most common way to access data stored in tables located on a remote database is to attach the tables to a local Jet database and then use the DAO model to work with the tables. With Visual Basic, you can programmatically attach remote tables to a local database. In this section, we're going to examine these features by building a simple application that allows the user to attach remote tables to a Jet database and view the attached tables.

In the context of our examination of ODBC, there are two key features to the application:

1. Allow the user to select a Data Source from those defined on his workstation.

2. Attach the remote table to the database.

Selecting the Data Source

We'll begin by implementing the dialog box to display a list of the Data Sources defined on the user's workstation. The dialog box will provide the following functionality:

Let's begin by implementing the user interface, figure 6.8 shows the completed dialog box.

Fig. 6.8 The SQL Data Sources selection dialog box.

The form properties that need to be changed are set out in Table 6.2.

Table 6.2 Table 6.2 Data Source Selection Form Properties

Property        Seting

Auto3D          True

BackColor       &H00C0C0C0& (light gray)

BorderStype     Fixed Double

Caption         SQL Data Sources

ControlBox      False

MaxButton       False

MinButton       False

Name            frmODBCDataSources

All other properties should be left at their default. Once you have set the form properties, you can add the required controls, as shown in figure 6.8. Details of the controls and their property settings are set out in Table 6.3 (label controls take default properties).

Table 6.3

Table 6.3 Control Settings for the Data Sources Dialog Box

Control         Property        Setting

ComboBox        Name            cboDSN

                Sorted          True

                Style           Dropdown List

TextBox         Name            txtDatabase

TextBox         MultiLine       True

                Name            txtDesc

TextBox         Name            txtDriver

TextBox         Name            txtUser

TextBox         Name            txtPassword

                PasswordChar    *

CommandButton   Caption         OK

                Default         True

                Name            cmdOK

CommandButton   Cancel          True

                Caption         Cancel

                Name            cmdCancel

Now that you've completed the user interfac,e it's time to start adding the code. The most difficult part of implementing this feature is to obtain a list of the Data Sources that are defined on the user's workstation. To do this, you'll have to drop down to the ODBC API. Included in the ODBC API is a function called SQLDataSources, which we can use to obtain a list of the Data Sources. To use this function, we need to allocate some ODBC environment space, which is done via another ODBC function called SQLAllocEnv. Listing 6.2 shows the necessary Visual Basic declarations.

Listing 6.2 SQLENV.BAS Allocating the Environment Space

Private Declare Function SQLAllocEnv Lib "odbc.dll" _
(env As Long) As Integer
Private Declare Function SQLDataSources Lib "odbc.dll" _
(ByVal henv As Long, _
ByVal fdir As Integer, _
ByVal szDSN As String, _
ByVal cbDSNMAx As Integer, _
pcbDSN As Integer, _
ByVal szDesc As String, _
ByVal cbDescMax As Integer, _
pcbDesc As Integer) As Integer

Now that we're figured out the API calls we need to make, we're ready to use the code in Listing 6.3 to write a subroutine to load the list of Data Sources into the list box on the dialog.

Listing 6.3 GETDSRC.BAS Listing the Data Sources

Private Sub GetDataSources(listctrl As Control)
' Gets ODBC data sources and displays then in a listbox
Dim strDataSrc As String
Dim strDesc As String
Dim intDataSrcLen As Integer
Dim intDescLen As Integer
Dim intRetCode As Integer
Dim henv As Long
If SQLAllocEnv(henv) <> -1 Then
strDataSrc = String$(32, 32)
strDesc = String$(255, 32)
'get the first one
intRetCode = SQLDataSources(henv, 2, strDataSrc, _
Len(strDataSrc), intDataSrcLen, _
strDesc, Len(strDesc), intDescLen)
While intRetCode = 0 Or intRetCode = 1
listctrl.AddItem Mid(strDataSrc, 1, intDataSrcLen)
strDataSrc = String$(32, 32)
strDesc = String$(255, 32)
'get all the others
intRetCode = SQLDataSources(henv, 1, strDataSrc, _
Len(strDataSrc), _
intDataSrcLen, _
strDesc, Len(strDesc), _
intDescLen)
Wend
End If
End Sub

After defining the required variables, you allocate the required ODBC environment space. Once this is done, you begin to obtain a list of the Data Sources by making successive calls to the SQLDataSources function. As you retrieve each Data Source, you add it to the list box until you have obtained all Data Sources.

Once you have written the routine for retrieving the Data Sources, you're in a position to use Listing 6.4 to complete the Form_Load method.

Listing 6.4 LOADFRM!.BAS Loading the Form

Private Sub Form_Load()
MousePointer = vbHourglass
' Center form
Move (Screen.Width - Width) \ 2, _
(Screen.Height - Height) \ 2
' Load ODBC Sources
GetDataSources cboDSN
MousePointer = vbDefault
End Sub

Once you have displayed the list of data sources, all that's left is to retrieve the information about each Data Source as the user selects it. This is done by reading the ODBC.INI file using the Windows API call GetPrivateProfileString. The code itself is added to the list box's click method as shown in Listing 6.5.

Listing 6.5 GETDSINF.BAS Getting Data Source Information

Private Sub cboDSN_Click()
' Display info about DSN
txtDatabase = GetAppINIString(cboDSN, "Database", "", _
"ODBC.INI")
txtDesc = GetAppINIString(cboDSN, "Description", "", _
"ODBC.INI")
txtDriver = GetAppINIString(cboDSN, "Driver", "", _
"ODBC.INI")
txtUser = GetAppINIString(cboDSN, "LastUser", GetUserID(), _
"ODBC.INI")
' Set focus
If Len(txtUser) Then
txtPassword.SetFocus
Else
txtUser.SetFocus
End If
End Sub

Having written the most difficult functions, you only need to add the supporting code.

Having provided the user with a means for selecting the Data Source, all that remains is to add the code for attaching the table to the local database and the supporting code. The function in Listing 6.6 attaches the table to the database.

Listing 6.6 TBLATTCH.BAS Attaching the Table

Public Sub AttachTable(dbODBC As Database, strSourceTable _
As String, fSavePassword As Integer)
' Attach the ODBC table to the Jet database
Dim tbl As New TableDef
Dim strTableName As String
Dim intLoc As Integer
' First build a table name to use
' in Jet database by removing any full stops
' from source table name
strTableName = strSourceTable
intLoc = InStr(strTableName, ".")
While intLoc <> 0
Mid$(strTableName, intLoc, 1) = "_"
intLoc = InStr(strTableName, ".")
Wend
' Confirm table name
strTableName = InputBox$("Enter name for attached table", _
frmMainWnd.Caption, strTableName)
' Attach the table
Set tbl = DB.CreateTableDef(strTableName, dbAttachedODBC, _
strSourceTable, dbODBC.Connect)
If fSavePassword Then
tbl.Attributes = tbl.Attributes + dbAttachSavePWD
End If
DB.TableDefs.Append tbl
' Add table to list box
lstTables.AddItem strTableName
End Sub

Using Server Data

Most business applications can be split into types, transaction processing and decision support or reporting. While you will use the same elements of Visual Basic to build both types of application, they differ greatly in one aspect: the location of the data.

In a transaction type system, the data needs to be updated immediately on the server so that it reflects the current situation at any given time. For example, if I have only one item on a particular product in stock and I accept an order for it, I need to update the stork records to reflect this so that no one else will accept an order for the same item.

In a decision support type of system, the data tends to be at least a day old, if not longer. Normally, users are looking at trends, so up to the minute data is not required.

Using Visual Basic to develop such applications allows us to take advantage of this fact. In developing the application, we can copy the data once from the server to the users workstation before the user begins to work with it. This will free up the server to handle other requests and also means that the user will normally experience better performance in screen updates and so forth. In this section, we'll look at the techniques used to implement such an application.

In our example, you'll implement a screen for the Northwind Traders database, which allows users to view a graph of product sales for the year. In this case, you need to extract the product sales from the Orders and Order Details tables and store them locally. The first thing you need to do then is to create a table in our local database to store the data. The structure of this table is shown in Table 6.4.

Table 6.4Table 6.4 Structure of the Local Sales Table

Field Type        Length           Comment

Month Text        2                      Month indicator for product sales           

Product_id Number                    Product's id number

NetSales Number                       Net sales of product

In order to access the data stored in the remote database, you'll need to attach the tables to our local database.

Once you have created the local table and attached the remote tables, you need to write some queries to copy the data between the tables.

The first query needs to join the Orders table with the Order Details table to extract the date, the product, and the net sales. The SQL to do this is as follows:

SELECT DISTINCTROW Orders.[Order Date], [Order Details]. _
[Product ID], Sum(LNG(([Quantity]*[Unit Price])*(1-[Discount]))) _
AS NetSales
FROM Orders INNER JOIN [Order Details] ON Orders.[Order ID] = _
[Order Details].[Order ID]
GROUP BY Orders.[Order Date], [Order Details].[Product ID]
HAVING (((Year([Order Date]))=1992));

Once you have extracted this information, you add a second query that summarizes the information and enters it in the local table. This query is as follows:

INSERT INTO locMonthlyProductSales ( Month, [Product ID], _
NetSales )
SELECT DISTINCTROW Month([Order Date]) AS Month,
GetDailySales1992.[Product ID], Sum(GetDailySales1992.NetSales) _
AS NetSales
FROM GetDailySales1992
GROUP BY Month([Order Date]), GetDailySales1992.[Product ID];

Now that you have all the basic components in place, you need to provide the user with the means to execute the copy. Each time you copy down the remote data, you need to do two things: first you need to delete the existing data and second you need to copy down the new data. To allow for future growth in the system, we'll develop a dynamic method of handling this. The idea is to create a batch job consisting of the several queries, which when executed in sequence, provides the required functionality. In order to store information about the batch process, you need to add two table to our local database: one to hold general information about the batch jobs and one to hold the detailed steps of each job. The structure of these tables is shown in Table 6.5 and Table 6.6.

Table 6.5 Structure of Batch Description Table

Field           Type    Length  Comment

BatchNo         Text    3       Batch number

BatchName       Text    50      Name of batch

BatchDesc       Text    50      Description of what batch does

Table 6.6 Structure of Batch Detail Table

Field     Type    Length   Comment

BatchNo   Text    3        Batch number

StepNo    Number           Step in batch

SQLQuery  Text    50       Name of query to be executed

Having provided a means for storing the batch information, all you need to do now is to add a user interface that will allow the user to execute the batch. The user interface can be a simple dialog box, like the one in figure 6.9, which lists the batch jobs and its description.

Fig. 6.9 The batch execution dialog box.

When the user clicks OK, the batch is executed via the routine shown in Listing 6.7.

Listing 6.7 RUNBATC1.BAS Running a Batch Query

Private Sub cmdOK_Click()
Dim rec As Recordset
Dim qry As QueryDef
' Execute the batch
Set qry = frmMainWnd.gdb.OpenQueryDef("GetBatch")
qry!BatchNum = Format$( _
lstBatches.ItemData(lstBatches.ListIndex), _
"000")
Set rec = qry.OpenRecordset(dbOpenSnapshot)
qry.Close
While Not rec.EOF
Set qry = frmMainWnd.gdb.OpenQueryDef(rec!SQLQuery)
qry.Execute
qry.Close
rec.movenext
Wend
End Sub

Once the data has been copied to the local table, we're back to standard Visual Basic to create the decision support screen.

Action Queries

Up to this stage, you have worked exclusively with Visual Basic's DAO model. Using the model, you have created record sets and worked on the data returned in these sets. While this method of working with data has been adequate in dealing with the Northwind situation, it does have one major disadvantage: it is not very well suited for carrying out operations on large tables. Consider the following problem.

On your server, you have a table that contains a summary of your company's product sales by day. This table contains over a 100,000 rows. Each day you receive a file containing 6,000 transactions, which either replace existing rows or addnew rows to the table. How would you go about performing the required updates/additions?

Using the DAO model, our first attempt might be to use a design such as this:

  1. Attach the sales summary table to a Jet database.
  2. Open the sales summary table as a Dynaset.
  3. Open the update file for input.
  4. Read in the rows from the update file until the EOF marker is reached.
  5. With each row that is read in, use the FindFirst method to locate the old record in the sales summary table and update it with the new data.
  6. For rows read that we fail to find an existing record in the sales summary table, we add it as a new record.

While this solution will work fine on a small table, it will fail in our case because our dynaset contains over a 100,000 rows and the FindFirst method performs a liner search. Thus, it will take hours to perform the required updates. (Take my word, I've tried it.)

Because we have identified the performance hog as such a large dynaset, our second attempt might be to try and reduce the size of the dynaset. One way we could do this is to retrieve all the sales summary records for a particular day, update them or add new ones, and move on to the next day. Our refined design might look like the following:

  1. Attach the sales summary table to a Jet database.
  2. Sort the update file by date order.
  3. Open the update file for input.
  4. Read in the rows from the update file until the EOF marker is reached.
  5. For each change of date in the update file, create a new dynaset from the sales summary table based on update record date.
  6. With each row that is read in, use the FindFirst method to locate the old record in the sales summary Dynaset and update it with the new data.
  7. For rows read in that we fail to find an existing record in the sales summary table, we add it as a new record.

While this solution does improve the situation, it still does not reach our expectations. We're still moving thousands of records from the server to the workstation.

What this problem requires is the capability to carry out operations on the data located in the remote database without having to transfer the records to the workstation. The solution lies in an action query. The Execute method of the database object allows us to submit SQL commands to manipulate data stored in a table without first having to create a Recordset. Using the execute method, our final solution would be as follows:

  1. Connect to the ODBC database directly.
  2. Open the update file for input.
  3. Read in the rows from the update file until the EOF marker is reached.
  4. For each row that is read in, build an update query based on the primary key, and submit it to the server using the Execute method.
  5. Check the number of rows that the update query affects, if it is zero, build an insert query to add the record to the table because it must be a new record.

For comparison, testing the first and final solution on my server showed that the final solution took only one-sixth the time of the first solution.

In addition to using action queries to insert or update records, you can use them to do the following:

Creating Tables on a Server Database

At times, you will need to create tables and indexes on the server database from the desktop application. This section discusses how this is done, and the challenges associated with this activity.

Creating tables and indexes in a C/S environment is almost the same as doing so in a typical desktop database application, with one exception: you need to have been granted the correct security privileges on the server before you can perform the task.

In the first example, you'll build a simple utility to create tables on any database for which ODBC drivers are available.

  1. The utility's functionality can be summarized as follows:
  2. Connect to the database via the standard ODBC sign-on screen.
  3. Once signed on, display a list of the tables in the database.
  4. When the user selects a table, display a list of the fields in the table.

When the user clicks on the New button, a dialog box is displayed which will enable him to create a new table.

Before building the application, let's take it for a test drive so that we understand how it works.

  1. Start the application.
  2. Once started the application will present you with the standard ODBC logon dialog box. Select a data source and complete the logon sequence.
  3. Once you have completed the logon, you'll find yourself at the main window of the application.
  4. From the main window, click the New button in dialog box shown in figure 6.10.

Fig. 6.10 The New button allows you to and add a new table to a database.

1. Complete the dialog as shown in figure 6.11 and click the Create button to add the new table.

Fig. 6.11 A application can be written to add a new table to a server database using Visual Basic.

Now it's time to start coding. We'll begin by developing the user interface. After opening a new project, set the form's properties to the values set out in Table 6.7.

Table 6.7

Table 6.7 The Form Property Settings

All other properties should be left at their default. Once you have set the form properties, you can add the required controls, as shown in figure 6.11. Details of the controls and their property settings are set out in Table 6.8 (label controls take default properties).

Table 6.8

Table 6.8 The Application's Controls

Control Property        Setting

ListBox Columns 3

        Name    lstTables

        Sorted  True

ListBox Columns 3

        Name    lstFields

        Sorted  True

Button  Caption E&xit

        Name    cmdExit

Button  Caption &New...



After building the main form, we turn our attention to the create tabel dialog box. Add a new form to the project and set it's properties as set out in Table 6.9.

Table 6.9

Table 6.9 The Dialog Box Settings

Auto3D  True

BackColor       &H00C0C0C0& (light gray)

BorderStype     Fixed Double

Caption Table Example

ControlBox      False

MaxButton       False

MinButton       False



Add controls to the form as shown in figure 6.11 and set the properties as shown in Table 6.10.

Table 6.10

Table 6.10 The Dialog Box Control Settings

Control Property        Setting

TextBox Name    txtTable

Grid    Cols    4

        Name    grd

        Rows    20

Frame   Name    fraNewField

TextBox Name    txtName

ComboBox        List    True/False

                Byte

                Integer

                Long

                Currency

                Single

                Double

                Name    cboType

TextBox Name    txtLength

Button  Caption &Add

        Name    cmdAdd

Button  Caption C&reate

        Name    cmdCreate

Button  Caption &Close

        Name    cmdClose

This completes the interface, now let's start adding the code. We'll handle the application start up first. The code executed at the start of the application is located in the main form's Form_Load, as shown in Listing 6.8.

Listing 6.8 DSPMAIN.BAS-Displaying the Main Form

Private Sub Form_Load()
MousePointer = Hourglass
' Locate and display form
Move (Screen.Width - Width) \ 2, (Screen.Height - Height) \ 2
Show
Refresh
' Open the database
Set gdb = Workspaces(0).OpenDatabase("", False, False, _
"ODBC;")
DisplayTables
MousePointer = Default
End Sub

The first block of code simply centers the form on the user's screen. The second block of code opens the database and displays it's list of tables. Note the way the OpenDatabase method is used. Entering just "ODBC;" as the last parameter causes the ODBC manager to display the list of data sources and enables the user to logon to any data source. The function DisplayTables simply loops through the database's TableDefs and adds the name of the table to the tables list box in the main screen. The code is provided in Listing 6.9.

Listing 6.9 DSPTBL.BAS Displaying the Existing Tables

Private Sub DisplayTables()
Dim strTable As String ' hold table name
Dim tbl As TableDef ' curret table def
MousePointer = Hourglass
' Display Tables
lstTables.Clear
lstFields.Clear
For Each tbl In gdb.TableDefs
If (tbl.Attributes And dbSystemObject) = 0 Then
strTable = tbl.Name
lstTables.AddItem strTable
End If
Next
MousePointer = Default
End Sub

Moving to the new table form, you use its load method to configure the grid and locate the form, as shown in Listing 6.10.

Listing 6.10 CNFGRID1.BAS Configuring the Grid and Locating the Form

Private Sub Form_Load()
' Locate and display form
Move (Screen.Width - Width) \ 2, (Screen.Height - Height) \ 2
Show
Refresh
' Config grid
grd.Col = 1
grd.Row = 0
grd.Text = "Name"
grd.ColWidth(1) = (grd.ColWidth(1) * 5)
grd.Col = 2
grd.Text = "Type"
grd.ColWidth(2) = (grd.ColWidth(2) * 2)
grd.Col = 3
grd.Text = "Width"
' Init variables
iLastUsedRow = 0
fFieldsAdded = False
End Sub

In review of the code, you will note that the form uses two variables at the form level, iLastUsedRow indicates the number of rows used in the grid, whereas fFieldsAdded acts as a flag to indicate if fields have been added.

The most interesting code in the new table form is in the click method of the Create button (see Listing 6.11).

Listing 6.11 ADDTBL1.BAS Adding a Table

Private Sub cmdCreate_Click()
' Add a new table
Dim i As Integer
Dim gdb As Database
Dim tbl As TableDef ' New table
Dim fld As Field ' New field
Dim strFldName As String
Dim fType As Integer
Dim iLen As Integer
' Create new table def
Set tbl = gdb.CreateTableDef(txtTable)
' Add new fields
For i = 1 To iLastUsedRow
grd.Row = i
grd.Col = 1
strFldName = grd.Text
grd.Col = 2
Select Case grd.Text
Case "True/False"
fType = dbBoolean
Case "Byte"
fType = dbByte
Case "Integer"
fType = dbInteger
Case "Long"
fType = dbLong
Case "Currency"
fType = dbCurrency
Case "Single"
fType = dbSingle
Case "Double"
fType = dbDouble
Case "Date/Time"
fType = dbDate
Case "Text"
fType = dbText
Case "Long Binary"
fType = dbLongBinary
Case "Memo"
fType = dbMemo
End Select
If fType = dbText Then
grd.Col = 3
iLen = CInt(grd.Text)
Else
iLen = 0
End If
' Create field def
Set fld = tbl.CreateField(strFldName, fType, iLen)
tbl.Fields.Append fld
Next i
' Create the table, by adding it to the
' tabledefs
gdb.TableDefs.Append tbl
End Sub

As you can see, there are a several steps involved in adding a table to the database. First, a new TableDef must be created. Then, the table's fields need to be created and added to the new TableDef. Finally, the new TableDef must be added to the database's TableDefs collection.

This completes the utility. As you can see, there really no difference in creating a table on a C/S database as opposed to a desktop database from a code point of view. From a process point of view there is one major difference: Security. Before you can create the table you must possess the required security level.

While server databases are normally managed by a Database Administrator, there are situations where users are allowed to add tables themselves. This section showed you how to use Visual Basic to accomplish this.

From Here...

This chapter taught you how to use ODBC technology in Visual Basic applications. To round out your knowledge on using Visual Basic for developing client/server database applications, you should also review the material in the following chapters:


| Previous Chapter | Next Chapter | Search | Table of Contents | Book Home Page |

| Buy This Book | Que Home Page | Digital Bookshelf | Disclaimer |


To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select Talk to Us.

© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.