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
Bookmarks