VBA to import Access data table with field names

[KP_SoCal]

Hi all, I'm running the following subroutine within an excel module to import a table from an access database into the worksheet named Summary starting from row A3.

The nice thing about this code is that it allows me to import starting from any row I designate. The one thing that I can NOT figure is how to import the FIELD NAMES of the table.

Does anyone have any ideas on how to modify the code below to bring in the field names with the raw data as opposed to just the row data?

Private Sub PullSummaryData()
Const strDb As String = "C:\db\AccessDatabase.accdb"
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

Sheets("Summary").Select

  Const strQry As String = "SELECT * FROM [AccessDataTable]"

  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDb & ";"

  Set rs = New ADODB.Recordset

  With rs
    Set .ActiveConnection = cn
    .Open strQry
  End With

  Sheets("Summary").Range("A3").CopyFromRecordset rs

  rs.Close
  cn.Close
End Sub

 

[ChrisM]

Sorry, don't have sample code with me, but I can give you the high level details.

Before looping through your recordset, you need to loop through the field collection of your recordset, and assign those values to row 1 of your output. Then jump down to your data, and start assigning that at row 2.

Something like this:

For i = 1 to rs.Fields.Count
     MySheet.Cells(1,i) = rs.fields(i)
Next i

 

[Richard Schollar]

Hi

You need to loop thru the Fields collection:

Private Sub PullSummaryData()
Const strDb As String = "C:\db\AccessDatabase.accdb"
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim i As Integer

Sheets("Summary").Select

  Const strQry As String = "SELECT * FROM [AccessDataTable]"

  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDb & ";"

  Set rs = New ADODB.Recordset

  With rs
    Set .ActiveConnection = cn
    .Open strQry
  End With

With Sheets("Summary")
  For i = 1 To rs.Fields.Count
    .Cells(2,i).Value = rs.Fields(i-1).Name  'fields is a 0 based collection
  Next i
  .Range("A3").CopyFromRecordset rs
End With

  rs.Close
  cn.Close
End Sub

Which will place the fieldnames on row 2 of your spreadsheet (amend as required).

 

[KP_SoCal]

Outstanding!!!! This is precisely what I needed!!! Thanks so much!!!!

 

[Norie]

Why not just import the query to Excel or export it from Access?

Isn't using ADO a bit too much trouble to use? Especially when I can't see anything in your code that makes it an advantage.

 

[KP_SoCal]

Why not just import the query to Excel or export it from Access?

Isn't using ADO a bit too much trouble to use? Especially when I can't see anything in your code that makes it an advantage.

Hi Norie, I agree with you that under normal circumstances it would be more practical to export the data from access, but I have a lot more going on within my excel workbook where I need the flexibility that the ADO gives me.