Remote Automation is the method employed by Visual Basic 4.0 to permit OLE Automation client applications to access out-of-process OLE Automation servers over a network connection. Prior to the introduction of Visual Basic 4.0's Remote Automation
features, in-process and out-of-process OLE Automation servers were required to reside on the same PC as the OLE Automation client application. Remote Automation makes implementation of distributed three-tier client/server database applications possible.
Remote Automation is an interim technology. When Visual Basic 4.0 was released, Microsoft unofficially called Remote Automation "NetworkOLE 0.9." When this edition was written, Microsoft had abandoned NetworkOLE terminology in favor of the
Distributed Common Object Model (DCOM). DCOM, which is expected to be included in Windows NT 4.0, also called the Shell Update Release (SUR), offers many improvements to the Remote Automation methodology of Visual Basic 4.0. In particular, DCOM is slated
to provide substantially improved security features and more efficient Remote Procedure Call (RPC) transport than Remote Automation. DCOM is expected to play a major role in making corporate intranets faster and more efficient. Fortunately, the
out-of-process OLE servers you write for use with Visual Basic 4.0's Remote Automation features are operable in the DCOM environment with little or no modification.
This chapter describes the theory of Remote Automation Objects (RAOs); where RAOs are most useful; and how to write and deploy RAOs on Windows NT Server 3.51+, and on Windows 3.1+, Windows 95, and Windows NT Workstation 3.51+ clients. The chapter also
describes how to use the utilities included with the Enterprise Edition of Visual Basic 4.0 for registering, managing, and cataloging RAOs.
Remote Automation relies on a Remote Automation proxy and Remote Automation stub to provide network communication between the OLE Automation client and server. The fact that the OLE Automation server is "remoted" is transparent to the client.
The Remote Automation proxy takes the place of the OLE proxy that's used by local OLE Automation clients to communicate by Lightweight Remote Procedure Calls (LRPCs). Figure 23.1 compares the conventional OLE proxy and OLE stub structure for local servers
with the architecture of RAOs.
The following two sections describe the individual components that implement the Visual Basic 4.0 version of Remote Automation and the alternatives for operational implementation of RAOs.
Visual Basic 4.0 supports 16-bit Remote Automation clients running under Windows 3.1+, 16-bit or 32-bit clients on Windows 95 and Windows NT, and 32-bit Remote Automation servers on Windows 95 and Windows NT. Following is a list of the basic components
that make up Visual Basic 4.0's current (pre-DCOM) implementation Remote Automation:
Figure 23.2 illustrates three different Remote Automation client configurations operable with a single Remote Automation server. RACMGR16.EXE and Racmgr32.exe appear in parentheses in the client elements of Figure 23.2 because Remote Automation
Connection Managers are options for Remote Automation clients. It is unlikely that Windows 95 will find use as a production Remote Automation server; Windows 95 is included in Figure 23.2 as a Remote Automation server option only for completeness. (If you
don't have Windows NT Server 3.51+, you can test Remote Automation examples with networked Windows 95 PCs.)
Figure 23.2. Typical client configurations and the standard server configuration for implementing Remote Automation.
The conventional method of interaction between Remote Automation clients and servers is synchronous. In synchronous mode, the client/server interaction occurs as follows:
Synchronous mode duplicates the process by which local servers communicate with OLE Automation clients. Thus, you can "remote" a local server without making any changes to the server code.
Do not include visible user interface objects, such as dialogs or message boxes in Remote Automation servers. Such visible objects appear on the server's display, not on the client's. To create reasonably bulletproof RAOs, all runtime errors must be trapped and you must implement a means to communicate error information to (and handle error processing in) your client application.
Asynchronous mode lets the server process requests while the client continues to execute code. One of the complaints of Visual Basic developers is the inability of Visual Basic to handle Windows API callback functions. Remote Automation, however, lets
a client pass a callback function to the server before applying an object method to start server processing. The server accepts the property values or method argument values and then returns control to the client. Asynchronous mode is especially useful
with Remote Automation servers that perform large-scale database tasks, such as acquiring and relating data from multiple data services. Asynchronous Remote Automation mode follows this route:
Asynchronous mode involves more complex code and eliminates the flexibility of passing property values as arguments of the calling method. However, you can pass property values as values of arguments of the callback method. If your client application
can accomplish useful work during the server processing interval, use of asynchronous mode usually is worth the additional code and testing effort. Otherwise, the more straightforward synchronous mode is adequate.
The majority of Visual Basic 4.0 Remote Automation applications involve databases. This isn't a surprising observation because the majority of all Visual Basic applications involve databases. The "Defining the Role of OLE Automation Servers"
section of Chapter 16 describes the basic reasons for using OLE Automation servers in database applications. Following are the primary advantages of moving from local to Remote Automation servers:
You must have at least as many licenses for Access 95 as the number of simultaneous instances of clients using Access 95 on the server. There are a variety of licensing terms for server-based Microsoft Office 95 components. Consult your license agreement to determine licensing requirements for Access 95 or other Office 95 applications running on servers.
The sections that follow describe the two most commonly used architectures for Remote Automation operations.
The simplest implementation of Remote Automation is a two-tier architecture in which the RAOs and the RDBMS reside on the same remote PC. Typically, two-tier architecture might be used for online transaction processing (OLTP) with a Jet 3.0 .mdb
database located on the same server as the RAOs. Another application for two-tier architecture, illustrated by Figure 23.3, is use of a remote copy of Access 95 for printing reports, as described in the preceding section. In either case, a copy of Jet 3.0
must reside on the server.
Figure 23.3. Two-tier Remote Automation architecture for printing reports with a server-based copy of Access 95.
True three-tier architecture requires that the RAOs be installed on an application server that is separate from the application server(s) for the database(s) accessed by the RAO. The primary advantages of three-tier architecture are as follows.
Figure 23.4 is an illustration of three-tier client/server architecture with user services (clients), business services (RAOs that connect to the Jet 3.0 DAO and Visual Basic's RDO 1.0, both of which use 32-bit ODBC drivers), and data services provided
by a heterogeneous set of client/server and mainframe RDBMSs that support ODBC, either directly or through a database gateway.
Figure 23.4. Three-tier architecture for distributed, heterogeneous client/server and mainframe RDBMs.
Order entry is one of the most common OLTP database front-end applications and is a prime candidate for Remote Automation. Sophisticated order entry front-ends are likely to involve complex business services, such as the following:
Order entry front-ends that require extensive business services often must connect to multiple databases. Customer billing and accounts receivable data is likely to be stored in a financial database, inventory data in a warehouse database, and
production or vendor delivery schedules in a manufacturing database. Thus, a three-tier architecture that abstracts the necessary order entry data from multiple databases is ideally suited for complex order processing systems.
Remote Automation's asynchronous mode is especially effective for telephone order entry systems. As an example, when the order entry operator identifies the customer, a remote credit check object can be used to determine payment history and credit
limits while the operator continues to input telephone order information. If the running subtotal of the order amount exceeds the customer's credit limit, the operator can advise the customer before entering additional line items. Asynchronous mode also is
useful for testing inventory levels as the operator enters individual line items with item number and quantity ordered values.
Creating complex, special-purpose Visual Basic 4.0 order entry front-ends is beyond the scope of this book. Most sophisticated order entry applications contain thousands of lines of code, most of which are devoted to implementing a specific set of
business services. The sections that follow describe the process of "remoting" the order entry component of an existing, relatively simple OLTP application. The principles outlined in these sections, however, are applicable to a wide variety of
OLTP front-ends.
The Northwind Traders Client/Server Order Entry System (Msa7oltp.mdb) is an Access 95 OLTP application designed to compare the performance of conventional shared-file and client/server front-ends. Msa7oltp first appeared at Microsoft's 1994 Tech*Ed
conference as an Access 2.0 application (MSA_OLTP.MDB) to demonstrate the use of Access Basic code-behind-forms and SQL passthrough to Microsoft SQL Server. Subsequently, MSA_OLTP.MDB was converted to the 32-bit Access 95 version, Msa7oltp.mdb, which uses
tables attached from Msa7data.mdb (data from Access 2.0's NWIND.MDB sample database converted to Access 95's Jet 3.0 format.)
Msa7oltp.mdb and Msa7data.mdb are located in the \DDG_VB4\32_bit\Chaptr23 folder of the accompanying CD-ROM. You must have Access 95 to run this application. If you locate Msa7data.mdb in a folder other than C:\DDG_VB4\32_bit\Chaptr23, use Access 95's Linked Table Manager to refresh the links to the attached tables. To use SQL passthrough to Microsoft SQL Server 4.21+, export the tables in Msa7data.mdb to a new SQL Server database, nwind, and then create a new SQL Server ODBC data source, NWIND. The Access 95 Upsizing Wizard, available free from http://www.microsoft.com, makes exporting data from Access 95 tables easy. If you don't use the Upsizing Wizard, add clustered (primary key) indexes to the SQL Server tables to improve performance.
Figure 23.5 shows the single data entry form (frmOrderEntry) of Msa7otlp.mdb. The application is designed for heads-down telephone order entry using the keyboard only. (All fields and command buttons have accelerator keys, so use of a mouse is
optional). To find an existing customer, you type one or more beginning letters of the customer name in the Bill To text box and press Return. If more than one customer name matches the search entry, you select the customer name from the list box that
opens at the lower left of the form and press Return. After the customer is found, a list of the customer's orders appears (last order first) in the lower right list box. The Show Detail command button opens a multicolumn list box that displays the line
items that comprise an existing order. (See Figure 23.6.)
Figure 23.6. The Show Detail command button displays the line items that comprise an existing order.
The New Order command button opens a subform whose data source is tblOrder_Details, a local table that holds a temporary set of records representing the line items of the order. The operator enters quantity and item numbers; on exiting the item number
field, the remainder of the line item data is filled from the appropriate record of the Products table. (You also can enter one or more of the initial letters of the Product Name field to find a product by name.) The Extended amount is calculated by
multiplying Quan(tity) by Unit (Price) and applying the Disc(ount), if any (see Figure 23.7). Extended amounts are not stored in the tblOrder_Details table. The Add New Order button starts a transaction that obtains the next order number, then INSERTs the
order in the Orders table, followed by an INSERT of each line item to the Order_Details table. If the COMMIT operation on the transaction is successful, the temporary records in tblOrder_Details are deleted and a multicolumn list box displays the line
items comprising the final order. The times to obtain the next order number, set up the transaction, and commit the transaction appear at the bottom of the form (see Figure 23.8). Listing 23.1 shows the Access VBA code that processes the Add New Order
transaction.
Figure 23.7. Adding line items to a new order in the sbfOrder_Details subform.
Figure 23.8. The frmOrderEntry form confirming the line items of a newly added order.
Listing 23.1. Access VBA code to add a new order to attached Jet 3.0 tables or by SQL passthrough to SQL Server tables.
Private Sub AddNewOrder()
'Purpose: Add a new order to the Orders table and _
'line items to the Order Details table
On Error GoTo AddOrderError
Dim rsLineItems As Recordset
Dim strMsg As String
Dim strSQL As String
Dim lngOrder_ID As Long
Dim sngStart As Single
Dim sngGetID As Single
Dim sngWrap As Single
Dim sngCommit As Single
On Error GoTo AddOrderError
sngStart = Timer
If fPassthru Then
'Use SQL passthrough to SQL Server
On Error GoTo PassthroughError
'Obtain the tentative order ID
strSQL = "SELECT MAX(Order_ID) Last_Order FROM Orders"
Set qdfOrderID = dbCurrent.OpenQueryDef("qdfOrderID")
qdfOrderID.Connect = strConnect
qdfOrderID.SQL = strSQL
qdfOrderID.ReturnsRecords = True
Set rsOrders = dbCurrent.OpenRecordset("qdfOrderID")
lngOrder_ID = rsOrders.Last_Order + 1
txtOrder_ID.Enabled = True
txtOrder_ID.Value = lngOrder_ID
sngGetID = Timer - sngStart
sngStart = Timer
'INSERT the order information (one _very_ long SQL statement)
strSQL = "BEGIN TRAN INSERT Orders VALUES(" & _
txtOrder_ID & ", '"
strSQL = strSQL & txtCustomer_ID & "', "
strSQL = strSQL & cboEmployee_ID & ", '"
strSQL = strSQL & txtShip_Name & "', '"
strSQL = strSQL & txtShip_Address & "', '"
strSQL = strSQL & txtShip_City & "', '"
strSQL = strSQL & txtShip_Region & "', '"
strSQL = strSQL & txtShip_Postal_Code & "', '"
strSQL = strSQL & txtShip_Country & "', "
strSQL = strSQL & cboShip_Via & ", '"
strSQL = strSQL & txtOrder_Date & "', '"
strSQL = strSQL & txtRequired_Date & "', "
strSQL = strSQL & "null, null) "
'INSERT the line item information for each record
rsLineItems.MoveFirst
Do Until rsLineItems.EOF
strSQL = strSQL & "INSERT Order_Details VALUES(" & _
lngOrder_ID & ", "
strSQL = strSQL & rsLineItems!Product_ID & ", "
strSQL = strSQL & rsLineItems!Unit_Price & ", "
strSQL = strSQL & rsLineItems!Quantity & ", "
strSQL = strSQL & rsLineItems!Discount & ") "
rsLineItems.MoveNext
If rsLineItems.EOF Then
'Finish the transaction
strSQL = strSQL & "COMMIT TRAN"
End If
Loop
sngWrap = Timer - sngStart
sngStart = Timer
'Execute the SQL statement
Set qdfNewOrder = dbCurrent.OpenQueryDef("qdfNewOrder")
qdfNewOrder.Connect = strConnect
qdfNewOrder.SQL = strSQL
qdfNewOrder.ReturnsRecords = False
qdfNewOrder.Execute
sngCommit = Timer - sngStart
Else
'Use attached Msa7data.mdb tables
'Obtain a tentative order ID
lngOrder_ID = DMax("Order_ID", "Orders") + 1
txtOrder_ID.Enabled = True
txtOrder_ID.Value = lngOrder_ID
sngGetID = Timer - sngStart
sngStart = Timer
'Add order and detail items as a transaction
On Error GoTo RollbackOrder
wsCurrent.BeginTrans
'Add the order to the Orders table
rsOrders.AddNew
rsOrders!Order_ID = lngOrder_ID
rsOrders!Customer_ID = txtCustomer_ID
rsOrders!Employee_ID = cboEmployee_ID
rsOrders!Ship_Name = txtShip_Name
rsOrders!Ship_Address = txtShip_Address
rsOrders!Ship_City = txtShip_City
rsOrders!Ship_Region = txtShip_Region
rsOrders!Ship_Postal_Code = txtShip_Postal_Code
rsOrders!Ship_Country = txtShip_Country
rsOrders!Ship_Via = cboShip_Via
rsOrders!Order_Date = txtOrder_Date
rsOrders!Required_Date = txtRequired_Date
'Following fields are not updated for a new order
'rsOrders!Shipped_Date = txtShipped_Date
'rsOrders!Freight = txtFreight
rsOrders.UPDATE
'Add the line item(s) to the Order Details table
rsLineItems.MoveFirst
Do Until rsLineItems.EOF
rsOrderDetails.AddNew
rsOrderDetails!Order_ID = lngOrder_ID
rsOrderDetails!Product_ID = rsLineItems!Product_ID
rsOrderDetails!Quantity = rsLineItems!Quantity
rsOrderDetails!Unit_Price = rsLineItems!Unit_Price
rsOrderDetails!Discount = rsLineItems!Discount
rsOrderDetails.UPDATE
rsLineItems.MoveNext
Loop
sngWrap = Timer - sngStart
sngStart = Timer
wsCurrent.CommitTrans
sngCommit = Timer - sngStart
End If
On Error GoTo AddOrderError
'Prevent further editing
Call LockOrderData
'Display the order details in the listbox
Call cmdShowDetail_Click
'Display the transaction times
Call HideTimerData
txtGetID.Value = sngGetID
txtWrap.Value = sngWrap
txtCommit.Value = sngCommit
Call ShowTimerData
DoCmd.Hourglass False
fNewOrder = False
Exit Sub
PassthroughError:
DoCmd.Hourglass False
MsgBox Error$, 48, "Unable to Add Order " & lngOrder_ID
Exit Sub
RollbackOrder:
DoCmd.Hourglass False
MsgBox Error$, 48, "Unable to Add Order " & lngOrder_ID
wsCurrent.Rollback
cmdAddNewOrder.Enabled = True
cmdCancelNewOrder.Enabled = True
Exit Sub
AddOrderError:
DoCmd.Hourglass False
MsgBox Error$, 48, "Order Entry Error"
Exit Sub
End Sub
Field entry completion and form housekeeping code in the preceding AddNewOrder subprocedure has been removed to conserve space.
The candidate code for a Remote Automation Object is the SQL Server transaction processing code between the If fPassthru Then and Else statements. In order for readers without Microsoft SQL Server to test the RAO, both the SQL
passthrough and Jet 3.0 database processes are included in the RAO. The sections that follow describe the process for creating an OrderServer.Order class with an AddNewOrder method, testing the OrderServer.Order class as a local OLE Automation server, then
converting the OrderServer.Order class to a RAO, the RemoteServer.Order class. (Fully qualified object names allow use of two versions of the Order.AddNewOrder method for testing.
The "Creating OLE Servers" section of the Professional Features manual for Visual Basic 4.0 and the Building Client/Server Applications with Visual Basic manual that accompanies the Enterprise Edition provide the
guidelines for creating local and remote OLE Automation servers. Many Visual Basic 4.0 OLE Automation servers, however, are likely to be derived from existing Visual Basic 3.0/4.0 or Access database applications, as previously observed in Chapter 16.
Following is a list of the primary considerations in determining a conversion strategy for creating Remote Automation servers for Visual Basic database applications:
The preceding design recommendations apply equally to new object classes you create or code in existing applications you convert to RAOs. It's especially important to observe these recommendations during a conversion process, because moving blocks of
existing code to an OLE Automation server is a deceptively simple process. Simply wrapping a block of code as a class (without optimizing the code for use as a Remote Automation server) can lead to excessive network traffic and very poor performance.
Following is a list of the suggested steps for creating a Remote Automation OLTP server from existing code:
The preceding sequence of steps is used to create the TstOrder.vbp, OrderSvr.vbp, and OrderRem.vbp projects described in the following sections.
The TstOrder.vbp application provides the order data used to test the internal AddTestOrder function and the AddNewOrder method of the Orders object exposed by the local and remote server. Figure 23.9 shows the test application adding orders to the
NWIND SQL Server data source for the SQL Server 6.5 nwind database that contains tables exported from Msa7data.mdb. You can run TstOrder.vbp independently of OrderSvr.vbp or the Remote Automation server you create from OrderRem.vbp. Click the Get Order
Data button to obtain the sample data and then click Internal Function Test button to run the internal function code that is converted to a method of the Order class. The values in the three text boxes are used to compare the speed of operation of the
three different implementations of the new order entry code.
The TstOrder.vbp project is located in the \DDG_VB4\32_bit\Chaptr23 folder of the accompanying CD-ROM. To run TstOrder.vbp with Msa7data.mdb, the database must be in the same folder as the files of TstOrder.vbp. To run TstOrder.vbp with the SQL Server NWIND data source in SQL passthrough mode, you must create the nwind database, export the tables in Msa7data.mdb to the nwind database, then create the NWIND 32-bit ODBC data source.
Listing 23.2 shows the three most important subprocedures of the frmTestOrd form. The Form_Load event handler creates a Recordset of the Snapshot type over the Customers and tblOrder_Details tables. The cmdInternalTest_Click event handler picks a
random Customer_ID value, then creates a Recordset of the orders for the customer to provide the field test values for records added to the Orders table. The tblOrder_Details table has 10 line item records to append to the Order_Details table with each new
order added. The most important change to the original code of Listing 23.1 is that in the cmdInternalTest_Click event handler for the creation of Variant arrays to hold the field values for the records added to the Orders (order header) and
Order_Details (line items) tables.
Listing 23.2. The three primary subprocedures of the frmTstOrder form.
Private Sub Form_Load()
'Open the Customers and tblOrder_Details tables
strConn = App.Path & "\Msa7data.mdb"
Set dbCurrent = DBEngine.Workspaces(0).OpenDatabase(strConn)
Set rsCustomers = dbCurrent.OpenRecordset("Customers", _
dbOpenSnapshot)
rsCustomers.MoveLast 'To get valid RecordCount
Set rsItems = dbCurrent.OpenRecordset("tblOrder_Details", _
dbOpenSnapshot)
rsItems.MoveLast 'To get valid RecordCount
End Sub
Private Sub cmdGetData_Click()
Do While True
'Pick a random customer
Randomize
rsCustomers.PercentPosition = 100 * Rnd()
txtCustomer.Text = rsCustomers!Company_Name
'Find orders for random customer
strSQL = "SELECT * FROM Orders WHERE Orders.Customer_ID = '"
& rsCustomers!Customer_ID & "'"
Set rsCustOrders = dbCurrent.OpenRecordset(strSQL, _
dbOpenDynaset)
If rsCustOrders.RecordCount > 0 Then
'Get the actual record count
rsCustOrders.MoveLast
'Pick a random order
rsCustOrders.PercentPosition = 100 * Rnd()
Exit Do
End If
Loop
'Create a Variant array of order field values
ReDim avarOrder(rsCustOrders.Fields.Count - 1)
For intCtr = 0 To rsCustOrders.Fields.Count - 1
avarOrder(intCtr) = rsCustOrders(intCtr)
Next intCtr
'Create a two-dimension Variant array of line items to add
intRows = rsItems.RecordCount - 1
'Don't include the Product Name, Quantity per Unit,
'or Extended columns
intCols = rsItems.Fields.Count - 4
ReDim avarItems(intRows, intCols)
rsItems.MoveFirst
For intRow = 0 To intRows
'Iterate through the rows and columns
For intCol = 0 To intCols
avarItems(intRow, intCol) = rsItems(intCol)
Next intCol
rsItems.MoveNext
If rsItems.EOF Then
'Safety valve
Exit For
End If
Next intRow
rsCustOrders.Close
Set rsCustOrders = Nothing
cmdInternalTest.Enabled = True
cmdLocalSvrTest.Enabled = True
cmdRemoteSvrTest.Enabled = True
End Sub
Private Sub cmdInternalTest_Click()
'Use the AddTestOrder function in modTstOrder
txtOrder_ID.Text = AddTestOrder(strConn, avarOrder, _
avarItems, fPassthru, sngGetID, sngWrap, sngCommit)
cmdInternalTest.Enabled = False
'Display the execution times
txtGetID.Text = Format$(sngGetID, "0.000")
txtWrap.Text = Format$(sngWrap, "0.000")
txtCommit.Text = Format$(sngCommit, "0.000")
End Sub
Listing 23.3 shows the initial version of the code that ultimately is used to implement the AddNewOrder method of the Order class. No error trapping is provided in the AddTestOrder function in order to aid in diagnosing errors during the debugging
process. With an internal function, all variables can be passed ByRef (the default), so the parameters of the function do not include a ByRef or ByVal prefix. The Ubound function is used to determine the upper limits of the
arrays; using Ubound (instead of literal numbers) makes the code more generic.
Listing 23.3. The initial (internal) test version of the AddNewOrder method of the Orders class.
Public Function AddTestOrder(strConn As String, _
avarOrder As Variant, _
avarItems As Variant, _
fUseSQL As Boolean, _
sngGetID As Single, _
sngWrap As Single, _
sngCommit As Single) As Long
Dim dbRemote As Database
Dim wsRemote As Workspace
Dim rsOrders As Recordset
Dim rsOrderDetails As Recordset
Dim lngOrder_ID As Long
Dim strSQL As String
Dim rsOrder_ID As Recordset
Dim intCtr As Integer
Dim intRow As Integer
Dim intCol As Integer
Dim intPos As Integer
Set wsRemote = DBEngine.Workspaces(0)
sngGetID = Timer
If fUseSQL Then
Set dbRemote = wsRemote.OpenDatabase("", False, _
False, strConn)
'Find the last order number
strSQL = "SELECT MAX(Order_ID) Last_Order FROM Orders"
Set rsOrder_ID = dbRemote.OpenRecordset(strSQL, _
dbOpenSnapshot, dbSQLPassThrough)
'Increment the last order number
lngOrder_ID = rsOrder_ID(0) + 1
If lngOrder_ID > 1 Then
sngGetID = Timer - sngGetID
sngWrap = Timer
'INSERT the order information (one very long
'SQL statement)
strSQL = "BEGIN TRAN INSERT Orders VALUES(" & _
lngOrder_ID & ", "
'Don't insert Shipped_Date or Freight
For intCtr = 1 To UBound(avarOrder) - 2
If VarType(avarOrder(intCtr)) = vbDate _
Or VarType(avarOrder(intCtr)) = vbString Then
'Two consecutive single quotes for "'"
If VarType(avarOrder(intCtr)) = vbString _
And InStr(avarOrder(intCtr), "'") Then
intPos = InStr(avarOrder(intCtr), "'")
'The following substitutes "''" for "'"
avarOrder(intCtr) = Left(avarOrder(intCtr), _
intPos) & Mid(avarOrder(intCtr), intPos)
End If
'Use single quotes for dates and strings
strSQL = strSQL & "'" & avarOrder(intCtr) & "', "
ElseIf VarType(avarOrder(intCtr)) = vbNull Then
strSQL = strSQL & "null, "
Else
strSQL = strSQL & avarOrder(intCtr) & ", "
End If
Next intCtr
strSQL = strSQL & "null, null) "
'INSERT the line item information for each record
For intRow = 0 To UBound(avarItems, 1)
strSQL = strSQL & "INSERT Order_Details VALUES(" & _
lngOrder_ID & ", "
For intCol = 1 To UBound(avarItems, 2)
If intCol = UBound(avarItems, 2) Then
strSQL = strSQL & avarItems(intRow, intCol) & ") "
Else
strSQL = strSQL & avarItems(intRow, intCol) & ", "
End If
Next intCol
Next intRow
strSQL = strSQL & "COMMIT TRAN"
sngWrap = Timer - sngWrap
sngCommit = Timer
'Execute the SQL statement
dbRemote.Execute strSQL, dbSQLPassThrough
Else
'Error condition
End If
Else
Set dbRemote = wsRemote.OpenDatabase(strConn)
Set rsOrders = dbRemote.OpenRecordset("Orders", _
dbOpenDynaset)
Set rsOrderDetails = dbRemote.OpenRecordset("Order Details", _
dbOpenDynaset)
'Get the last order number and increment
rsOrders.MoveLast
lngOrder_ID = rsOrders(0) + 1
sngGetID = Timer - sngGetID
sngWrap = Timer
'Add order and detail items as a transaction
wsRemote.BeginTrans
'Add the order record to the Orders table
rsOrders.AddNew
'Add the new Order_ID
rsOrders(0) = lngOrder_ID
'Add the field values
For intCtr = 1 To UBound(avarOrder)
rsOrders(intCtr) = avarOrder(intCtr)
Next intCtr
'Update the values
rsOrders.Update
'Add the line item(s) to the Order Details table
For intRow = 0 To UBound(avarItems, 1)
'New row
rsOrderDetails.AddNew
'Add the Order_ID as column 0
rsOrderDetails(0) = lngOrder_ID
'Add the valid field values
For intCol = 1 To UBound(avarItems, 2)
rsOrderDetails(intCol) = avarItems(intRow, intCol)
Next intCol
rsOrderDetails.Update
Next intRow
sngWrap = Timer - sngWrap
sngCommit = Timer
wsRemote.CommitTrans
Set rsOrders = Nothing
Set rsOrderDetails = Nothing
End If
Set dbRemote = Nothing
Set wsRemote = Nothing
sngCommit = Timer - sngCommit
AddTestOrder = lngOrder_ID
End Function
Following is a typical Transact-SQL statement created by the preceding code when adding an order to the SQL Server nwind database. An ellipsis (. . .) replaces the INSERT . . . VALUES statements for line items 3 through 9. Use of the VALUES function
requires a value for each field; null is used if no field value is supplied. Using the VALUES function is faster than specifying multiple field names and their values.
BEGIN TRAN
INSERT Orders
VALUES(11057, 'OTTIK', 2, 'Ottilies Käseladen',
'Mehrheimerstr. 369', 'Köln', null, '50739',
'Germany', 1, '3/2/92', '3/16/92', null, null)
INSERT Order_Details
VALUES(11057, 1, 18, 10, 0.05)
INSERT Order_Details
VALUES(11057, 2, 19, 5, 0)
...
INSERT Order_Details
VALUES(11057, 10, 31, 10, 0.15)
COMMIT TRAN
Relatively few but important changes to the code for the internal AddTestOrder function are required to add error handling and specify ByVal and ByRef handling of parameters for the AddNewOrder method of the Order class. Listing 23.4
shows only the changes to the code for the AddTestOrder function of Listing 23.3 required to minimize marshalling operations, accommodate Variant arrays, and provide primitive error handling. Code duplicated in Listing 23.3 is replaced by ellipses
(. . .) to conserve space. Figure 23.10 shows Object Browser displaying the syntax of the AddNewOrder method. ByVal and ByRef prefixes do not appear in Object Browser's syntax listing.
If you do not prefix the avarOrder and avarItems parameters with ByRef, you receive an Invalid procedure call error when executing the subprocedure that creates an instance of the Order object. The ByVal prefix is not optional for multidimensional Variant arrays.
Listing 23.4. Changes to the initial (internal) test code of Listing 23.3 to create the AddNewOrder method of the Orders class.
Public Function AddNewOrder(ByVal strConn As String, _
ByVal avarOrder As Variant, _
ByVal avarItems As Variant, _
ByVal fUseSQL As Boolean, _
ByRef sngGetID As Single, _
ByRef sngWrap As Single, _
ByRef sngCommit As Single, _
ByRef lngErrNum As Long, _
ByRef strError As String) As Long
... 'Dim statements
'Provide error handler
On Error GoTo AddNewOrderErr
... 'Order processing code
Exit Function
AddNewOrderErr:
'Primitive error handling
lngErrNum = Err.Number
strError = Err.Description
Set dbRemote = Nothing
Set wsRemote = Nothing
Exit Function
End Function
The code for the OrderSvr.vbp project is located in the same folder of the CD-ROM as TstOrder.vbp. Compile and run OrderSvr.vbp in a separate instance of 32-bit Visual Basic 4.0. Click the Get Order Data button of TstOrder.vbp and then click Local Server Test to check performance of the local server.
Figure 23.10. Object Browser displaying the syntax for the AddNewOrder method of the Order class.
You must add a reference to the Microsoft DAO 3.0 Object Library or the Microsoft Remote Data Object 1.0 to OLE Automation servers that connect to databases.
Listing 23.5 shows the code of the cmdLocalSvrTest_Click event handler that creates a local instance of the Orders object when you apply the AddNewOrder function. Calling a method, or getting or setting a property value, of an object specified by the
Dim objName As New ObjectClass is required to instantiate the object.
Listing 23.5. The cmdLocalSvrTest_Click subprocedure for testing the local server version of the AddNewOrder method of the Order class.
Private Sub cmdLocalSvrTest_Click()
'Test the local OLE Automation server (OrderSvr.vbp)
Dim objNewOrder As New OrderServer.Order
'Instantiate the Order object with the method call
txtOrder_ID.Text = objNewOrder.AddNewOrder(strConn, _
avarOrder, avarItems, fPassthru, sngGetID, _
sngWrap, sngCommit, lngErrNum, strError)
cmdLocalSvrTest.Enabled = False
txtGetID.Text = Format$(sngGetID, "0.000")
txtWrap.Text = Format$(sngWrap, "0.000")
txtCommit.Text = Format$(sngCommit, "0.000")
Set objNewOrder = Nothing
End Sub
Deploying a Remote Automation server and setting up client applications that use the remote server is a relatively simple task if you use Visual Basic 4.0 Setup Wizard to create a Setup program that automatically supplies the files necessary to
implement Remote Automation. Use of the Setup Wizard for conventional Visual Basic 4.0 applications is described in Chapter 27, "Making Distribution Diskettes with the Visual Basic Setup Kit." The required auxiliary files are described the
"Remote Automation Components" section, near the beginning of this chapter. You first install the Remote Automation server on the application server and then create the setup files installation of the client application on users' workstations.
The sections that follow describe the process for deploying the remote version (OrderRem.vbp) of the local server (OrderSvr.vbp) described in the preceding sections.
OrderRem.vbp is created by opening a new 32-bit Visual Basic 4.0 project, removing the Form1 file, and adding the clsOrder.cls and modMain.bas to the project. The value of the Project Name property of OrderRem.vbp is RemoteServer, in order to distinguish between the remote version of the Order class (RemoteServer.Order) and the local version (OrderServer.Order).
The TstOrder.vbp client lets you compare the performance of three different architectures with Jet 3.0 and SQL Server databases. Minor modifications to the cmdLocalSvrTest_Click event handler are required to create the cmdRemoteSvrTest_Click
subprocedure (see Listing 23.6). The most important change is the connection string (strRemConn) for the Jet 3.0 database. You must specify explicitly the location of Msa7data.mdb on the server; the App.Path statement does not work in this case, because
App.Path returns the location of the client application. The cmdRemoteSvrTest_Click event handler also includes a message box to report errors returned from the server.
Listing 23.6. Revisions to the cmdRemoteSvrTest_Click event handler to specify .mdb file location and to provide error reporting.
Private Sub cmdRemoteSvrTest_Click()
'Test the remote OLE Automation server (OrderRem.vbp)
Dim strRemConn As String
Dim lngRetVal As Long
Dim objNewOrder As New RemoteServer.Order
If fPassthru Then
strRemConn = strConn
Else
'App.Path does not work for remote server
'Location of file must be hard coded
strRemConn = "C:\OrderRem\Msa7data.mdb"
End If
'Instantiate the Order object with the method call
lngRetVal = objNewOrder.AddNewOrder(strRemConn, _
avarOrder, avarItems, fPassthru, sngGetID, _
sngWrap, sngCommit, lngErrNum, strError)
txtOrder_ID.Text = lngRetVal
cmdRemoteSvrTest.Enabled = False
txtGetID.Text = Format$(sngGetID, "0.000")
txtWrap.Text = Format$(sngWrap, "0.000")
txtCommit.Text = Format$(sngCommit, "0.000")
Set objNewOrder = Nothing
If lngRetVal = 0 Then
'An error occurred in the remote server
MsgBox "Error " & lngErrNum & ": " & strError
End If
End Sub
The process for creating remote server setup files with the Setup Wizard and installing files on the remote server is as follows:
The content of the OrderRem.vbr file created during compilation of OrderRem.vbp, which provides the Registry information required by the remote server, is as follows:
VB4SERVERINFO
VERSION=1.0.0
APPDESCRIPTION=Remote Automation Server for Northwind
HKEY_CLASSES_ROOT\Typelib\{3CD04C66-9157-11CF-A658-
00AA002A764D}\1.0 = Remote Automation Server for Northwind
HKEY_CLASSES_ROOT\Typelib\{3CD04C66-9157-11CF-A658-
00AA002A764D}\1.0\0\win32 = OrderRem.exe
HKEY_CLASSES_ROOT\Typelib\{3CD04C66-9157-11CF-A658-00AA002A764D}\1.0\FLAGS = 0
HKEY_CLASSES_ROOT\RemoteServer.Order\CLSID = {3CD04C65-9157-11CF-A658-
00AA002A764D}
HKEY_CLASSES_ROOT\CLSID\{3CD04C65-9157-11CF-A658-
00AA002A764D}\ProgID = RemoteServer.Order
HKEY_CLASSES_ROOT\CLSID\{3CD04C65-9157-11CF-A658-
00AA002A764D}\Typelib = {3CD04C66-9157-11CF-A658-00AA002A764D}
HKEY_CLASSES_ROOT\CLSID\{3CD04C65-9157-11CF-A658-
00AA002A764D}\InprocHandler32 = OLE32.DLL
HKEY_CLASSES_ROOT\CLSID\{3CD04C65-9157-11CF-A658-
00AA002A764D}\LocalServer32 = OrderRem.exe
HKEY_CLASSES_ROOT\INTERFACE\{3CD04C64-9157-11CF-A658-00AA002A764D} = Order
HKEY_CLASSES_ROOT\INTERFACE\{3CD04C64-9157-11CF-A658-
00AA002A764D}\ProxyStubClsid = {00020420-0000-0000-C000-000000000046}
HKEY_CLASSES_ROOT\INTERFACE\{3CD04C64-9157-11CF-A658-
00AA002A764D}\ProxyStubClsid32 = {00020420-0000-0000-C000-000000000046}
HKEY_CLASSES_ROOT\INTERFACE\{3CD04C64-9157-11CF-A658-
00AA002A764D}\Typelib = {3CD04C66-9157-11CF-A658-00AA002A764D}
You must edit the ProjectName.vbr file for the Remote Automation server to add security features before you create the client setup files.
The process for setting up Remote Automation clients is quite similar to that for Remote Automation servers. In addition to the ProjectName.vbr file, the Setup Wizard also creates a ProjectName.tlb type library. Follow these steps to
create the client setup files, which you might want to locate on a file server for distribution to users:
During installation, the Setup Wizard adds the keys for the remote server to the Registry. Figure 23.16 shows the Registry entries for the RemoteServer.Order class. The entries you make in the Remote OLE Server Details dialog in the preceding step 4
sets the values of the AllowRemoteActivation, NetwordAddress, and ProtocolSequence keys.
You can use the Client pane of the Remote Automation Connection Manager (Racmgr32.exe) to change the values of the Registry keys for the remote server. Using Racmgr32.exe for this purpose is safer than manually modifying the values of the keys.
Figure 23.16. Registry entries for the RemoteServer.Order class.
After you run the Setup application for your Remote Automation client, you can choose to run the .exe file or run the client source code in Visual Basic 4.0. The client Setup process is required primarily to install the Registry keys for the Remote Automation object class.
Run TstOrder.exe, click Get Order Data, then click Remote Server Test to instantiate the RemoteServer.Order object and add an order or two to the database. The performance of the Creatable MultiUse version of the remote server, as measured by the
values shown in the transaction processing time text boxes (see Figure 23.17), is slightly slower than the local server version. The times displayed, however, do not include the overhead of creating an instance of the server.
Many errors returned during testing of your remote server are difficult to diagnose because Remote OLE Automation error codes and messages often are ambiguous or uninformative as to the actual source of the problem. For an explanation of the most likely source of the most common Remote OLE Automation errors, download Microsoft Knowledge Base article Q141824, "Common Remote OLE Automation Errors," from CompuServe (GO MSKB) or from http://www.microsoft.com. This article contains information, not included in the documentation that accompanies Visual Basic 4.0, that's helpful during the debugging process.
If you are running SQL Server and the Remote Automation server on the same computer, the time required by the remote server to process the first order by SQL passthrough is substantially faster than either the internal function call or the local
server. Establishing a remote connection to SQL server is responsible for this time difference, a one-time performance hit. (The remote server has a permanent local connection to SQL Server.) Subsequent order additions with the remote server are somewhat
slower than with the internal function call or local server. The reduction in speed is due to the time required to instantiate a RemoteServer.Order object.
Using a pool manager to provide multiple instances of Creatable SingleUse remote servers improves the performance, especially for multiple simultaneous users of an object class. The pool manager asynchronously creates a pool of objects of a specific class for use by client applications. Creating an object prior to the client's request for instantiation of the object saves the time necessary to instantiate the object in response to each client request. A RAO consumes about 500KB of server memory, so you need to be conservative in your allocation of server resources to pooled RAOs. The \Vb4\Samples\Remauto\Poolmngr folder contains an example of a Visual Basic 4.0 pool manager application.
The Component Manager is designed to encourage sharing of OLE Automation servers with other developers. You publish the characteristics of your server in a catalog that other developers can search to find an object class that meets their needs. To add
your Remote Automation sever to the Component Manager catalog included with the Enterprise Edition of Visual Basic 4.0, follow these steps:
Figure 23.20. An entry for the RemoteOrder class added to Component Manager's Sample Components catalog.
The ability to create and deploy Remote Automation servers is one of the primary selling features of the Enterprise Edition of Visual Basic 4.0. This chapter described the architecture of the current version of Remote Automation that uses Remote
Procedure Calls to instantiate OLE Automation objects across networks. The sample application of this chapter, derived from an existing Access 95 database application, demonstrated techniques for coding three-tier online transaction-processing
applications. Step-by-step procedures for deploying remote servers and client applications that rely on remote servers also were covered. The chapter concluded with a brief description of the use of the Enterprise Edition's Component Manager to catalog OLE
Automation servers.
The next chapter, "Managing Large Projects with Visual SourceSafe 4.0," deals with the process of creating Visual Basic 4.0 database applications in a multi-developer environment. Although source code control is critical to the successful
completion and subsequent maintenance of any Visual Basic application, source code control is particularly important during the development of sophisticated Remote Automation servers that provide a uniform set of business services to multiple client
applications.
