Much ado about ADO

First, I apologise for the title – it must be one of the oldest geek data jokes.

Anyway, I often see questions about retrieving data from databases (of any sort – Oracle, Access, SQL Server or even Excel or other flat files) so I thought I’d provide some simple examples.

To begin, here is a sample for returning data from an Access database. Different provider strings are required depending on the version of Office you have, so I’ve put both in the code with comments. A quick note – as written the code is early bound so it requires a reference to a Microsoft ActiveX Data Objects library of one version or another. It can of course be late bound as well, but I leave that as an exercise for the reader – if you read my earlier post on late binding, it should be a piece of cake. 😉

Sub GetAccessData()
' Sample demonstrating how to return a recordset from an Access db
' requires a reference to the Microsoft ActiveX Data Objects Library.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim wks As Excel.Worksheet
Dim commandText As String
Dim sPathToDB As String
Dim i As Long

' sheet for output
Set wks = ActiveSheet

' Path to database
PathToDB = "C:\test\db1.accdb"

Set cn = New ADODB.Connection
' open connection to database
With cn
.CursorLocation = adUseServer
.ConnectionTimeout = 500

' For Excel 2003 and earlier
'.Provider = "Microsoft.Jet.OLEDB.4.0"

' For Excel 2007 and later
.Provider = "Microsoft.ACE.OLEDB.12.0"

.ConnectionString = "Data Source=" & sPathToDB & ";"
.CommandTimeout = 500
End With

' SQL query string - change to suit
commandText = "SELECT * FROM tblTest"

' Create New Recordset
Set rs = New ADODB.Recordset

' open recordset using query string and connection
With rs
.CursorLocation = adUseServer
.Open commandText, cn, adOpenStatic, adLockPessimistic, adCmdText
' check for records returned
If Not .EOF Then
'Populate field names
For i = 1 To lngFieldCount
wks.Cells(1, i).Value = .Fields(i - 1).Name
Next i

' Copy data to sheet starting from A2
wks.Cells(2, 1).CopyFromRecordset rs

End If
End With

' clean up
End Sub

In future posts (which, let’s face it, will probably be next year at the current rate of progress), I will demonstrate variations on this theme including:

  • Different ways to retrieve the data from the recordset
  • Shaped recordset
  • Multiple recordsets
  • Different data sources

Leave a Reply

Your email address will not be published. Required fields are marked *