Solved

VBA Access ADO Recordset Field Names Output

Posted on 2013-01-30
4
1,121 Views
Last Modified: 2013-02-02
Hi,

I am exporting some selected data from a table in Access into Excel. I have done this many time however for some reason the code below is not working as I expect it to. All the records are exported to excel but the field order is incorrect.

So in my query below the first two fields I want in columns A and B or Security Code Primary & Sedol. However for some reason when the data is exported it sorts the field names in alphabetical order?

Private Sub ExportData(Factory As clsAccess)

Dim rsEx As ADODB.Recordset

' query data from consolidate table
Factory.strQueryText = "SELECT SS_Consolidate.[Security Code Primary], SS_Consolidate.Sedol, SS_Consolidate.Isin, " & _
                            "SS_Consolidate.[Alternate Security Code], SS_Consolidate.[SECURITY NAME SHORT], SS_Consolidate.[Currency Code Local], " & _
                            "SS_Consolidate.[SECURITY TYPE CODE], SS_Consolidate.[COUNTRY RISK], SS_Consolidate.[Transaction Number External], SS_Consolidate.[Process Date], " & _
                            "SS_Consolidate.[Trade Date], SS_Consolidate.[Settlement Date], SS_Consolidate.[Transaction Category Code], " & _
                            "SS_Consolidate.[Currency Code Settlement], SS_Consolidate.[Fx Rate Local To Firm], SS_Consolidate.Units, " & _
                            "SS_Consolidate.[Price Trade Local], SS_Consolidate.[Trading Units], SS_Consolidate.[Cost Local], " & _
                            "SS_Consolidate.[Proceeds Local], SS_Consolidate.[Interest Purchased Sold Local], SS_Consolidate.[Commission Local], " & _
                            "SS_Consolidate.[Fee Total Capitalized User Defined Local], SS_Consolidate.[Portfolio Code], " & _
                            "SS_Consolidate.[Client Executing Broker], SS_Consolidate.[Principal Or Agent Name], SS_Consolidate.[Fx Rate Local To Portfolio], " & _
                            "SS_Consolidate.[Currency Code Portfolio], SS_Consolidate.[Block Id External], SS_Consolidate.Type " & _
                        "FROM SS_Consolidate " & _
                        "ORDER BY SS_Consolidate.[Security Code Primary];"
Set rsEx = Factory.SQLExecuteQuerySelect
    
    If rsEx.EOF = False Then
        ' export data to excel
        Dim wb As Workbook
        Dim ws As Worksheet
        Set wb = Application.ActiveWorkbook
        Set ws = wb.ActiveSheet
                
        ' populate header
        Dim i As Integer
        
        For i = 0 To rsEx.Fields.Count - 1
            ws.Cells(1, i + 1) = rsEx.Fields(i).Name
        Next
        
        ws.Range("A5").CopyFromRecordset rsEx
        ws.Rows("1:1").Font.Bold = True
        ws.Cells.EntireColumn.AutoFit
    Else
        MsgBox "No data found in Consolidate table, please check the contents of the excels files, Transactions & Security_Reference", vbInformation, "No Data"
    End If

Set rsEx = Nothing

End Sub

Open in new window


Code below is from the class

Public Function SQLExecuteQuerySelect() As ADODB.Recordset

' Any SQL select query is run from here

    On Error Resume Next                        ' cannot open recordset if already open
        prs.Close
    If Err <> 0 Then On Error GoTo 0
    
    prs.Open strQueryText, pcn, adOpenKeyset, adLockOptimistic
    Set SQLExecuteQuerySelect = Clone(prs)
    prs.Close
    
End Function

Private Function Clone(ByVal oRs As ADODB.Recordset) As ADODB.Recordset

' Function makes a clone of the private recordset. The ADO Recordset.Clone method doesn't actually clone the recordset. It doesn't create a new object _
    in memory it just returns a reference to the private recordset. This function make a real clone without any dependencies. _
    Without this function user cannot have more than one open recordset

Dim rsClone As ADODB.Recordset

    'save the recordset to the stream object
    Set pStream = New ADODB.Stream
    prs.Save pStream
    
    'and now open the stream object into a new recordset
    Set rsClone = New ADODB.Recordset
    rsClone.Open pStream
    
    'return the cloned recordset
    Set Clone = rsClone
    
    'release the reference
    Set rsClone = Nothing
    
End Function

Open in new window

0
Comment
Question by:mcs26
[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
  • 2
  • 2
4 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38835202
try this codes



Private Sub ExportData()
   Dim cn As ADODB.Connection
   Dim rsEx As ADODB.Recordset
   Dim strSQL as string     
   
    strSQL= "SELECT SS_Consolidate.[Security Code Primary], SS_Consolidate.Sedol, SS_Consolidate.Isin, " & _
                            "SS_Consolidate.[Alternate Security Code], SS_Consolidate.[SECURITY NAME SHORT], SS_Consolidate.[Currency Code Local], " & _
                            "SS_Consolidate.[SECURITY TYPE CODE], SS_Consolidate.[COUNTRY RISK], SS_Consolidate.[Transaction Number External], SS_Consolidate.[Process Date], " & _
                            "SS_Consolidate.[Trade Date], SS_Consolidate.[Settlement Date], SS_Consolidate.[Transaction Category Code], " & _
                            "SS_Consolidate.[Currency Code Settlement], SS_Consolidate.[Fx Rate Local To Firm], SS_Consolidate.Units, " & _
                            "SS_Consolidate.[Price Trade Local], SS_Consolidate.[Trading Units], SS_Consolidate.[Cost Local], " & _
                            "SS_Consolidate.[Proceeds Local], SS_Consolidate.[Interest Purchased Sold Local], SS_Consolidate.[Commission Local], " & _
                            "SS_Consolidate.[Fee Total Capitalized User Defined Local], SS_Consolidate.[Portfolio Code], " & _
                            "SS_Consolidate.[Client Executing Broker], SS_Consolidate.[Principal Or Agent Name], SS_Consolidate.[Fx Rate Local To Portfolio], " & _
                            "SS_Consolidate.[Currency Code Portfolio], SS_Consolidate.[Block Id External], SS_Consolidate.Type " & _
                        "FROM SS_Consolidate " & _
                        "ORDER BY SS_Consolidate.[Security Code Primary];"
   
   'Use the ADO connection that Access uses
   Set cn = CurrentProject.AccessConnection

   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rsEx = New ADODB.Recordset
   With rsEx
      Set .ActiveConnection = cn
      .Source = strSQL
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open 
   End With
   
       If rsEx.EOF = False Then
        ' export data to excel
        Dim wb As Workbook
        Dim ws As Worksheet
        Set wb = Application.ActiveWorkbook
        Set ws = wb.ActiveSheet
                
        ' populate header
        Dim i As Integer
        
        For i = 0 To rsEx.Fields.Count - 1
            ws.Cells(1, i + 1) = rsEx.Fields(i).Name
        Next
        
        ws.Range("A5").CopyFromRecordset rsEx
        ws.Rows("1:1").Font.Bold = True
        ws.Cells.EntireColumn.AutoFit
    Else
        MsgBox "No data found in Consolidate table, please check the contents of the excels files, Transactions & Security_Reference", vbInformation, "No Data"
    End If
   
   
   Set rsEx = Nothing
   Set cn = Nothing



End Sub

Open in new window

0
 

Author Comment

by:mcs26
ID: 38835231
Hi,

Thanks for the reply. Yep your code works fine! I'm confussed though what is causing mine not to as other similar queries from other projects work fine? Have you come across this issue before?

I didn't even know you could order the fields of a recordset in alphabetical order?

Thanks again,
M
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38835251
probably it was done when you use the Class Factory in your codes.

sorry, but i don't see any reason to use the Class if you just want to export the records to Excel.. actually, i've never used any UDF class just to export records to excel
0
 

Author Comment

by:mcs26
ID: 38835754
Yes I agree I have inherited the code and now intrigued to understand whats going on.

The Clone function in the class is causing the error.

prs is a private variable in the class of type adodb.recordset. Even though it is private it is still passed to the Clone function by val. When I look at the local varibales whilst in the Clone function oRs has the correct field order and source.

The line rsClone.Open pStream is where the fields change order? Although for other select queries this has worked.

Public Function SQLExecuteQuerySelect() As ADODB.Recordset

' Any SQL select query is run from here

    On Error Resume Next                        ' cannot open recordset if already open
        prs.Close
    If Err <> 0 Then On Error GoTo 0
    
    prs.Open strQueryText, pcn, adOpenKeyset, adLockOptimistic
    Set SQLExecuteQuerySelect = Clone(prs)
    prs.Close
    
End Function

Private Function Clone(ByVal oRs As ADODB.Recordset) As ADODB.Recordset

' Function makes a clone of the private recordset. The ADO Recordset.Clone method doesn't actually clone the recordset. It doesn't create a new object _
    in memory it just returns a reference to the private recordset. This function make a real clone without any dependencies. _
    Without this function user cannot have more than one open recordset

Dim rsClone As ADODB.Recordset

    'save the recordset to the stream object
    Set pStream = New ADODB.Stream
    pRs.Save pStream
    
    'and now open the stream object into a new recordset
    Set rsClone = New ADODB.Recordset
    rsClone.Open pStream
    
    'return the cloned recordset
    Set Clone = rsClone
    
    'release the reference
    Set rsClone = Nothing
    
End Function

Open in new window

0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

740 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