Connect to Access from Excel using ADO

Here's the Excel VBA code I used to pull data from an Access query into an Excel Spreadsheet. It also brings in the column names. If you regularly have to pull data from Access or SQL server and then copy it to Excel this can help cut out some of those steps. I usually do a lot of formatting in code after bringing the data in, but left it out in this example in order to focus on the data extraction.

In order for this to work you need to add a refernce to ADO in Excel. In the Visual Basic Editor (VBE) go to Tools, References and search for Microsoft ActiveX Data Objects 2.8 Library. Click the checkbox and click OK.

Dim SQLcmd As String

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

Dim fld As ADODB.Field

Dim Row As Integer

Dim Column As Integer

SQLcmd = "SELECT * FROM [AccessQueryName]"

rs.Open Source:=SQLcmd, _

ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=N:\MyFolder\MyDatabaseName.mdb" + _

"; User Id=admin; Password="

Column = 1

Row = 1

For Each fld In rs.Fields

Cells(Row, Column).Value = fld.Name

Cells(Row, Column).Select

With Selection.Interior

.ColorIndex = 15

.Pattern = xlSolid

End With

Selection.Font.Bold = True

Column = Column + 1

Next fld

Cells(2, 1).CopyFromRecordset rs

Cells.Select

Cells.EntireColumn.AutoFit