Excel VBA get Access database table data

Excel VBA get Access database table data

This Excel VBA will connect to an Access database and loop the designated Table.  Update Data Source with the path to your Access Database and then update rs.Open”Table1″ with your Table’s name.

 

 

Sub GetDBData()

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=Z:\My Documents\Database1.accdb;"
Set rs = New ADODB.Recordset
rs.Open "Table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable
 rs.MoveLast
 rws = rs.RecordCount
 rs.MoveFirst
 
 
 Do While Not rs.EOF
 val1 = rs("Field1")
 MsgBox "Table1 value =" & val1
 rs.MoveNext
 Loop
 rs.Close



End Sub

Interested in learning new skills?