Solved

Transpose an ADO recordset

Posted on 2004-08-27
9
1,064 Views
Last Modified: 2008-01-09
I've got a JET/ADO query in a VB6 app that returns a recordset with x1 to xn columns, y1 to yn rows, and a value (an average) for each cell. I need to transpose - in memory, not excel - this so that I have x1 to xn as rows, y1 to yn as column names and the correct value in each cell.

Anyone have any VB6 code that will do this? Note: the number of columns/rows is dynamic and changes depending on user inputs.

sort of like:

public function TranADOset(rstIn as adodb.recordsset) as adodb.recordset
'do the transpose
end function
0
Comment
Question by:thePrisoner
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 40 total points
ID: 11917248
You can do it, but I would not worry about making it a recordset.  ADO recordsets contain the GetRows method, which transforms a recordset into an array.  From there, you have much more flexibility in how you want to display the data, and is overall easier to deal with than a recordset.  The main difficulty you will run into is if you have to update data.  You will have to devise a system by which you can open the recordset, find the record which is to be altered, and commit the changes, based on the data in the array.  Really not that difficult, but it could be a little confusing..

0
 

Author Comment

by:thePrisoner
ID: 11917490
I need to end up with an ADO recordset since I need to bid that to a grid. good point re getrows: got any code?
0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 440 total points
ID: 11917959
got some at work but I am at home now :-(
0
 

Author Comment

by:thePrisoner
ID: 11918013
Stevbe,

How about Monday?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 440 total points
ID: 11918036
sure thing ...
0
 
LVL 4

Assisted Solution

by:nmilmine
nmilmine earned 20 total points
ID: 11923728
This is a function I have used to transpose and create a new table to use

Function TransposeCourses(strSource As String, strTarget As String)

   Dim db As DAO.Database
   Dim tdfNewDef As DAO.TableDef
   Dim fldNewField As DAO.Field
   Dim rstSource As DAO.Recordset, rstTarget As DAO.Recordset
   Dim i As Integer, j As Integer

   On Error GoTo Transposer_Err

   Set db = CurrentDb()
   Set rstSource = db.OpenRecordset(strSource)
   rstSource.MoveLast
   
   'Delete old table
   DoCmd.DeleteObject acTable, "Averages - Course"

   ' Create a new table to hold the transposed data.
   ' Create a field for each record in the original table.
   Set tdfNewDef = db.CreateTableDef(strTarget)
      Set fldNewField = tdfNewDef.CreateField("Category", dbText)
      tdfNewDef.Fields.Append fldNewField
      Set fldNewField = tdfNewDef.CreateField("Average", dbSingle)
      tdfNewDef.Fields.Append fldNewField
   
   db.TableDefs.Append tdfNewDef

   ' Open the new table and fill the first field with
   ' field names from the original table.
   Set rstTarget = db.OpenRecordset(strTarget)
   For i = 0 To rstSource.Fields.Count - 1
      With rstTarget
        .AddNew
        .Fields(0) = rstSource.Fields(i).Name
        .Update
      End With
   Next i

   rstSource.MoveFirst
   rstTarget.MoveFirst
   ' Fill each column of the new table
   ' with a record from the original table.
   For j = 0 To rstSource.Fields.Count - 1
      ' Begin with the second field, because the first field
      ' already contains the field names.
      For i = 1 To rstTarget.Fields.Count - 1
         With rstTarget
            .Edit
            .Fields(i) = rstSource.Fields(j)
            rstSource.MoveNext
            .Update
         End With

      Next i
      rstSource.MoveFirst
      rstTarget.MoveNext
   Next j

   db.Close

   Exit Function

Transposer_Err:

   Select Case Err
      Case 3010
         MsgBox "The table " & strTarget & " already exists."
      Case 3078
         MsgBox "The table " & strSource & " doesn't exist."
      Case Else
         MsgBox CStr(Err) & " " & Err.Description
   End Select

   Exit Function

End Function
'To test the function, press CTRL+G. In the Immediate window, type the following line,
'and then press ENTER:
'Print TransposeCourses("Average Course Results", "Averages - Course")

Cheers
Neil
0
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 40 total points
ID: 11931983
Here's some code that shows how to use GetRows().  Also, you could consider creating an Excel object (not a visible app...just the object), populating the sheet with the recordset, transpose it using Excel's built-in method, then requery the updated sheet to return another recordset.  GetRows returns an array, not a recordset, but it should be just as easy for you to use.  I'll see if I can test out some code for you.  In the meantime:

Set db = CurrentDB
Set rs = db.OpenRecordset("SELECT * FROM MyTable")

MyArray = rs.GetRows

For y = 0 to ubound(MyArray,1)    ' loops through the records
   For x = 0 to ubound(MyArray,0)   ' loops through fields
      Debug.Print MyArray(x,y)
   Next ' x
Next ' y
0
 
LVL 39

Accepted Solution

by:
stevbe earned 440 total points
ID: 11932387
so if you want to build the recordset entirely in memory with ADO instead of using a temp table ....

Set rst = .... blah blah ... this part you already have

    'create blank recordset for storing ttransposed items
    Set rstMem = New ADODB.Recordset

    i = 1
    Do While not rst.EOF
        rstMem.Fields.Append "Field" & i, adVarChar, 255
        i = i + 1
        rst.MoveNext
   End If

   'ok ... now you have the recordset structure transposed, fill values
   i = 1
   rstMem.Open
       Do While not rst.EOF
           For Each fld In rst.fields
                rstMem.AddNew
                rstMem.Fields("Field" & i).Value = fld.Value
                rstmem.Update
                i = i + 1
           Next
           rst.MoveNext
       Next

Set Me.grdAvg.Recordset = rstMem
0
 

Author Comment

by:thePrisoner
ID: 11935116
Thanks stevbe, that was enough to get me there. FYI, here's the final code.


Private Function FlipTheGrid(rstIN As ADODB.Recordset, _
                             rstOUT As ADODB.Recordset, FLD0 As String) As Boolean
    'transpose the recordset and return it
    Dim i As Long
    Dim x As Long
    Dim fld As ADODB.field
    'Debug.Print "RSTIN-FLDs: " & rstIN.Fields.Count '7
    'Debug.Print "RSTIN-RECs: " & rstIN.RecordCount '14
    i = 1
    rstOUT.Fields.Append FLD0, adVarChar, 255  'the factor name fld
    Do While Not rstIN.EOF
        rstOUT.Fields.Append rstIN.Fields(0).Value, adVarChar, 255
        i = i + 1
        rstIN.MoveNext
    Loop
    i = 1
    If rstIN.RecordCount > 0 Then
        rstIN.MoveFirst
    End If
    rstOUT.Open
    'Debug.Print "RSTOUT-FLDs: " & rstOUT.Fields.Count '14
    'Debug.Print "RSTOUT-RECs: " & rstOUT.RecordCount  '7
    'pass 1: create a record for each factor, factor name as value for fld 1
    i = 1
    For x = 1 To rstIN.Fields.Count - 1
        rstOUT.AddNew
        rstOUT.Fields(0).Value = rstIN.Fields(x).name
        rstOUT.UpdateBatch adAffectAllChapters
    Next
    'Debug.Print "RSTOUT-FLDs: " & rstOUT.Fields.Count '14
    'Debug.Print "RSTOUT-RECs: " & rstOUT.RecordCount  '7
    rstOUT.MoveFirst
    rstIN.MoveFirst
    'pass 2 for each record in, write down record after record in that fld in OUT.
    For i = 1 To rstOUT.Fields.Count - 1
        rstOUT.MoveFirst
        For x = 1 To rstIN.Fields.Count - 1
            rstOUT.Fields(i).Value = Format(rstIN.Fields(x).Value, "#.00")
            rstOUT.UpdateBatch adAffectAllChapters
            rstOUT.MoveNext
        Next
        rstIN.MoveNext
    Next
    FlipTheGrid = True
End Function
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

708 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now