Solved

VBA Access ADO Recordset Field Names Output

Posted on 2013-01-30
4
1,077 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…

932 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

9 Experts available now in Live!

Get 1:1 Help Now