How to use VBA in MS Word or MS Excel with MS Access Database?

Assuming that you have already had a MS Access Database setup and create a query.

1.    Make sure that Microsoft DAO Object Library is active. This can be done by go to Tools > References and check the box. In this case, I’m using Microsoft DAO 3.6 Object Library
2.    Create a sub

Sub test()

End sub

3.    Connect to the database

Dim DB As Database
Set DB = OpenDatabase(“c:\path\db.mdb”)

4.    Query the database (in this case is to call a query from the database

Dim Qd As QueryDef
Dim RS As Recordset
Set Qd As DB.QueryDefs(“myQueryName”)
Set RS = Qd.OpenRecordset()
Qd.Close

5.    Loop through the result

Do While Not RS.EOF
MsgBox RS.Fields(0).Value
RS.MoveNext
Loop
RS.Clode

6.    Passing Parameter

Qd.Parameters(0) = “param”