How to connect to MS access from Excel

The code below is a sample code which connects to MS access from Excel. Please note that you need to enable ActiveX Data Objecys X.X library in the reference to use this code.

Sample Excel file:
https://www.dropbox.com/s/tszrdiy3jzvjq5r/Sample.zip?dl=0

Code:

Private Sub Test()
    Dim adoCON      As New ADODB.Connection
    Dim adoRS       As New ADODB.Recordset
    Dim strSQL      As String
    Dim odbdDB      As Variant
    Dim wSheetName  As Variant
    Dim i           As Integer
    Dim Num1        As Integer
   
    odbdDB = ActiveWorkbook.Path & "\DB_name.accdb"

    adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                        & "Data Source=" & odbdDB & ""
    adoCON.Open
    adoCON.BeginTrans

    adoRS.CursorLocation = adUseClient

    'SQL for the database
        strSQL = "SELECT * FROM  table_name ORDER BY table_name.ID;"
    'If you want to search a certain thing, write as follows:
    'strSQL = "SELECT table_name.* FROM table_name WHERE subject_of_the_table = " & TextBox1.Value & ";"
    'If this is string data type, you must write as: strSQL = "SELECT * FROM table_name WHERE subject_of_the_table  LIKE '" & TextBox1.Value & "';"

    adoRS.Open strSQL, adoCON, adOpenDynamic
 
    i = 1

    Do Until adoRS.EOF
        With Worksheets("Sheet1")
            .Cells(i, 1).Value = adoRS!ID
            .Cells(i, 2).Value = adoRS!Name
            .Cells(i, 3).Value = adoRS!age
        End With
        i = i + 1
        adoRS.MoveNext
    Loop
    adoCON.CommitTrans

    adoRS.Close
    Set adoRS = Nothing
    adoCON.Close
    Set adoCON = Nothing
   
End Sub

Highlighted words with blue are depending on your MSaccess file. So you must change the blue-highlighted-words depending on your MSaccess file.

Although you copied and pasted this code to your VBA editor's window, you can't access to the database? If so, maybe you have not changed the setting of  references.

If you see the following error message:
User-defined type not defined
Then it is probably because of the references settings.

Go to the VBA editor.

Then select Tools from the menu. Then select "References".

If you can not click the references button, maybe some macro is working. Click the stop button as follows;

Then you see the below window. Look for Microsoft ActiveX Data Objects X.X Library. The version of ADO can be different, so select the latest one.

Click "OK" in the upper right of the window. Now you can use the ADO in your Excel.