How to Pass an ADO.NET DataTable from Visual Basic 2005 to Visual Basic 6.0

  • Article

Ed Robinson

ActionThis Ltd

October 2007

Summary: This article demonstrates how to convert an ADO.NET DataSet to an ADO Recordset, and pass the Recordset from a Microsoft Visual Basic 2005 application to a Microsoft Visual Basic 6.0 application. All of the programming logic is stored in a Visual Basic 2005 library module that you can reuse in your own applications by using just a few lines of code.

Click here to download the code sample for this article.

Contents

Introduction

Using the Sample Application

Using the DS2RS Module in Your Own Applications

Other Resources

Conclusion

Introduction

Microsoft Visual Basic 2005 with ADO.NET provides powerful capabilities for developers to create data-rich applications. This article demonstrates how to extend these capabilities by using a library that converts an ADO.NET DataSet into an ADO Recordset. We also show how to pass the Recordset from a Visual Basic 2005 class library to a Visual Basic 6.0 executable.

The net result is that you can add the ability to return a DataSet from a Visual Basic 2005 class library to Visual Basic 6.0 in only three steps.

Before we begin, let's clear up some confusion around DataSet and DataTable terminology in Visual Basic 2005; these are sometimes used incorrectly. A DataTable is, as it sounds, a table that contains rows and columns of data, similar to an ADO recordset. DataGrids and other controls can be bound only to a DataTable. A DataSet, on the other hand, is a collection of one or more DataTables, along with relationships, constraints, and other information that is related to the set of tables.

To illustrate the difference, let's look at an example: The Employees table in the Northwind sample database can be represented as a DataTable, whereas the entire collection of tables in the Northwind database can be represented as a DataSet, with each database table a separate DataTable within the DataSet.

This article shows how to convert an ADO.NET DataTable to an ADO Recordset.

Using the Sample Application

Let's get started by looking at the sample application that is supplied with this article. First, we will need to ensure that your machine has all of the necessary prerequisites:

  • You will need to have Visual Studio 2005 or Visual Basic Express installed.
  • You will also need to install Visual Basic 6.0, including the Microsoft DataGrid control.
  • You should install the latest service packs for both products (at the time of writing, this is SP1 for Visual Studio 2005 and SP6 for Visual Basic 6.0).

After installing the prerequisites, unzip the sample files to a directory on your local hard disk. You will see that the sample contains three folders: the VB6 folder, which contains the Visual Basic 6.0 components; the VB2005 folder, which contains the Visual Basic 2005 components; and the Ds2Rs folder, which contains a Visual Basic 2005 library that you can copy and reuse in your own applications.

Together, these components form an AdoNet2Ado sample application that demonstrates how to pass an ADO.NET DataTable from Visual Basic 2005 to Visual Basic 6.0.

Now, we're ready to walk through the sample.

  1. Open the VB2005\AdoNet2Ado2005.sln Visual Basic 2005 solution.
    The solution contains two projects: AdoNet2Ado2005Exe, which is an executable that calls a method in a DLL library to return a DataTable that is then displayed in a DataGridView; and AdoNet2Ado2005lib, which is the DLL library that returns the Employees table as a DataTable. This DLL library will also be used later in the walkthrough, to demonstrate how to convert the Employees DataTable to a Recordset.
  2. Choose the Build|Build Solution menu item, and then press F5 to run the application.
  3. When the application runs, it opens a single Form. Click the Fill DataGrid button to populate the DataGridView with the Employees table from the sample Microsoft Office Access database named Northwind.
  4. Now, open the VB6\AdoNet2AdoVB6.prj Visual Basic application. The project contains a single form that has a DataGrid and button.
  5. Press F5 to compile and run the application.
  6. Click the Load Recordset button to call a method in the AdoNet2Ado2005lib DLL library to return a Recordset and show the Recordset in the DataGrid. That's it! You've converted and passed a DataTable from Visual Basic 2005 to Visual Basic 6.0.

Using the DS2RS Module in Your Own Applications

The bulk of the processing is done in the Ds2Rs.vb Visual Basic 2005 module. This module provides a DataTableToRecordset method to convert an ADO.NET DataSet to an ADO Recordset. After this is done, you can just return the ADO Recordset from a function in a Visual Basic 2005 DLL library to a Visual Basic 6.0 application.

This requires a few steps in both Visual Basic 2005 and Visual Basic 6.0.

Visual Basic 2005

To convert a DataSet to a Recordset in Visual Basic 2005:

  1. Add a project reference to the "Microsoft ActiveX Data Objects 2.8 Library" COM object.

  2. Copy the Ds2Rs\Ds2Rs.vb module from the Ds2Rs\ sample directory into your application.
    You can now convert a DataTable into an ADO Recordset by using the following code:

    Function MyFunction() As ADODB.Recordset
  Dim dt As DataTable
  'Your code that loads the DataTable dt with data
  'goes here
  Return Ds2Rs.DataTableToRecordset(dt)
End Function

  3. Make the DLL library COM visible. See Walkthrough: Creating COM Objects with Visual Basic 2005 in the MSDN Library, for help on how to do this.

Visual Basic 6.0

In your Visual Basic 6.0 applications, after adding a reference to the Visual Basic 2005 DLL library, you can call the function that returns an ADO Recordset in exactly the same way you as you would call any other function in a DLL library:

Dim MyClass As New MyLibrary.MyClass
Dim rs As Recordset
Set rs = MyLibrary.MyFunction()

Other Resources

Converting from ADO to ADO.NET

What about converting from ADO to ADO.NET? Happily, ADO.NET provides native methods to convert an ADO Recordset to an ADO.NET DataTable. For information on how to use these, see Filling a DataSet with an ADO Recordset or Record in the MSDN Library.

Passing an ADO Recordset by Using a Swap File

In some situations, you may need to pass an ADO Recordset from a Visual Basic 2005 executable to a Visual Basic 6.0 executable that is running side by side. You can do this easily with ADO by saving the Recordset to a file from Visual Basic 2005, and then loading the Recordset from the file in Visual Basic 6.0.

Here is how to save an ADO Recordset to a file in Visual Basic 2005:

rs.Save("C:\Recordset.xml", ADODB.PersistFormatEnum.adPersistXML)

Here is how to load the same ADO Recordset from the file in Visual Basic 6.0:

Dim rs As New Recordset
rs.Open "C:\Recordset.xml", Options:=adCmdFile

Interop Forms Toolkit 2.0

The techniques in this article are great for quickly returning a read-only Recordset from Visual Basic 2005 to Visual Basic 6.0. When you need read/write access to the DataSet from a Visual Basic 6.0 application, it might be time to look at the Interop Forms Toolkit 2.0, which provides a number of capabilities to create hybrid applications by using Visual Basic 6.0 and Visual Basic 2005. The Interop Forms toolkit enables you to host a Visual Basic 2005 control inside a Visual Basic 6.0 application or open a Visual Basic 2005 form from a Visual Basic 6.0 application. By using this toolkit, you can create Visual Basic 2005 components that manipulate DataTables natively, and host these components seamlessly in your Visual Basic 6.0 applications. See Microsoft Interop Forms Toolkit 2.0 in the MSDN Library, to download the Interop Forms Toolkit 2.0.

Conclusion

In this article, we demonstrated how to convert an ADO.NET DataTable to an ADO Recordset. This is an important technique; data forms the basis for almost every business application, and the data-manipulation features in Visual Basic 2005 are incredibly powerful, for both creating new applications and breathing new life into existing Visual Basic 6.0 systems.

About the author

Ed Robinson is the coauthor of Upgrading Microsoft Visual Basic 6.0 to Microsoft Visual Basic .NET and Security for Microsoft Visual Basic .NET, as well as numerous technology articles. Ed is the CEO for ActionThis Ltd, business-productivity software that is built proudly by using 100 percent .NET technology.