asked on

in-memory table joins (VB6)

Is there an easy way to perform a simple inner join on two tables that reside in memory, for instance stored as disconnected ADO recordsets, and output the result as another ADO recordset? Writing temp tables is not an alternative. The only other option that comes to my mind is to create a dictionary for table 1 and update with records from table 2 but the dictionary has much less capabilities.

I searched the Web and an interest for such operations appears to exist but I couldn't find an explicit solution.

Thanks for any hints,

Valentina

dcgames

Move to VB.NET :)

valboy

ASKER

I am moving but it takes time and the project deadline is near...

What about ADO and data shaping? Looks promising. I think it can be done in VB6 as well.

Éric Moreau

Once opened, recordsets cannot be joined. Can't you joined them from your datasource?

sandygad

HI

can you elaborate your problem or
give some more info about what kind of join on what kind of data ?

valboy

ASKER

Here is the background. I am working on a fairly complex utility geodatabase (ArcGIS environment). In each asset type (water, sewer, stormwater, etc) there are multiple spatial features - pipes, valves, etc. Each feature is further split on subtype - main pipe, lateral pipe, etc. In addition to the spatial tables, I have several standalone tables containing financial information. My task is to generate reports for the chosen asset over a particular period. There are other restrictions, as well, that are not pertinent to the problem that I am describing.

During the process of creating the reports I have to join one table to the spatial feature tables several times, calculate some fields, unjoin, then join another table based on the calculations from the previous step, etc. It's an iterative process and the tables being joined constantly change their content.

So, because of the large number of joins which are of course performed over the network, I thought that having at least some of the information stored in memory will speed up considerably the process. My idea was to create a disconnected ADO recordset for each financial table and once the job is done, to export them in the specified format. I have worked many times before this project with diconnected recordsets and I love what they can do for me. If only I could figure out how to do data shaping on them which right now seems to be the only way to get related information.

Maybe this is not the best approach. I would like to avoid creating temp tables on the disk (local or network) because I still will have to import them in my map document and performance is of concern.

Thanks for your interest.

Éric Moreau

>>If only I could figure out how to do data shaping on them which right now seems to be the only way to get related information

Use the DataEnvironment to create your shaped-commands then copy the queries to your code if you don't want to keep the DE.

valboy

ASKER

emoreau,

I am only vaguely familiar with Data Environment designer and it will take me some time to research the feasibility of this approach unless I receive more hints and possibly links to examples.

I did not mention that all my interfaces are created in ArcGIS VBA - this is not a VB6 application distributed through a setup. Therefore, while I can import any fancy designer objects on my machine, I can't expect the client to have VB installed on his machines to ensure the existence of all dependent libraries. You do mention that I can eventually get rid of the DE, though, so this is good news for me.

Thanks,
Valentina

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

valboy

ASKER

emoreau,

I apologize for my late reply - I was pulled off this project for a few days. My title did mention VB6 instead of VBA only because I wanted VB6-oriented answers rather than VB.NET. Your answer helped me a lot - I could have never figured the sequence to get the SQL query. I also did some additional reserach and came upon a very useful example for hierarchical recordsets by Microsoft - it deals with virtual tables only, just what I wanted.

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q196/0/29.asp&NoWebContent=1

Nevertheless, I will accept your answer because it started me thinking in the right direction.

Thanks,

Valentina

valboy

ASKER

Another excellent example for custom hierarchical recordsets (this is the official name for what I wanted to do). The code below contains fixes for some minor bugs in the original code.

Cheers!
==============================================================

Sub Test_In_Memory_Join()

' http://www.4guysfromrolla.com/webtech/060301-1.shtml
' custom hierarchical recordsets
' working example of in-memory table joins

Dim strConnString
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
'We want the MSDataShape provider and no Data Provider
strConnString = "Data Provider=NONE; Provider=MSDataShape"

objConn.Open strConnString 'Open the connection

Dim objParentRS As ADODB.Recordset
Set objParentRS = New ADODB.Recordset

'Create our custom data shape (hierarchical Recordset)
Dim strSQL
strSQL = "SHAPE APPEND " & _
" NEW adInteger AS EmployeeID, " & _
" NEW adVarChar(50) AS Name, " & _
" ((SHAPE APPEND " & _
" NEW adInteger AS ProjectID, " & _
" NEW adVarChar(50) AS ProjectName, " & _
" NEW adInteger AS EmployeeID) " & _
" RELATE EmployeeID TO EmployeeID) AS rsProjects"

objParentRS.LockType = adLockOptimistic
objParentRS.Open strSQL, objConn

objParentRS.AddNew
objParentRS("EmployeeID") = 1
objParentRS("Name") = "Scott Mitchell"
objParentRS.Update

objParentRS.AddNew
objParentRS("EmployeeID") = 2
objParentRS("Name") = "Bill Gates"
objParentRS.Update

objParentRS.AddNew
objParentRS("EmployeeID") = 3
objParentRS("Name") = "Larry Ellison"
objParentRS.Update

Dim objChildRS
Set objChildRS = CreateObject("ADODB.Recordset")

'Now we have to add projects for each employee
Set objChildRS = objParentRS("rsProjects").Value

objChildRS.AddNew
objChildRS("ProjectID") = 1
objChildRS("ProjectName") = "Wireless Something-or-Other"
objChildRS("EmployeeID") = 1
objChildRS.Update

objChildRS.AddNew
objChildRS("ProjectID") = 2
objChildRS("ProjectName") = "Floor Sweeping"
objChildRS("EmployeeID") = 2
objChildRS.Update

objChildRS.AddNew
objChildRS("ProjectID") = 3
objChildRS("ProjectName") = "Janitorial Duties"
objChildRS("EmployeeID") = 2
objChildRS.Update

objChildRS.AddNew
objChildRS("ProjectID") = 4
objChildRS("ProjectName") = "Mopping"
objChildRS("EmployeeID") = 3
objChildRS.Update

objChildRS.AddNew
objChildRS("ProjectID") = 5
objChildRS("ProjectName") = "Web Site Redesign"
objChildRS("EmployeeID") = 1
objChildRS.Update

objParentRS.MoveFirst
Do While Not objParentRS.EOF

Debug.Print objParentRS("Name")

'Now Set the Child recordset to rsTitles
Set objChildRS = objParentRS("rsProjects").Value

'Loop through the Child recordset
If objChildRS.EOF Then Debug.Print "No Projects"

Do While Not objChildRS.EOF
Debug.Print " === " & objChildRS("ProjectName")

objChildRS.MoveNext
Loop

Debug.Print

objParentRS.MoveNext
Loop

End Sub