+ Reply to Thread
Results 1 to 2 of 2

ADO Recordset return as msgbox

Hybrid View

  1. #1
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    ADO Recordset return as msgbox

    Hi,
    just started experimenting with ADO

    I can return a sum of a range returned from Access data table to a range.
    But how do i return the value to a message box.

    Sub SelectDataFunctions() 'use to get sum/count etc of a certain column depending on criteria
        Dim MyConnection As String
        Dim MyDatabaseFilePathAndName As String
        Dim Recordset As ADODB.Recordset
        
        'Create connection string
        MyDatabaseFilePathAndName = "C:\Users\Sean\Documents\Access\Repairs.mdb"
        MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
        MyConnection = MyConnection & "Data Source=" & MyDatabaseFilePathAndName & ";"
        
        'Define the SQL Statement
        Const SQL As String = "SELECT SUM(Cost)" & "FROM CostData "
    
        
        'Initialize the Recordset object and run the query
        Set Recordset = New ADODB.Recordset
        Recordset.Open SQL, MyConnection, 0, 1, 1
        
        'Make sure records returned
        If Not Recordset.EOF Then
            'Dump the contents of the recordset onto the worksheet
            Call Sheet1.Range("L1").CopyFromRecordset(Recordset)
            'Msgbox Recordset ##
        Else
            Call MsgBox("Error: no records found", vbCritical)
        End If
        
        'Close the recordset if it is still open
        If (Recordset.State And objectstateenum.adstateopen) Then Recordset.Close
        Set Recordset = Nothing
        
    End Sub
    Regards
    Sean

    Please add to my reputation if you think i helped
    (click on the star below the post)
    Mark threads as "Solved" if you have your answer
    (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code:
    [code] Your code here [code]
    Please supply a workbook containing example Data:
    It makes its easier to answer your problem & saves time!

  2. #2
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: ADO Recordset return as msgbox

    found it!

    MsgBox Recordset.Fields(0)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1