[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

in-memory table joins (VB6)

Posted on 2004-11-03
10
Medium Priority
?
520 Views
Last Modified: 2008-01-16
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
0
Comment
Question by:valboy
10 Comments
 
LVL 5

Expert Comment

by:dcgames
ID: 12487139
Move to VB.NET :)
0
 

Author Comment

by:valboy
ID: 12487194
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 12489175
Once opened, recordsets cannot be joined. Can't you joined them from your datasource?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Expert Comment

by:sandygad
ID: 12489966
HI

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

0
 

Author Comment

by:valboy
ID: 12493502
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 12494493
>>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
 

Author Comment

by:valboy
ID: 12495057
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
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 750 total points
ID: 12496770
>>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
 

Author Comment

by:valboy
ID: 12551923
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
 

Author Comment

by:valboy
ID: 12552228
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question