Solved

Transpose an ADO recordset

Posted on 2004-08-27
9
1,190 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 51

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 51

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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views 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 Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

717 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