Stntax:

Retrieve data from Access Table:

Sub Querytableadd_Access()
Dim qtable As QueryTable
Dim scon As String
scon = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data source=E:Data\Test.accdb;"
Set des = Sheet2.Range("A1")
'create the Query table
Set qtable = Sheet1.QueryTables.Add(scon, des)
'INFO is the table name
qtable.CommandText = "info"
qtable.CommandType = xlCmdTable
qtable.Refresh
End Sub

Access - ADODB.Connection - Required Columns

Private Sub CommandButton1_Click()
'open connection
Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=E:Data\Test.accdb;" & _
"User Id=admin;Password="
cn.Open
'open recordset
Dim rs As New ADODB.Recordset
Set rs.ActiveConnection = cn
rs.Open "select * from info"
i = 2: j = 1
For i = 2 To 5
Cells(i, 1) = rs.Fields(0).Value
Cells(i, 2) = rs.Fields(1).Value
rs.MoveNext
Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Access - ADODB.Connection - Entire table

Sub AccessConnections_Final()
'open connection
Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=E:Data\Test.accdb;" & _
"User Id=admin;Password="
cn.Open
'open recordset
Dim rs As New ADODB.Recordset
Set rs.ActiveConnection = cn
Query = "select * from info"
rs.Open Query
Range("A1").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Access - ADODB.Connection - EOF

Sub Access_Connections()
'open connection
Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=E:Data\Test.accdb;" & _
"User Id=admin;Password="
cn.Open
'open recordset
Dim rs As New ADODB.Recordset
Set rs.ActiveConnection = cn
rs.Open "select * from info"
i = 2:
Do Until rs.EOF
Cells(i, 1) = rs.Fields(0).Value
Cells(i, 2) = rs.Fields(1).Value
i = i + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub