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
valboyGIS Database AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dcgamesCommented:
Move to VB.NET :)
0
valboyGIS Database AdministratorAuthor Commented:
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.
0
Éric MoreauSenior .Net ConsultantCommented:
Once opened, recordsets cannot be joined. Can't you joined them from your datasource?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sandygadCommented:
HI

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

0
valboyGIS Database AdministratorAuthor Commented:
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.
0
Éric MoreauSenior .Net ConsultantCommented:
>>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.
0
valboyGIS Database AdministratorAuthor Commented:
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
0
Éric MoreauSenior .Net ConsultantCommented:
>>all my interfaces are created in ArcGIS VBA - this is not a VB6 application

Your title is talking of VB6!

>>I am only vaguely familiar with Data Environment designer and it will take me some time to research the feasibility of this approach

Create 2 commands, one will be your parent (main) command retreiving the data from the master table. The second table will be the child command retreiving data from the related table. Now in the "Relation" tab of the child command properties, check the "relate to a parent command object" and select your first command and the field(s) required to relate both commands. Now right-click your parent command and select "hierarchy info" from the popup menu. You will have your SQL query there.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
valboyGIS Database AdministratorAuthor Commented:
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
0
valboyGIS Database AdministratorAuthor Commented:
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




0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.