MS Access in VBA

Private Sub CommandButton1_Click()

Dim wbCopy As Worksheet

Set wbCopy = ActiveSheet

' Click on Tools, References and select

' the Microsoft ActiveX Data Objects 2.0 Library

Dim DBFullPath As String

Dim Connect As String, Source As String

Dim Connection As ADODB.Connection

Dim Recordset As ADODB.Recordset

Dim Col As Integer

Dim found As Integer

'Cells.Clear

'Database path info

'DB Path

DBFullPath = "E:\Projects\test.accdb"

'Open the connection

Set Connection = New ADODB.Connection

Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"

Connect = Connect & "Data Source=" & DBFullPath & ";"

Connection.Open ConnectionString:=Connect

'Create RecordSet

Set Recordset = New ADODB.Recordset

Recordset.CursorLocation = adUseClient

With Recordset

'Filter Data

Source = "SELECT * FROM tbl1 WHERE (((tbl1.[fname]) like '%" & wbCopy.Range("A2") & "%')); "

.Open Source:=Source, ActiveConnection:=Connection

'MsgBox "The Query:" & vbNewLine & vbNewLine & Source

'Write field names

For Col = 0 To Recordset.Fields.Count - 1

wbCopy.Range("B1").Offset(0, Col).Value = Recordset.Fields(Col).Name

Next

'Insert rows

found = Recordset.RecordCount

For i = 1 To found - 1

wbCopy.Rows("3:3").Select

Selection.Insert Shift:=xlDown

Next

'Write recordset

wbCopy.Range("B1").Offset(1, 0).CopyFromRecordset Recordset

End With

ActiveSheet.Columns.AutoFit

Set Recordset = Nothing

Connection.Close

Set Connection = Nothing

End Sub

For binding see:
 https://www.devhut.net/2017/02/16/vba-early-binding-and-late-binding-part-2/
https://stackoverflow.com/questions/35647732/excel-vba-late-bind-microsoft-dao-3-6-object-library

For connection strings
https://www.connectionstrings.com/access-2013/