Excel VBA: Connect to Microsoft Access Database (MDB) using ADODB

First, you have to enable Microsoft ActiveX Data Object Library. Follow this instruction:
1. Open Visual Basic for Application, click Tools, then click Add References

2. Search for   Microsoft ActiveX Data Object Library

This is an example code to connect to ADODB:

Private Sub CommandButton1_Click()
Dim adoConn As New ADODB.Connection
Dim adoRS As New ADODB.Recordset

    adoConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb; Persist Security Info=False"
    adoConn.Open

    adoRS.Open "SELECT COUNT([employee_id]) AS empID FROM [Table1]", adoConn, adOpenKeyset, adLockOptimistic
   
    ActiveSheet.Range("A1").Value = adoRS!empID   
    If adoRS.State = adStateOpen Then adoRS.Close
    If adoConn.State = adStateOpen Then adoConn.Close
   
End Sub