Up to this point, Database Developer's Guide with Visual Basic 4.0 has used desktop database files in all of the examples of Visual Basic 4.0 database applications. Sample applications that use Jet database files predominated in the preceding
chapters because the .mdb file format is Microsoft's (and the author's) preferred database file structure for applications that do not need to maintain interoperability with existing applications that share dBASE, FoxPro, or Paradox table files. Another
reason for using the .mdb database file structure is that .mdb files resemble the file structures used by client/server databases.
This chapter introduces you to the principles employed to create Visual Basic 4.0 database applications that serve as front-ends to client/server RDBMS back-ends. The RDBMS used for the majority of the examples in this chapter is Microsoft SQL Server
versions 4.21a and 6.0 (SQL Server) running as a service of Windows NT Server 3.51. SQL Server 6.0 is one of the components of Microsoft Back Office 1.5, the version that was current when this edition was written. SQL Server 6.5, which offers a variety of
new features for enterprise computing, is scheduled for release in mid-1996. All versions of SQL Server are backwardly-compatible with version 4.21a and can be accessed through Jet 1.x, 2.x with 16-bit ODBC drivers, and via Jet 3.0 with
32-bit ODBC drivers.
The examples in this chapter use the 32-bit Microsoft ODBC API (version 2.5), so the examples are equally applicable to most client/server RDBMSs for which 32-bit ODBC drivers that support core-level SQL grammar and offer level 2 ODBC compliance are
available. The preceding chapter, "Using the Open Database Connectivity API," describes Visual Basic 4.0's interface to the ODBC API in detail and explains how to use single-tier ODBC drivers. Therefore, this chapter discusses only those elements
of the ODBC API, such as creating client/server ODBC data sources that are specific to multiple-tier ODBC drivers and Visual Basic 4.0 front-ends for client/server RDBMSs. This chapter includes an example of exporting data from Jet databases to
client/server tables because Visual Basic 4.0 developers often are called on to upgrade Visual Basic and Access applications from desktop RDBMSs to client/server database front-ends.
Designing Visual Basic 4.0 front-ends for client/server databases follows the same methodology as designing applications that manipulate tables of conventional desktop databases, as described in preceding chapters. If you have an RDBMS table structure
that duplicates the structure of the tables that compose your desktop database, you simply create an ODBC data source and link the tables from the server database to a local .mdb file. (Link replaces attach in Jet 3.0 terminology.) Jet 3.0
automatically handles all of the remote access issues for you and lets you use bound controls to display and update table data. Alternatively, you can apply the OpenDatabase method and the OpenRecordset method to create a Recordset object of the Dynaset or
Snapshot type to manipulate a query result set. (The Table-type Recordset isn't available with client/server RDBMSs.) You can elect to bypass Jet 3.0 and let the server process the query by invoking the Execute method with the dbSQLPassThrough option to
create a Recordset object of the Snapshot type. This chapter describes techniques for all three methods of manipulating data in tables of client/server RDBMSs.
The examples in this chapter use Microsoft Access 95 to export files to an SQL Server database and to link SQL Server tables to a Jet 3.0 database. It's possible to use VBA code and SQL CREATE TABLE statements to create server tables
and INSERT INTO statements to export data to the tables. You also can use Visual Basic's Data Manager add-in, the VisData sample application, or TableDef operations in VBA code to link tables to a Jet .mdb file. All versions of Access provide automated
exporting of table structures and data to client/server RDBMS, as well as offer a quick and simple linking of the tables to a new .mdb file. If you intend to develop client/server front-end applications using local .mdb files for subsequent
"upsizing" to a client/server RDBMS, Access is an indispensable tool for making the transition.
Microsoft's objective for its BackOffice software suite is to gain market share in the "enterprise computing" sector, which today is dominated by mainframes, minicomputers, and high-end PC servers running UNIX. Microsoft has established an
aggressive price structure for BackOffice and its components; the licensing cost for SQL Server is substantially less than competitive RDBMSs offering similar performance and features. Installation and administration of Windows NT 3.51 and SQL Server 6.0
is far simpler than that of UNIX and UNIX-based RDBMSs. Therefore, many smaller firms, which traditionally have employed shared file systems for multiuser database systems, are moving to client/server systems. The practical limit for shared-file desktop
database systems is 20 to 50 simultaneous users, depending on the ratio of decision-support to online transaction-processing applications in use. Migration from shared-file to client/server systems has created a substantial demand for Visual Basic
developers with client/server credentials. It's a reasonably sure bet that the majority of new Visual Basic 4.0 client/server front-ends will use Microsoft SQL Server as the back-end. The sections that follow describe the most important features of SQL
Server.
You can connect to SQL Server via NetBEUI, IPX/SPX, TCP/IP, and a variety of other network protocols. NetBEUI (the Windows Network protocol) often is used for new server installations at smaller firms because of its speed and simplicity. Many
shared-file database systems run over NetBEUI because the default protocol for Windows for Workgroups 3.1+ is NetBEUI. NetWare IPX/SPX protocol most commonly is used where Novell networks predate installation of Windows NT Server and SQL Server. The
current trend is toward TCP/IP as the favored protocol for client/server networking because of its support of heterogeneous networks, which are likely to include servers running NetWare, UNIX, and Windows NT. Use by the Internet also has contributed to the
general acceptance of TCP/IP as the primary networking protocol for at least the last half of this decade. Microsoft calls TCP/IP its "strategic protocol for scaleable Windows-based networking."
The networking features of SQL Server are designed for maximum user convenience; for example, you can set up Windows NT so that users are automatically logged on to SQL Server when they log on to the network. Automatic logon to the database server also
is provided across remote servers in Windows NT domains that share a trust relationship. Successive versions of SQL Server have demonstrated substantial improvements in performance with increasing numbers of simultaneous users The database objects of SQL
Server 6.0+ are compatible with those of SQL Server 4.2+, so upgrading to newer versions is a relatively painless process.
SQL Server 4.21a for Windows NT and later versions of SQL Server use one or more Net-Libraries to provide access by a variety of networked clients. SQL Server simultaneously supports named pipes connections over NetBEUI, TCP/IP, and
IPX/SPX, and uses the TCP/IP sockets API for communication with Macintosh, UNIX, and DEC MVS clients. Windows 3.1+ clients use a 16-bit library (DBNMP3.DLL) to implement named pipes; Windows 95 and Windows NT use a 32-bit named pipes library (Dbnmpntw.dll)
on the client side.
A conversation between an application and SQL Server takes place through one or more connections established by the named pipes service or by one of the other Net-Libraries supported by SQL Server. A connection is similar in concept to that of the
Windows Sockets for TCP/IP communication. Each open connection is dedicated to the client workstation that originated the connection and may persist for a period of time after the client is finished using the connection.
The following information relating to the Jet 2.0 database engine's use of SQL Server connections is based on the Microsoft "Jet Database Engine ODBC Connectivity White Paper," dated November 15, 1993, by Neil Black of Microsoft's Jet Program Management group and Stephen Hecht of the Jet Development group. You can download this white paper in Word format (Rjetwpv2.doc) as RJETV2.ZIP from the ODBC/Client/Server Library of the MSACCESS Forum on CompuServe. If you use or intend to use Visual Basic 4.0 front-ends for SQL Server databases, you need a copy of this white paper to understand fully how the Jet database engine manages connections. Connection management by Jet 3.0 is quite similar to that of Jet 1.1 and 2.x.
When you use the Jet database engine to connect to a server back-end with the ODBC API and create a Recordset object of the Dynaset type from a query, the operation usually requires at least two connections. (One connection obtains information about
the query from the server and the other passes the data to your application.) Recordset objects of the Dynaset type having less than 100 records and Snapshot-type Recordset objects require only a single connection. If your query is based on a complex SQL
statement that involves multiple JOINs, many GROUP BYs, or creates a crosstab query, it is likely that your application will open three connections at once. Although the Jet database engine attempts to share connections when it can, there are many
situations in which several simultaneous connections may be required by your application. Each user connection requires about 18KB of server memory; on installation, 25 is the default maximum number of SQL Server connections and is likely to support 10
simultaneous users. You must increase the maximum number of SQL Server connections as workstations running SQL Server front-ends are added to the network.
Both Windows 95 and Windows NT are multithreaded operating systems. A thread is a single task that is executed by an application or the operating system. Multithreaded operating systems enable multiple tasks to appear to run simultaneously; the
operating system determines the priority of the task and enables tasks with the highest priority to execute first and most often. Tasks with higher priorities can suspend processing of lower priority tasks; operating systems that enable task priorities to
govern the flow of the execution of tasks are called preemptive multitasking applications. 32-bit applications must be coded explicitly to support multiple threads of execution. SQL Server and Access 95 are multithreaded 32-bit applications; Jet 3.0
supports up to three threads of execution.
Windows NT provides a thread for each connection to SQL Server from a worker pool of threads whose population is set by the sp_conFigure stored procedure. When a client workstation makes a request from SQL Server, one of the threads from the worker
pool is assigned to the workstation's connection. When the request is fulfilled and the connection is terminated, the connection thread is returned to the pool. If a client workstation requests a thread and none are available in the pool, the first thread
released by another connection is assigned to the request. When a fault occurs during execution of a thread, only the connection associated with the thread is affected. Thus, a page fault on a single connection does not bring down the server.
The advantages of a multithreaded operation are particularly evident when you add more than one microprocessor unit to the server. Windows NT supports thread-level multiprocessing, usually called symmetrical multiprocessing (SMP), and is referred to as
a scalable operating system. These terms mean that multiple processors that share the same memory region can execute any thread. This process also is called symmetrical load balancing. If a thread is executing on a processor that is running
near capacity, the thread can be transferred so that it executes on another processor. Windows NT 3.51, and thus SQL Server, supports up to eight Intel processors. Therefore, it is unlikely that a Windows NT server running on SMP hardware would ever become
CPU-bound if enough sockets are available to hold additional processors. Multithreaded SMP operation and the capability to run on a variety of RISC microprocessors, not just members of the Intel 80x86 series, are the two most distinguishing features of
Windows NT.
SQL Server 6.0 provides "100 percent scalability" for up to four processors; therefore, a four-processor server theoretically has four times the capacity of a single-processor server. With more than four processors, the benefits to SQL Server of SMP begin to drop off. As an example, Microsoft says four to eight processors provide "80 percent scalability." Today's most common SMP server configurations for Intel chips use two- or four-processor motherboards.
Examples in preceding chapters explain the procedure for creating ODBC data sources for desktop databases using the ODBC database drivers included with Microsoft Office 95. The procedure described in the following section for adding the northwind SQL
Server database as a 32-bit ODBC 2.5 data source is typical of the method of adding a data source from the majority of popular client/server RDBMSs for which 32-bit ODBC drivers are available. Each supplier has a slightly different approach to the design
and collection of control objects in the dialogs used to set RDBMS-specific options for the data source.
Use of all lowercase letters for the names of database objects, including table and field names, is a UNIX tradition. Similarly, it has been a standard practice to install SQL Server with UNIX case-sensitive object names. The default installation option for SQL Server 4.21 is case-sensitive object names; subsequent versions default to case-insensitivity. It is strongly recommended that you install SQL Server using case-insensitive object names.
The two sections that follow describe how to add the northwind database as an SQL data source and how to test the validity of your new ODBC data source with the VisData sample application. You or the database administrator must add the northwind
database using the SQL Server tools appropriate for your version of SQL Server. The database tables, which are derived from the Northwind.mdb sample database of Access 95, require about 2MB. Setting a database size of 3MB to 5MB for northwind provides
sufficient room for adding indexes to the tables and experimenting with action queries that add a large number of records to the tables.
To use the SQL Server ODBC database driver to add the northwind database as an ODBC data source, follow these steps:
You can verify the settings for the new ODBC data source by launching RegEdit and inspecting the \HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\Northwind hive (see Figure 20.4).
Figure 20.4. Entries for the Northwind ODBC data source in the Windows 95 Registry.
The ODBC data source created in the preceding steps is available only to the current Windows 95 or Windows NT user. You can create a System data source that is available to all users of the computer by clicking the System DSN button of the ODBC Data Sources dialog to display the System Data Sources (Driver) dialog. Follow preceding steps 2 through 8 to create the system data source. System Data Sources appear in the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI folder.
As noted earlier in this chapter, using any version of Access to export data from desktop databases to SQL Server is easier (and usually faster) than the alternatives, such as executing SQL DDL statements from Visual Basic or using SQL Server's bulk
copy process (BCP). This section describes how to use Access 95 to automatically create server table structures and append records to the tables. The process described in this section also is applicable, with only minor modifications, to other SQL RDBMSs
for which you have 32-bit drivers, as well as to Access versions 1.1 and 2.0.
To create tables in the SQL Server northwind database from the Access 95 sample database, Northwind.mdb, follow these steps:
Access only exports the table structures and data; the process does not generate indexes on or relationships between tables. If you use the Microsoft Access Upsizing Wizard add-in, the Wizard automatically adds indexes and the triggers or SQL DDL statements necessary to maintain data consistency (by constraints) and referential integrity (by relations). Triggers are a type of stored procedure analogous to a Visual Basic event handling subprocedure. The trigger procedure is executed on one or more events, such as inserting, updating, or deleting a record. When this edition was written, the Access Upsizing Wizard was available from Microsoft (at a nominal charge) only for Access 2.0 and was designed to support SQL Server 4.21a and earlier versions. Microsoft is expected to release in mid-1996 the 32-bit Upsizing Wizard for Access 95, which will support SQL Server versions 4.2 through 6.5. Microsoft SQL Server 6.5 was in the beta-testing stage when this edition was written.
If the tables you are exporting have more than 10,000 rows, it usually is faster to export the table data to an .asc, .txt, or .csv file and then use the server's BCP application to import the data to the tables. In this case, export only the existing table structures to a new Access .mdb file, then use BCP to append the data from the .txt file.
[VB4_NEW]The RegisterDatabase method of the DBEngine object lets you create a statement that performs the same function as making the entries in the ODBC setup dialog, such as the ODBC SQL Server Setup dialog shown in Figure 20.3. The RegisterDatabase
method, which replaces Visual Basic 3.0's RegisterDatabase statement, can be used to create or modify ODBC data source entries in ODBC.INI and the ODBC.INI hive of the Registry, identical to that which you create with the ODBC Administrator's setup
procedure. Your code needs to apply the RegisterDatabase method before invoking the OpenDatabase method that refers to the ODBC data source that has been created or modified by the RegisterDatabase method.
The syntax of the RegisterDatabase method is as follows:
DBEngine.RegisterDatabase strDSN, strDriver, fSilent, strAttribs
The strDSN argument is the data source name, and strDriver specifies the registered name of the ODBC driver used with the data source. The fSilent flag determines if the driver dialogs appear when the RegisterDatabase method is
invoked. (Set fSilent False to display the dialogs.) The strAttribs argument provides additional information required by the driver to make the connection. Each attribute is separated from the preceding attribute by a carriage return
(Chr$(13)). To apply the RegisterDatabase method to duplicate the entries in ODBC.INI that are shown earlier in the chapter and which open the Database object, dbNwind, created from the northwind data source, use the following code:
Dim dbNwind As Database Dim strDSN As String Dim strDriver As String Dim fSilent As Boolean Dim strAttribs As String strDSN = "northwind" strDriver = "d:\Win95\System\sqlsrv32.dll" fSilent = True strAttribs = "Description=Northwind Sample Database" & Chr$(13) strAttribs = strAttribs & "Server=OAKLEAF0" & Chr$(13) strAttribs = strAttribs & "ODBCDatabase=northwind" & Chr$(13) strAttribs = strAttribs & "OemToAnsi=No" & Chr$(13) strAttribs = strAttribs & "LastUser=sa" & Chr$(13) DBEngine.RegisterDatabase strDSN, strDriver, fSilent, strAttribs Set dbNwind = Workspaces(0).OpenDatabase(strDSN, False, _ False, "ODBC;")
If any of the values in the strAttribs argument do not match current entries in the ODBC.INI Registry hive for the data source, the existing entries are updated. Attribute values are not case-sensitive. The Set statement is required only if you
intend to use VBA code to manipulate the data source. Once you've created the object variable of the Database type, you can treat the database as if it were a local database. The one exception is that you can't open a Recordset of the Table type over a
table of a remote database.
Microsoft recommends that applications requiring access to client/server databases link the tables to a Jet .mdb file. This recommendation is appropriate for prototype front-ends and relatively simple production decision-support applications. The
sections that follow describe how to link client/server tables to a Jet 3.0 .mdb file, how to use the VisData sample application with linked tables, and how to create applications to compare the performance of local and remote data sources with bound
controls.
Linking server database tables to a Jet database is the fastest method of verifying that the export operation succeeded and that all of the tables reside in the northwind SQL Server database. You automatically generate the equivalent of a temporary
local primary key index on the linked tables when you create the links. Using Access to link the tables and to generate the local indexes is much faster than writing the VBA code to accomplish this objective.
To link the tables of the SQL Server northwind database to a new version of Northwind.mdb, follow these steps:
It is a common practice among database administrators (DBAs) to provide users with SQL VIEWs of tables or query result sets, rather than direct access to the tables. Jet lets you link VIEWs in the same manner as TABLEs. (As noted in Table 19.5 of the preceding chapter, TABLE, VIEW, SYSTEM TABLE, ALIAS, and SYNONYM are Jet's default linkable objects). Traditionally, VIEWs have not been updatable; most client/server RDBMSs now provide updatable views. Even if the table(s) underlying an updatable VIEW have primary key indexes, the VIEW is not indexed. If the VIEW includes field(s) that uniquely identify its records, you can add a "fake" index when you link the VIEW to a Jet table.
The VisData sample application is useful to confirm the properties of linked client/server tables, as well as to inspect the ODBC connect strings that the Access linking procedure generated in order to create the links. Follow these steps to use
VisData with Jet databases containing tables linked from ODBC data sources:
Figure 20.17. Using VisData to display the properties of a table linked by ODBC.
Assigning valid user ID (UID) and password (PWD) values in design mode breaches database security. A better practice is to design a logon form in which the user enters his or her password and login ID prior to opening the first form that uses the linked tables.
You can compare the speed of traversing local and remote Recordset objects of the Dynaset and Snapshot type by creating a form with two Data and DBGrid controls, as illustrated by Figure 20.18. (The term remote often is used as a synonym for a
server table accessed through the ODBC API.) The first (local) Data control and its bound DBGrid uses Access 95's Northwind.mdb sample database and the second Data control/DBGrid combination uses the Northwind.mdb with tables linked from the SQL Server
northwind database. Both Data controls use the Order Details table for the RecordSource property because Order Details contains a sufficient number of records (2,155) to demonstrate the difference in performance between local and remote Recordsets
underlying bound controls.
Figure 20.18. A form with local and remote Data and DBGrid controls to compare performance.
The form shown in Figure 20.18 is included in the Loc_rem.vbp project in the \DDG_VB4\32_bit\Chaptr20 folder of the accompanying CD-ROM. Change the values of the DatabaseName property of both Data controls to point to the location of the native and linked versions of Northwind.mdb on your computer.
The following list describes the differences in behavior of local and remote DBGrid controls bound to Data controls whose RecordsetType property value is set to Dynaset:
The following list describes the local and remote performance differences you encounter when the value of the RecordsetType property of the Data control is set to Snapshot instead of to Dynaset:
As a rule of thumb, it's faster to work with Snapshot-type Recordsets if the number of rows is about 100 or less and the Recordset contains only a few columns. Therefore, Snapshot-type Recordsets usually are preferred for populating bound list boxes or
combo boxes.
To maximize performance, especially with remote data sources, specify in the SQL SELECT statement for the RecordSource property of the Data control only the columns (fields) you want to display or bind to the control. Connecting to a table or executing SELECT * FROM TableName may return a large amount of data you don't need.
The majority of client/server front-end applications are used for decision-support purposes. A typical example of a decision-support application is a front-end for checking the status of customer orders in response to telephone inquiries. Figure 20.19
shows a simple front-end for displaying orders placed by a customer whose name you pick from a bound combo box. The latest orders of the selected customer appear first in one DBGrid control. The line items appear in a second DBGrid control when you click
the record selector for a particular order.
Figure 20.19. A simple front-end application for decision support that uses bound controls.
The application of Figure 20.19 emulates real-world decision support forms that are designed to return a manageable set of records. The three Data controls are bound to Snapshot-type Recordsets for the following reasons:
[VB_NEW]The application of Figure 20.19 takes full advantage of Visual Basic 4.0's capability to change the values of Recordset-related properties of Data controls in code. The Local and Remote command buttons at the bottom of the form change the
database to which the controls are bound from the local to the remote version of Northwind.mdb. The capability to quickly switch change data sources lets you evaluate the relative performance of a local and remote data source. The code to implement the
application of Figure 20.19 appears in Listing 20.1. The SQL statements that populate the DBGrid controls are broken into multiple lines for readability. (You can't use VBA's code continuation pair to break literal strings.)
Listing 20.1. Code to display customer orders and select a local or remote data source.
Private Sub dbcCustomers_Change()
'Find latest orders for CustomerID picked in combo box
Dim strSQL As String
strSQL = "SELECT OrderID, OrderDate AS Ordered, "
strSQL = strSQL & "ShippedDate AS Shipped FROM Orders "
strSQL = strSQL & "WHERE CustomerID = '"
strSQL = strSQL & dbcCustomers.BoundText
strSQL = strSQL & "' ORDER BY OrderDate DESC"
dtcOrders.RecordSource = strSQL
dtcOrders.Refresh
End Sub
Private Sub dtcOrders_Reposition()
'Find detail records for order number clicked in grid
Dim strSQL As String
strSQL = "SELECT [Order Details].Quantity AS Quan, "
strSQL = strSQL & "Products.ProductName AS Product, "
strSQL = strSQL & "[Order Details].UnitPrice AS Price "
strSQL = strSQL & "FROM [Order Details], Products "
strSQL = strSQL & "WHERE OrderID = " & dtcOrders.Recordset(0)
strSQL = strSQL & " AND Products.ProductID = "
strSQL = strSQL & "[Order Details].ProductID"
dtcOrderDetails.RecordSource = strSQL
dtcOrderDetails.Refresh
End Sub
Private Sub cmdLocal_Click()
'Set database name to local .mdb file
Call SetDBName("E:\MSOffice\Access\Samples\Northwind.mdb")
End Sub
Private Sub cmdRemote_Click()
'Set database name to .mdb file with linked remote tables
Call SetDBName("E:\VB4\Northwind.mdb")
End Sub
Private Sub SetDBName(strDBN As String)
'Change DatabaseName property value of each data control
dtcOrders.DatabaseName = strDBN
dtcOrderDetails.DatabaseName = strDBN
dtcCustomers.Refresh
dtcOrders.Refresh
dtcOrderDetails.Refresh
End Sub
The SQL SELECT queries that populate the three bound controls return only the columns needed for display and record selection. The CustomerName column populates the combo box and the bound CustomerID column is used to select the orders displayed in the
first DBGrid control. Only three columns are needed to populate the two DBGrid controls. The first time you select a customer and order, there is a brief delay when using the remote data source. Once the data for the snapshots is cached locally, the
difference in application performance using a local or remote data source is almost indistinguishable.
The application of Figure 20.19 is included on the accompanying CD-ROM as PickList.vbp in the \DDG_VB4\32_bit\Chaptr20 directory. Change the value of the DatabaseName property of the bound controls and the arguments passed to the SetDBName procedure to match the location of the local and remote versions of Northwind.mdb on your computer. The SQL statement that appears in the RecordSource property of dtcOrderDetails is used to generate a design-time field list. The field list is needed in order to use the properties sheet of the DBGrid control to set the format of the Price column to Currency. Unlike Access's datasheet, the DBGrid control does not automatically format with dollar signs and trailing zeroes columns of the Currency field data type.
You can connect directly to an ODBC data source as an alternative to linking the remote tables to a local Jet .mdb database. Using a direct ODBC connection does not result in measurably improved performance and you can't take advantage of local
"fake" Jet indexes to make unindexed server tables updatable. (Unindexed server tables are uncommon, however, so the need for local "fake" indexes seldom is necessary.) You also can't take advantage of the capability to alias linked
table names.
To test your Northwind SQL Server ODBC data source with 32-bit VisData, follow these steps:
When you use Access to export a Jet table to a client/server database, Jets does not create indexes on the remote table. You need to use the SQL CREATE INDEX statement or use the SQL Server administrative tools to manually add indexes to the
client/server table. You can add one clustered or as many non-clustered indexes as you want to a table with SQL Server 4.2+'s SQL Object Manager or SQL Server 6.x's SQL Enterprise Manager. (SQL Object Manager and SQL Administrator also work with SQL
Server 6.x.) Clustered indexes, which physically place the pages of the table in the index order, greatly improve the performance of SQL Server databases. However, you gain only a performance improvement with a clustered index if you specify that
the index must be unique. (Clustered indexes are not unique by default.)
To use SQL Object Manager to create a clustered primary key index on the Order_Details table or any other table in an SQL Server database, follow these steps:
You can verify that the index is present and is recognized by the SQL Server ODBC driver by reopening the dbo.Order_Details table in VisData and inspecting the last record to determine if a Recordset of the Dynaset type created over the table is
updatable. Figure 20.27 demonstrates that Jet recognizes the newly-created PrimaryKey index. The PrimaryKey index and its properties appear at the bottom of the Table Structure dialog (see Figure 20.28.)
Figure 20.28. Displaying the properties of an index on a remote table.
Opening tables and manipulating client/server table data through the ODBC API is inherently faster than performing the same operations with the Jet database engine. This is because the Jet engine, the ODBC Driver Manager, and the ODBC driver constitute
a series of separate layers, related to the layers of OSI network protocols described in the preceding chapter, through which each instruction must pass. In addition, Jet's query parser must process all queries written in Jet SQL. (Jet only parses the
query once, however, if you use a persistent QueryDef object; therefore, using a parameterized QueryDef object is faster than executing a custom SQL statement each time you run a query.)
Most Visual Basic 4.0 and many Access production applications that connect to client/server databases with the ODBC API employ the SQL passthrough method to create Recordset objects of the Snapshot type based on SQL SELECT statements. SQL passthrough
sends the SQL statement, verbatim, directly to the server. The server executes the query and returns only the specified rows to create the Recordset object in your Visual Basic 4.0 application. Thus, the SQL passthrough method improves performance for the
following reasons:
The conditions under which the Jet database engine processes locally parts of SQL queries intended for client/server RDBMSs is one of the subjects of the "JET Database Engine ODBC Connectivity White Paper," described in the "Connections, Threads, and Symmetrical Multiprocessing" section earlier in this chapter.
To use SQL passthrough, you set bit 7 (decimal 64) of the masked intOptions argument of the OpenRecordset method with the dbSQLPassThrough constant, as in the following example:
Set rssQuery = dbNwind.OpenRecordset(strSQL, _ dbOpenSnapshot, dbSQLPassThrough)
The value of strSQL is a string that contains the SQL SELECT statement you want the server to execute in the dialect of SQL supported by the server, not Jet SQL. To execute a SQL passthrough action query, such as an INSERT, UPDATE, or DELETE operation,
invoke the Execute method, as in the following example:
dbNwind.Execute(strSQL, dbSQLPassThrough)
As noted in Chapter 5, "Learning Structured Query Language," each server back-end has its own flavor of ANSI or pseudo-ANSI SQL. Therefore, it is likely that you must rewrite any SQL statements that previously were sent to the Jet database
engine in another dialect of SQL to make your SQL passthrough queries run without choking on special Jet SQL syntax. Chapter 22 provides examples of passthrough queries that use SQL Server's Transact-SQL dialect.
The SQL passthrough option of the OpenRecordset method also can be used with the appropriate SQL syntax to execute stored procedures contained in the server database. The syntax for executing stored procedures with SQL statements differs with each
RDBMS that supports stored procedures. SQL Server stored procedures are one of the subjects of Chapter 22.
This chapter emphasized that creating Visual Basic 4.0 client/server, front-end applications does not differ substantially from the design of similar applications that connect to conventional desktop databases with the Jet database engine. The
principal issues for the Visual Basic 4.0 database developer are setting up the server database and the ODBC data source(s) for the client/server back-end. Thus, much of this chapter was devoted primarily to a discussion of the characteristics of a
powerful client-server RDBMS, Microsoft SQL Server versions 4.2 and higher, and the version 2.5 of the Microsoft 32-bit ODBC API. The chapter concluded with a brief description of how you use the OpenRecordset and Execute methods with the SQL passthrough
option to improve the performance of client-server database front-ends that use multiple-tier ODBC drivers.
The next chapter describes how to create mail-enabled Visual Basic applications that use the MAPI OLE control. Applications that take advantage of use the Schedule+ Object Library also are discussed.
