CODEWITHSUNDEEP
excelvbams-access
barciewicz
barciewicz

Reputation: 3773

Print Access table contents through VBA

I want to read an Access table through Excel VBA and print the contents of each each row and each column.

So far I have the following code, but it will only print the contents of the first row. How do I make it print all rows?

My test table has 2 columns (hence the For i= 0 to 1) and 4 rows, but in reality I will possibly have variable number of columns and rows, so preferably the code should cover for that.

Sub get_column()

Dim cn As Object
Dim rs As Object
Dim strSql As String
Dim strConnection As String
Dim i As Long

Set cn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=M:\test_database.accdb;" '& _
'"User Id=admin;Password=admin"
strSql = "SELECT Table1.CC_Number, Table1.Region FROM Table1;"
cn.Open strConnection
Set rs = cn.Execute(strSql)
For i = 0 To 1
    Debug.Print rs.Fields(i)
Next i
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

Upvotes: 1

Views: 931

Answers (1)

Erik A
Erik A

Reputation: 32642

You can easily loop over all fields, and use .MoveNext and .EOF to print all rows:

Set rs = cn.Execute(strSql)
Dim fld As Object
Do While Not rs.EOF
    For Each fld In rs.Fields
        Debug.Print fld.Name; vbTab; fld.Value
    Next
    Debug.Print "" 'Blank line between records
    rs.MoveNext
Loop
rs.Close

Upvotes: 1

Related Questions