Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Transpose an ADO recordset

Posted on 2004-08-27
9
Medium Priority
?
1,481 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 51

Assisted Solution

by:Steve Bink
Steve Bink earned 120 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 1320 total points
ID: 11917959
got some at work but I am at home now :-(
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:thePrisoner
ID: 11918013
Stevbe,

How about Monday?
0
 
LVL 39

Assisted Solution

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

Assisted Solution

by:nmilmine
nmilmine earned 60 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 51

Assisted Solution

by:Steve Bink
Steve Bink earned 120 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 1320 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

580 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