You can use all the Access macro instruction from Excel VBA. Just get the
object
set obj = Getobject("ledger_obj.mdb")
then in Excel VBA add the reference from menu tools - References
Microsoft Access 11.0 Library object (or latest on your PC)
if you are using ADO methods
Microsoft ActiveX Data Object 2.8 library (or latest on your PC)
Or DAO method
Microsoft DAO 3.5 object Library (or Latest)
Use the Access VBA help to get help for particular method you are using.
Sub SQLX()
Dim dbsNorthwind As Database
Dim qdfTemp As QueryDef
Dim rstEmployees As Recordset
Set dbsNorthwind = GetObject("Northwind.mdb")
Set qdfTemp = dbsNorthwind.CreateQueryDef("")
' Open Recordset using temporary QueryDef object and
' print report.
Call SQLOutput("SELECT * FROM Employees " & _
"WHERE Country = 'USA' " & _
"ORDER BY LastName", qdfTemp)
' Open Recordset using temporary QueryDef object and
' print report.
Call SQLOutput("SELECT * FROM Employees " & _
"WHERE Country = 'UK' " & _
"ORDER BY LastName", qdfTemp)
dbsNorthwind.Close
End Sub
Function SQLOutput(strSQL As String, qdfTemp As QueryDef)
Dim rstEmployees As Recordset
' Set SQL property of temporary QueryDef object and open
' a Recordset.
qdfTemp.Sql = strSQL
Set rstEmployees = qdfTemp.OpenRecordset
Debug.Print strSQL
With rstEmployees
' Enumerate Recordset.
Do While Not .EOF
Debug.Print " " & !FirstName & " " & _
!LastName & ", " & !Country
.MoveNext
Loop
.Close
End With
End Function