Solved

Transpose an ADO recordset

Posted on 2004-08-27
9
1,108 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:thePrisoner
ID: 11918013
Stevbe,

How about Monday?
0
 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

813 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

17 Experts available now in Live!

Get 1:1 Help Now