S o far in the discussions of accessing databases, the focus has been on using PC-based databases. These types of databases include Access, FoxPro, dBASE, and Paradox. However, Visual Basic is also a great tool for creating front ends for client/server applications. These types of applications are used to access data stored in database servers such as SQL Server and Oracle. Most of your front-end work--such as designing forms and writing code to process information--will be the same whether you are writing an application for a PC database or a client/server database. The key difference is in how you make the connection to the data.
In this chapter, we'll discuss how your Visual Basic programs can easily access data stored in a variety of remote locations through the use of Remote Data Objects (RDO). You'll see how the recent addition of RDO to Visual Basic's repertoire makes short work of writing applications that need to work with remote data.
Before delving further into actually setting up applications that access client/server databases, you'll take a look at the difference in the philosophy of the two types of database access. In the PC-database world, the information is accessed through the database engine, which is part of the application. For Visual Basic, the Jet engine is a part of your database applications. As you issue commands to retrieve information from the database, the commands are interpreted by the Jet engine and the processing of the commands is done locally on your PC. Whether the database file actually resides on your PC or is located on a file server, the database engine remains on your PC. The application itself contains the logic to directly access the database file. In the client/server world, this is not the case. Your application issues a request for information, usually in the form of a SQL statement. This request is passed to the database server which processes the request and returns the results. This is true client/server computing, in which a database server does the actual processing of the request.
Client/server systems have a number of advantages over just sharing a database file. First, database logic is removed to a central, more maintainable location. For example, suppose you have a program that calculates sales tax based on your company's rules. In a client/server environment, the logic for this calculation process would be located on the database server. This means you can make changes to it in one place, without having to rewrite the client application. Other advantages include being able to distribute processing and separating the user interface design from the business logic.
One method used by Visual Basic to communicate with client/server databases is called Open Database Connectivity, or ODBC. ODBC is a component of Microsoft's Windows Open System Architecture (WOSA). ODBC provides a set of application program interface (API) functions, which makes it easier for a developer to connect to a wide range of database formats. Because of the use of ODBC standards, you can use the same set of functions and commands to access information in a SQL Server, Oracle, or Interbase, even though the actual data-storage systems are quite different. You can even access a number of PC databases using ODBC functions.
ODBC drivers are the DLLs containing the functions that let you connect to various databases. There are separate drivers for each database type. For many standard formats, such as PC databases and SQL Servers, these drivers are provided with Visual Basic. For other databases, the ODBC driver is provided by the server manufacturer.
NOTE: If you use ODBC in your application, make sure the appropriate drivers are distributed with your application. If you selected the Redistributable ODBC option when installing VB, an ODBC subdirectory should have been created in the Visual Basic program directory. Running Setup.exe will install ODBC drivers, although you will still have to set up your data sources.
ODBC drivers can be one of two types: single-tier or multiple-tier. A single-tier driver is used to connect to PC-based database systems that may reside on either the local machine or a file server. Multiple-tier drivers are used to connect to client/server databases where the SQL statement is processed by the server, not the local machine.
Each ODBC driver you encounter must contain a basic set of functions, known as the core-level capabilities. These basic functions are:
Before you can use ODBC to connect to a database, you must make sure of two things:
Both of these functions can be accomplished by using the ODBC Manager application. Also, the second function can be accomplished from code, by using the data access objects. Remember, an ODBC driver is used to connect to a type of database, for example, SQL Server. An ODBC Data Source is a configuration of an ODBC driver used to connect to a specific database, that is, "Accounting Department Database."
NOTE: On Microsoft Windows 95 systems, you will find the ODBC manager in the Control Panel, under the Settings item on the Start menu. The icon you are looking for is labeled "32-bit ODBC." You might also have an icon called "ODBC" if you have some older 16-bit programs.
NOTE: To ensure that all readers can use the information presented here, the Access ODBC driver is used in all examples. While this is a PC database, the methods used can also be applied to server databases. It is important to remember that connecting to an Access MDB file via ODBC is different than connecting directly through the Jet engine.
Gaining Access to ODBC Drivers To set up the ODBC Data Sources on your system, you need to use Windows 95's ODBC Data Source Administrator. You will find this in the Control Panel, which is accessible by choosing the Control Panel item from the Settings submenu on the Start menu. The Control Panel is illustrated in Figure 32.1.
FIG. 32.1
The ODBC Data Source Administrator is accessed by selecting the "32-bit
ODBC" icon on the Control Panel.
The ODBC Administrator Dialog Box
If you see different dialog boxes than the ones pictured here when you click the 32-bit ODBC icon, don't panic. As with any product, Microsoft has produced several versions of ODBC. ODBC is included with many of their products, including Office and Visual Basic. Depending on the installation options you chose, you may or may not have the latest version. Older versions of ODBC do not have the "tabbed" dialog style. Fortunately, ODBC is included with VB so you can install it during setup.
When you open the ODBC Manager, you see the ODBC Data Source Administrator dialog box, as shown in Figure 32.2. The Data Source administrator includes several tabs used to add data sources as well as new ODBC drivers.
FIG. 32.2
The Data Source Administrator dialog box allows you to configure ODBC data
sources.
As you'll notice, the titles of the first three tabs in the dialog box end in the letters DSN. DSN is an abbreviation for Data Source Name. The DSN is the key that your program uses to identify an ODBC Data Source. ODBC takes care of mapping the DSN to the actual driver, server, and database file.
The ODBC Data Sources are divided into three types: user, system, and file. Although the purpose of all DSNs is essentially the same--to provide information about a specific data source--there are differences where and when you can use each type:
The remaining three tabs in the ODBC Data Source Administrator dialog box are used for informational and debugging purposes. The ODBC Drivers tab, shown in Figure 32.3, displays a list of the ODBC drivers installed on your machine.
FIG. 32.3
The ODBC Drivers tab tells you which drivers are installed on your system.
The last tab, the About tab, is very similar to the ODBC Drivers tab. It lists the versions and files used by ODBC itself. You may find these two screens helpful in determining whether or not your users have the correct drivers installed.
Before moving on, note the Tracing tab, pictured in Figure 32.4. This tab allows you to trace each call made by the ODBC Manager to the ODBC Drivers. Remember that ODBC is a means to connect to various databases via some common API functions. The Tracing options allow you to view those API calls. This is something you probably won't do very often, but it is nice to know about.
Creating an ODBC Source with the ODBC Manager To set up a data source for use in your application, you need to know which driver to use and how to configure it. For example, you will need to know the name of the SQL Server or Access MDB file the data resides in. You also will need to come up with a unique name to identify the data source.
Fig. 32.4
The Tracing tab of the ODBC Administrator is a low-level debugging aid.
Set up a sample data source now. Go to the User DSN screen and click the Add button to create a new data source. This presents you with the Create New Data Source dialog box, shown in Figure 32.5. In this first dialog box, you choose the ODBC driver that will be used to access the data.
FIG. 32.5
Selecting the ODBC driver is the first step to setting up a data source.
After choosing the driver and clicking the OK button, you are presented with the Setup dialog box for the particular database type associated with the driver. Choose the Microsoft Access Driver and press the Finish button. You will be presented with a dialog box like that in Figure 32.6.
FIG. 32.6
A Setup dialog box lets you specify the information necessary to connect to
an ODBC data source.
In this dialog box, you provide a name in the Data Source Name box. This is the name you will use in your applications to refer to the data source. You can also choose to include a Description of the data source.
After setting the name, you need to choose the actual database file or server you want to use with your program. For the Access driver, this is done by clicking the Select button of the dialog box. You are then presented with a Select Database dialog box (which is basically an open-file dialog box). Try opening a file by choosing an MDB file on your PC. Figure 32.7 shows a data source called MyDSN linked to the biblio database that comes with Visual Basic.
FIG. 32.7
The Access dialog box allows you to select which MDB file you are going to
be working with.
Keep in mind that the setup dialog boxes are driver dependent. In each case, however, you specify both a data-source name and the location of the data. Figure 32.8 shows the dialog box for Microsoft SQL Server.
FIG. 32.8
The SQL Server dialog box requires you to specify the server where the information
is located.
The DSN screens also give you the capability to Configure or Remove ODBC data sources. To modify a data source, select the data source and then click the Configure button. This presents you with the same dialog box that you used initially to set up the data source. To delete a data source, select it and then click the Remove button.
Using the DAOs to Create an ODBC Source You are not limited to setting up data sources interactively. There are times, such as application installation, where you might want to add a data source with code. For this purpose, you can use the RegisterDatabase method of the DBEngine object. Here is the syntax of the RegisterDatabase method:
DBEngine.RegisterDatabase dbname, driver, silent, attributes
Table 32.1 defines the parameters used in the RegisterDatabase method.
Parameter | Definition |
dbName | A user-definable string expression that specifies the data source name (for example, "MyDatabase"). |
driver | A string expression that indicates the installed driver's name (for example, ORACLE) as listed in the ODBC Drivers tab of the ODBC Administrator. silent True specifies that the next parameter (attributes) indicates all connection information. False specifies to display the Driver Setup dialog box and ignore the contents of the attributes parameter. |
attributes | All connection information for using the ODBC driver. This parameter is ignored if silent is set to False. |
The following code sample illustrates how the RegisterDatabase method is used to create a link to an Access database. Before attempting to use the data access objects, remember to add the appropriate reference.
Dim sAttrib As String Dim sDriver As String sAttrib = "DBQ=D:\VB5\BIBLIO.mdb" sDriver = "Microsoft Access Driver (*.mdb)" DBEngine.RegisterDatabase "MyDSN", sDriver, True, sAttrib
After executing the above code, you can go back to the Data Source Administrator window and verify that a new User DSN has been added.
NOTE: You can also use the rdoRegisterDataSource method of the rdoEngine to perform the registration task for Remote Data Objects.
Notice that for the Access driver the "DBQ" parameter indicates the name of the database file. To determine all the parameters required for a particular ODBC driver, you should create a connection with the ODBC Manager and then examine the settings in the Registry. You will find these under HKEY_USERS\Default\Software\ODBC\ODBC.INI. (To view the Registry, use the REGEDIT application included with Windows 95.) To specify multiple parameters with the RegisterDatabase method, separate them with a semicolon.
Data access objects (DAO) are a layer on top of the ODBC API. Before the advent of Remote Data Objects in Visual Basic, programmers would sometimes skip this layer by calling the ODBC API directly. The reason, of course, was to make their applications run faster. However, the ODBC API calls are much harder to use than the data access objects. Remote data objects changed this by providing an interface to the ODBC API that uses the familiar operations of setting properties and calling methods. Because properties and methods are used in all Visual Basic programs, this made the access of ODBC databases much easier for developers to understand and accomplish.
The remote data objects, or RDO, are very similar to the data access objects (DAO), which were covered in Chapter 31, "Improving Data Access with Data Access Objects (DAO)." This similarity not only makes RDO easier to understand, but it also makes the conversion of programs from PC databases to client/server databases much easier. In fact, once the connection to the data source is made, the same code statements can be used to access the data by using RDO as were used for DAO.
To give you a feel for the similarities between the RDO and DAO models, Table 32.2 lists a number of RDO objects and their corresponding DAO objects.
RDO Object | DAO Object |
rdoEngine | DBEngine |
rdoEnvironment | Workspace |
rdoConnection | Database |
rdoTable | TableDef |
rdoResultset | Recordset |
rdoColumn | Field |
rdoQuery | QueryDef |
rdoParameter | Parameter |
In addition, the rdoResultset object supports several types of returned sets of records, similar to the recordset types of the Recordset object. Table 32.3 summarizes these similarities.
rdoResultset Types | Recordset Types | Definition |
Keyset | Dynaset | Updatable set of records in which movement is unrestricted. |
Static | Snapshot | Non-updatable set of records that were present when the set was created. Updates by other users are not reflected. |
Dynamic | N/A | Similar to a keyset. |
Forward-only | Forward-only | Similar to a static resultset or snapshot, but you can move forward only through the set of records. This is the default Resultset type. |
Notice that the Remote Data Objects do not support any rdoResultset type that returns a table. This is because the Remote Data Objects are geared to using SQL statements to retrieve subsets of information from one or more tables. You must set the order of the rdoResultset with the Order By clause of the SQL statement used to create the set. Also, because there is no table equivalent, RDO does not support indexes.
As you might expect with the similarity of the objects, there are methods of the RDO that are similar to the methods of the DAO. These methods and their respective objects are summarized in Table 32.4.
RDO Method | RDO Object | DAO Method | DAO Object |
rdoCreateEnvironment | rdoEngine | CreateWorkspace | DBEngine |
BeginTrans | rdoConnection | BeginTrans | Workspace |
CommitTrans | rdoConnection | CommitTrans | Workspace |
OpenConnection | rdoEnvironment | OpenDatabase | Workspace |
RollbackTrans | rdoConnection | Rollback | Workspace |
CreateQuery | rdoConnection | CreateQueryDef | Database |
Execute | rdoConnection | Execute | Database |
OpenResultset | rdoConnection | OpenRecordset | Database |
And finally, the rdoResultset object and the Recordset object have the following methods in common:
To further illustrate the similarities between the RDO and DAO models, the code in Listings 32.1 and 32.2 perform the same function on the "biblio" database. The difference between the two listings is simply the objects and methods used to create returned records. Once the recordset or resultset is established, the remaining statements simply print each entry in the first field. In the RDO example, the ODBC data source "MyDSN" was created previously with the ODBC Manager.
NOTE: In order to use the Remote Data Objects, you need to add a reference to the "Microsoft Remote Data Object" from the Project, References menu.
Dim db As rdoConnection Dim rs As rdoResultset Dim sSQL As String Set db = rdoEngine.rdoEnvironments(0).OpenConnection("MyDSN") sSQL = "Select * From Titles" Set rs = db.OpenResultset(sSQL, rdOpenKeyset) rs.MoveFirst While Not rs.EOF Print rs.rdoColumns(0) rs.MoveNext Wend rs.Close db.Close
Dim db As Database Dim rs As Recordset Dim sSQL As String Set db = DBEngine.Workspaces(0).OpenDatabase("D:\VB5\BIBLIO.MDB") sSQL = "Select * From Titles" Set rs = db.OpenRecordset(sSQL, dbOpenDynaset) rs.MoveFirst While Not rs.EOF Print rs.Fields(0) rs.MoveNext Wend rs.Close db.Close
Another thing you will want to explore with RDO is asynchronous execution of database operations. This means control is returned to your program before the database operation completes, as in the example below:
Set db = rdoEngine.rdoEnvironments(0)._ OpenConnection("MyDSN", , , , rdAsyncEnable) While db.StillConnecting = True Print "Connecting..." Wend
The constant rdAsyncEnable indicates asynchronous operation. The while loop keeps running until the connection is made and the StillConnecting property becomes False.
If you want a faster way to create applications by using ODBC data sources, you can use the Remote Data control (RDC). The RDC lets you set a few properties of the control, and then the RDC handles all the tasks of making the connections to the ODBC data source for you. In this way, the RDC automates the methods of the remote data objects in the same way that the data control automates the methods of the data access objects.
After setting up the Remote Data control, you can use the bound controls to display and edit information that is in the resultset created by the data control. The bound controls are set up the same way they would be for use with the Data control that was discussed in Chapter 29, "Using the Visual Basic Data Control," except that now, the DataSource property of the bound controls points to a Remote Data control. Once set up, the bound controls are updated with new information each time a new row is accessed by the Remote Data control.
The Remote Data Objects were compared to the data access objects in the earlier "Using the Remote Data Objects" section; now take a look at the similarities of the Data control and the RDC. As you might expect, many of the properties of the RDC have counterparts in the Data control. These properties and their functions are summarized in Table 32.5.
RDC Property | Data Control Property | Purpose |
BOFAction | BOFAction | Determines whether the beginning of file flag is set when the user invokes the MovePrevious method while on the first record. |
DataSourceName | DatabaseName | Specifies the database containing the desired information. |
EOFAction | EOFAction | Determines whether the end of file flag is set or if a new row (record) is added when the user invokes the MoveNext method while on the last record. |
ResultsetType | RecordsetType | Determines the type of dataset created by the control. |
SQL | RecordSource | The SQL statement that identifies the specific information to be retrieved. |
Setting up the RDC for use in your program is also very similar to setting up the Data control. Before you can use the RDC, you must first add it to your project. You do this by using the Components dialog box, which you access by choosing the Components item from the Project menu. After you close the dialog box, the remote data control is added to your toolbox.
TROUBLESHOOTING: The Remote Data control does not appear as one of the available controls in the Custom Controls dialog box. You may have chosen not to install the remote data control when you first set up Visual Basic. You need to reinstall that portion of Visual Basic. Also, if you do not have the Enterprise Edition of Visual Basic, the remote data control is not available to you at all.
To set up the remote data control, follow these steps:
As stated in Step 3, you can choose the DataSourceName value from a drop-down list. This list contains every registered ODBC data source on your system. An example of this list is shown in Figure 32.8.
FIG. 32.9
You can choose from a list of available ODBC data sources when setting the
DataSourceName.
After you have set up the remote data control, you then can attach bound controls to it by setting the DataSource property. As shown in Figure 32.10, a drop-down list in the DataSource property contains the names of any remote data controls or data controls on the current form. After the DataSource property has been set, you can select the DataField property from a list, just as you did for the controls bound to a data control.
FIG. 32.10
The DataSource property list contains all available data controls,
remote or not.
This chapter has given you a basic understanding of client/server applications. The chapter has also shown you how the Remote Data Objects and Remote Data control make it easier to access the ODBC databases that are part of many client/server programs.
© Copyright, Macmillan Computer Publishing. All rights reserved.