Sharing ADO Recordsets Between Visual Basic 6 and Visual Basic 2005

Ed Robinson, Intergen Ltd

Applies to:
   Visual Basic 6
   Visual Basic 2005

Summary: Learn how to pass an ADO recordset from a Visual Basic 6 Windows application to a Visual Basic 2005 Windows application, update the recordset in Visual Basic 2005 and return the updated recordset to the original Visual Basic 6 application. (11 printed pages)

Download the associatedSharing_ADO.exe sample code.

Contents

Introduction
Using the Sample
Visual Basic 6 Section of the Sample
Visual Basic 2005 Section of the Sample
Handy Tips for Developing Hybrid Applications
Where to Use Disconnected Recordset Sharing
Conclusion

Introduction

Enhancing a Visual Basic 6 Windows application by adding new features developed in Visual Basic 2005 is a great way to extend the life of an existing application. However, actually developing components that will interoperate between Visual Basic 6 and Visual Basic 2005 present the following challenges:

• It is somewhat complex to develop an application using two development tools at the same time.
• It is difficult to pass state and data across application boundaries.
• It is often problematic maintaining a consistent experience for the end user.

Most developers work around these challenges by carefully partitioning their applications between Visual Basic 6 and Visual Basic .NET to favor one of two elements of interoperability; either service-centric, and form-centric.

Service-centric

Developers may choose to implement a solution where two separate applications interoperate at a service level. For example: A Visual Basic 2005 user interface that invokes business logic methods from a Visual Basic 6 DLL. This works well if the existing application is cleanly separated into logical tiers and user interface modules. However, many applications do not have this clean separation. Proceeding with this strategy is done at the expense of either cost or usability: The user interface is either entirely re-implemented in Visual Basic 2005; or is split between Visual Basic 6 and Visual Basic 2005 and maintained as two separate applications (existing in a clunky partnership that doesn't feel natural to the end user).

Form-centric

Developers may also choose to implement a solution where interoperability happens at the form boundary. This enables a more natural flow in the application as the user moves backwards and forwards seamlessly from a Visual Basic 6 form to a Visual Basic 2005 form. The Visual BasicInterop Forms Toolkit is a free download that enables this interoperability. Applications designed with this approach feel more natural, but face their own set of challenges when it comes to passing data between forms.

Each of the above methods has its own set of advantages. The purpose of this article is to illustrate a technique that uses the best of both methods to build applications with great workflow, developed in a hybrid of Visual Basic 6 and Visual Basic 2005. Using the illustrated method, the applications you create will also have the ability to navigate and pass data easily between forms. This technique is extremely useful when the primary user interface is already implemented in Visual Basic 6 and the goal is to add new features using Visual Basic 2005. The result is a true "hybrid" application implemented from different versions of Visual Basic. From a business perspective, this presents a cost-effective means to extend the life of existing applications.

The Visual Basic Interop Forms Toolkit is the basis of this solution, enabling a Visual Basic 6 form to open a Visual Basic 2005 form, creating the ability to invoke methods, and allowing a shared property bag to pass state between the forms. This article employs ADO recordset sharing as a technique to pass both clean and dirty recordsets between Visual Basic 6 and Visual Basic 2005. This enables scenarios similar to the following:

Peter Sloth creates a new customer record in Visual Basic 6. Using Visual Basic 2005, He then adds invoice items. He updates the customer's contact information in Visual Basic 6, and then finally commits the changes to the underlying database.

Since the point of most business applications is to manipulate information in some way, this is an important technique. Later in the article, we also give some tips for working productively with both the Visual Basic 6 and Visual Basic 2005 development environments concurrently.

Using the Sample

Let's start by looking at the sample application supplied with this article. We'll need to make sure your machine has all the necessary prerequisites:

• HaveVisual Studio 2005 orVisual Basic Express installed.
• InstallVisual Basic 6, with the latest service pack (SP6 at the time of writing).
• Ensure theVisual Basic 6 Datalist andADODC ActiveX controls are installed with Visual Basic 6 (for the purposes of this sample).
• InstallInterop Forms Toolkit after the above components are installed.

One of the useful features of Visual Basic is that both Visual Basic 6 and Visual Basic 2005 can be installed on the same machine. Both development environments can also run side-by-side on the same machine, at the same time, without interfering with each other. The order of installation doesn't matter—you can install Visual Basic 6 before or after installing Visual Basic 2005.

After installing the prerequisites, install the sample files to a directory on your local hard disk drive. After installing the sample, you will see it contains two folders:VB6, andVB2005. The VB6 folder contains the Visual Basic 6 component for selecting customers from the Northwind database, while the VB2005 folder contains the Visual Basic 2005 component for editing these customers. Together, the two components form a "hybrid" application.

The following steps will walk you through using the sample:

1. Double-click the folder\VB6, and run the applicationDataInteropVB6.exe.

2. The Visual Basic 6 application will display the list of customers in a list box.

   

   Figure 1. Visual Basic 6 Customer List Box

3. SelectAlfreds Frutterkiste (the first item in the list box), and click theEdit button.

   Note   As you select customers in the list box, the text fields on the right-hand side of the form display the customer's ID and company name. The label on the form background reads "Visual Basic 6.0", indicating this is the Visual Basic 6 component of the application.

4. A modal dialog will open, allowing you to edit fields in the customer record.

   Note   The label on the dialog background reads "Visual Basic 2005", indicating this is a Visual Basic 2005 form.

   

   Figure 2. Modal Dialog

5. To update the customer, change the Company Name toMy Customer then click theOK button.

   

   Figure 3. Updated Customer Information

6. That's it! You just successfully passed data from Visual Basic 6 to Visual Basic 2005 and back, with a seamless end-user experience. You can also run the application by opening the Visual Basic 6 project fileDataInteropVB6.vbp, and pressingF5. The remainder of this article will walk you through the concepts behind what you have just seen in this sample.

Visual Basic 6 Section of the Sample

Let's now look inside the Visual Basic 6 component of the sample. Open the projectVB6\DataInteropVB6.vpb and look at the design of the form. Notice the form uses an ADO data control to access the Customers table in the NWind.mdb Microsoft Access Database. The DataList1 data list ActiveX control displays the list of customers, and the Edit button opens the Visual Basic 2005 dialog for editing customer information.

Figure 4. Visual Basic Form Design

There are two important concepts to explore:

1. ADO disconnected recordsets
2. Using the Interop Forms Toolkit to pass control to a Visual Basic 2005 application

ADO Disconnected Recordsets

By default, all ADO recordsets are connected—changes to data are automatically updated in the underlying database. However, ADO also supports taking recordsets offline—or "disconnecting" them. When a recordset is disconnected, new records can be added; existing records can be updated or even deleted just as you would with a connected recordset. The difference is no changes will be reflected in the underlying database until the recordset is reconnected. Disconnected recordsets can also be saved to and loaded from an XML stream. Leveraging these two capabilities enables us to pass a recordset from Visual Basic 6 to Visual Basic 2005 and back.

To pass a record set from Visual Basic 6 to Visual Basic 2005 and back

1. InVisual Basic 6, open a recordset based on the Customers table and filter it to the currently selected customer.
2. Disconnect therecordset.
3. The disconnected recordset is packed into a string.
4. The string is passed to Visual Basic 2005.
5. The Visual Basic 2005 application unpacks the disconnected recordset from the string.
6. After changing fields, the updated disconnected recordset is packed into a string.
7. InVisual Basic 6, unpack thedisconnected recordset from the string.
8. Reconnect therecordset and apply any changes to the underlying database.

When creating the recordset, be sure to set the cursor location toclient-side, the cursor type todynamic, and the lock type tobatch optimistic, as in the following code snippet:

Dim rs As RecordsetSet rs = New Recordsetrs.CursorLocation = adUseClientrs.CursorType = adOpenDynamicrs.LockType = adLockBatchOptimisticrs.Open "Customers", GetConnectionrs.Filter = "CustomerID=  '" & Me.txtCustomerID & "'"

In the code snippet above, a methodGetConnection is used to store and provide a pointer to the live connection. To disconnect the recordset, simply set theActiveConnection toNothing:

Set rs.ActiveConnection = Nothing

The recordset is now disconnected. The disconnected recordset can now be saved to a file or packed into a string.

Saving a disconnected recordset to a file

Ultimately, the accompanying sample uses string packing. But let's first look at how you would save the disconnected recordset to a file.

Be aware that attempting to save a disconnected recordset to a file will cause an error in Visual Basic if a file with the same name already exists. A recordset cannot be saved over an existing file. If the file already exists, you must first remove it before saving a disconnected recordset:

If Dir$("C:\MyFile.xml") <> "" Then    Kill "C:\MyFile.xml"End If

Loading and saving the disconnected recordset is done using theRecordset.Save andRecordset.Open methods as in the following snippet:

'Save disconnected recordset to a filers.Save "C:\MyFile.xml", adPersistXML'Load a disconnected recordset from a filers.Open "C:\MyFile.xml", Options:=adCmdFile

Packing a disconnected recordset into a string

Similar to saving a disconnected recordset to a file, the accompanying sample uses string packing to store the contents of a recordset in a string. First an ADO stream is created which is similar to a file, but held in memory rather than stored on disk. The recordset is saved to the stream in exactly the same way as we saw to a file. The stream is then copied into a string. The contents of the string are said to be "packed", meaning the string is not intended to be read or interpreted as text. Instead, it is simply a convenient way to store and pass around a chunk of data.

Dim stm As ADODB.StreamDim RsToString As StringSet stm = New ADODB.Streamrs.Save stm, ADODB.PersistFormatEnum.adPersistXMLRsToString = stm.ReadText

To unpack the string back into a recordset, we essentially do the same procedure in reverse—create an ADO stream, copy the contents of the string into the stream, then open a disconnected recordset, using the stream like an in-memory file:

Set stm = New ADODB.StreamSet rs = New ADODB.Recordsetstm.Openstm.WriteText RsToStringstm.Position = 0rs.Open stm

In both the Visual Basic 6 and Visual Basic 2005 sections of the sample, the string packing logic is encapsulated into two methods you can copy and use in your own applications:

• RsToString
  A method for packing a recordset into a string
• StringToRs
  A method for unpacking a recordset from a string

Reconnecting the Recordset

After opening a disconnected recordset, the recordset can be reconnected to its original connection by setting theActiveConnection property back to its original value. The final step is to write any data updates made while the recordset was disconnected back to the database using theRecordset.UpdateBatch method.

Set rs.ActiveConnection = GetConnectionrs.UpdateBatch adAffectAllrs.Close

This is an incredibly simple and robust procedure as long as you adhere to the following key principles:

• The connection must remain open for the entire time the recordset is disconnected and the recordset can only be reconnected to exactly the same connection it was originally disconnected from
• While a recordset is disconnected, no other code should attempt to use the recordset in a connected fashion. This primarily applies to databinding. Do not attempt to disconnect and reconnect the recordset used by the ADO data control. Instead, create a new recordset based on the same query, and use this new recordset for purposes of disconnecting. The sample shows how to do this, and how to keep the ADO data control up-to-date.

Passing control from Visual Basic 6 to Visual Basic 2005

When the user clicks the Edit button, our application opens a Visual Basic 2005 dialog for editing the selected customer record. The form is opened modal on top of the Visual Basic 6 application. Since Visual Basic 6 does not natively ship with the capability to open a Visual Basic 2005 form, you will need to install theInterop Forms Toolkit, a free download.

The sample performs the following steps to pass the recordset packed into a string from Visual Basic 6 to Visual Basic 2005:

1. The Visual Basic 2005 form is created
2. The string is saved to theFormDataString custom form property
3. The Visual Basic 2005 form is opened modal
4. As the Visual Basic 2005 form closes, control returns to the Visual Basic 6 application, which retrieves the updated string from theFormDataString property.

Here is how the Visual Basic 2005 form is opened from Visual Basic 6:

Dim frmEdit As DataInteropVB2005_Interop_frmEditSet frmEdit = New DataInteropVB2005_Interop_frmEditfrmEdit.FormDataString = RsToString(rs)frmEdit.Show vbModal

Notice how the job of opening a Visual Basic 2005 form is made simple by the Interop Forms Toolkit.

This technique is effective for passing data between a parent form and a modal pop-up form and ensures good form navigation from Visual Basic 6 to Visual Basic 2005.

Visual Basic 2005 Section of the Sample

Now let's look inside the Visual Basic 2005 component. Open the projectVB2005\DataInteropVB2005.vbproj in Visual Basic 2005.

Figure 5. Project DataInteropVB2005.vbproj

The form contains four textboxes, an OK button and Cancel button. The form has no ADO data control or any other datasource for populating the textboxes. Instead, when the form is created, the Visual Basic 6 application saves the packed recordset into the public propertyFormDataString. In theForm_Load event, the disconnected recordset is unpacked from the string and used to populate the textboxes on the form.

m_rs = StringToRs(Me.FormDataString)Me.txtCustomerID.Text = m_rs("CustomerID").ValueMe.txtCompanyName.Text = m_rs("CompanyName").ValueMe.txtContactName.Text = m_rs("ContactName").ValueMe.txtContactTitle.Text = m_rs("ContactTitle").Value

It is important to note the Visual Basic 2005 application never reconnects the recordset; the reconnection is done after the recordset is passed back to the Visual Basic 6 application.

When you click the OK button, the fields in the recordset are updated with the values from the textboxes

m_rs("ContactName").Value = Me.txtContactName.Textm_rs("CompanyName").Value = Me.txtCompanyName.Textm_rs("ContactTitle").Value = Me.txtContactTitle.Text

The recordset is packed into theFormDataString property and the form is then hidden.

Me.FormDataString = RsToString(m_rs)Me.Hide()

When the visible status changes to hidden, control is returned to the Visual Basic 6 application, which retrieves the updated recordset and applies the changes back to the database.

If you click the Cancel button, no changes are saved back to theFormDataString property.

Handy Tips for Developing Hybrid Applications

Now that we've walked through the sample, let's explore a few handy tips to make it easier to develop hybrid applications with Visual Basic 6 and Visual Basic 2005.

Develop components apart, debug them together

Although you can develop and debug hybrid applications as a single unit, the most effective method is to develop the Visual Basic 2005 components separately from the Visual Basic 6 components. You will integrate them together only when you are ready to debug the end-to-end application.

The main reason for this method is because of a limitation in Visual Basic 6. The Visual Basic 6 development environment maintains a file lock on any DLLs referenced by the currently loaded project. Because the Visual Basic 6 project references the Visual Basic 2005 application, the Visual Basic 2005 forms cannot be recompiled while the Visual Basic 6 application referencing them is open in the Visual Basic 6 IDE. In short, you have to close Visual Basic 6 every time you recompile the Visual Basic 2005 component. The solution is to use a lightweight test harness in Visual Basic 2005 for developing and testing the Visual Basic 2005 component until it is ready for integration with the Visual Basic 6 component.

Debugging between Visual Basic 6 and Visual Basic 2005

Debugging a hybrid application is surprisingly easy—once you know how. The secret is to drive the debugging from Visual Basic 2005.

In the Visual Basic 2005 development environment, set the debugging start action to start the external Visual Basic 6 program. Remember to pass the path to the Visual Basic 6 component (Figure 6) to Visual Basic 6. To debug the hybrid application, pressF5 from Visual Basic 2005. The component will then compile and the Visual Basic 6 project will open in the Visual Basic 6 development environment. If you pressF5 from Visual Basic 6, you can debug and step through the code from Visual Basic 6 to Visual Basic 2005 (and back). To stop debugging, close the Visual Basic 6 development environment. After closing the environment, Visual Basic 2005 will revert back to design mode.

Figure 6. Pass the Visual Basic 6 Component Path to Visual Basic 6

Common method names

We purposely look for common methods that can be implemented in both components of the application. A good example is the methodRsToString, which packs a recordset into a string. We recommend looking for common methods and using the same method name in both Visual Basic 6 and Visual Basic 2005. This makes development easier as you only need to remember one method name instead of two.

Where to Use Disconnected Recordset Sharing

After finishing this article, you may be wondering just where you might use this technique in your own applications. Sharing ADO recordsets and creating hybrid applications using Visual Basic 6 and Visual Basic 2005 is an excellent way to gradually replace parts of a Visual Basic 6 application with Visual Basic 2005, form-by-form. In addition, the Visual Basic 2005 components can take advantage of the following new capabilities in the .NET framework 2.0:

• Access to WebServices to gain information and services across any network.
• Advanced graphics techniques such as transparency, shaped forms, drawing, and painting services.
• Better connectivity, including enhanced network access, full event log support, WMI, and Windows enterprise services support.

These features are available to hybrid applications and can be used to add a new dimension to your Visual Basic 6 applications, extending their life and increasing their value inside your organization.

Conclusion

Hopefully, the techniques shown in this article will create new opportunities for enhancing and extending your existing Visual Basic 6 applications with capabilities from the latest version of Visual Basic. In this article, you learned how to pass recordsets from Visual Basic 6 to Visual Basic 2005 and back, as well as how to invoke a Visual Basic 2005 form modally from Visual Basic 6. Stay tuned for more great ways you can easily leverage your existing Visual Basic 6 skills and applications to cost-effectively deliver tomorrow's solutions using both Visual Basic 6 and Visual Basic 2005.

Ed Robinson co-authored "Upgrading Visual Basic 6.0 to Visual Basic .NET", "Security for Visual Basic .NET", and numerous technology articles. Ed is the CIO for Intergen Ltd—one of New Zealand's most prominent Microsoft Gold Certified Partners.