To connect to databases using macro, you will need to add Microsoft Data Access Objects 3.6 or Microsoft ActiveX objects 2.5 (minimum) to the reference (Tools -> References). Then write the following code to connect to the database and records.

For DAO:

Declare Variable – Dim dbs As Database

Connect to database – Set dbs = OpenDatabase(C:\foldername\databasename)

Access all records – dbs.Execute “Select * from tablename”

NB: replace the tablename with your Access database table name

For ADO:

Declare Variables – Dim cn As New ADODB.Connection 

Connect to database – cn.Open “Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\foldername\databasename.mdb;”

Declare Variables – Dim rst As New ADODB.Recordset

Access all records – rst.Open “Select * From tablename”, cn

NB: cn is the variable name for the connection, rst is a variable name for recordset, replace the tablename with your Access database table name

 

Leave a Reply