Solved

VBA Access ADO Recordset Field Names Output

Posted on 2013-01-30
4
1,051 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
  • 2
  • 2
4 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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 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 how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

746 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

11 Experts available now in Live!

Get 1:1 Help Now