ADO Recordset Basics
I get asked how to hold an Excel query table in memory from time-to-time, so it’s time for a post about it. You can use ADO (ActiveX Data Objects) to query a database object and get a recordset without having to write it to a worksheet. This post shows how I do it, and I’m no expert on the subject, so be sure to read the comments for errata and performance issues.
First, I create an Excel external data table normally through the Data > External Data menu. Then I copy the pertinent information for that query to my code. Finally, I delete the external data table. I do this because I don’t want to remember how to write connection strings and sql statements. I know, I’m lazy.
With the external data table created, I go to the Immediate Window to get what I need.
The CommandText property is the SQL statement (it was the SQL property in Excel97 and that still works in later versions). The Connection property is the connection string you’ll need to get access to the database. I paste these strings into variables in my code, clean the up a little, and add line continuation characters for easier reading.
Next, I set a reference (VBE - Tools > References) to the Microsoft ActiveX Data Objects x.x Library. Generally, pick the highest number for x.x that shows. Mine starts with 2.0 and goes to 2.8, but not every number in between is there.
Here’s the commented code to create and read the recordset. All it does is create a list of customer Ids for every customer that’s in London.
Dim adoConn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim sConn As String
Dim sSql As String
Dim sOutput As String
'Copied from the immediate window "Connection" and removed the
'ODBC from the beginning
sConn = "DSN=MS Access Database;" & _
"DBQ=C:\Program Files\Microsoft Office 2000\Office\Samples\Northwind.mdb;" & _
"DefaultDir=C:\Program Files\Microsoft Office 2000\OfficeSamples;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
'Copied from the immediate window "CommandText" and cleaned it up a little.
sSql = "SELECT CustomerID, CompanyName, Address, City, Region, PostalCode" & _
" FROM Customers" & _
" WHERE (City='London')"
'Create a new connection and open it
Set adoConn = New ADODB.Connection
adoConn.Open sConn
'Create a new recordset and open it. There are probably some
'advantages to using different cursor types, but I generally use
'the default (adOpenForwardOnly) unless I know I need to go backward
'then I use adOpenDynamic
Set adoRs = New ADODB.Recordset
adoRs.Open Source:=sSql, _
ActiveConnection:=adoConn
If Not (adoRs.BOF Or adoRs.EOF) Then 'If there are no records, this will be false
adoRs.MoveFirst 'This probably isn't necessary, particularly with the default
'cursor type,but I always include it anyway
Do While Not adoRs.EOF 'Start looping through the records
sOutput = sOutput & adoRs.Fields(0).Value & "," 'Concatenate a string
'of the first field
adoRs.MoveNext 'When you code has been running for 1/2 an hour, you
'probably forgot this line. I know I forget it all the time.
Loop
'Close stuff
adoRs.Close
adoConn.Close
'Get rid of the last comma
sOutput = Left(sOutput, Len(sOutput) - 1)
Else
sOutput = "Empty Recordset"
End If
'Output
Debug.Print sOutput
'Clean up
Set adoRs = Nothing
Set adoConn = Nothing
End Sub
The result, if you’re playing at home
“ADO Recordset Basics:
I get asked how to hold an Excel query table in memory”
From that build up I was expecting something more like this:
Sub test7()
Dim rs As Object
Set rs = CreateObject(”ADODB.Recordset”)
With rs
.CursorLocation = 3
.Fields.Append “Querytables”, 13
.Open
.AddNew
.Fields(”Querytables”).Value = _
ThisWorkbook.Worksheets(1).QueryTables(1)
.Update
Dim qt As Excel.QueryTable
Set qt = .Fields(”Querytables”).Value
MsgBox qt.CommandText
End With
End Sub
Jamie.
–
Ooh, hierarchical recordsets.
rs.Fields(2).Fields(3).Fields(0).Value
My head is spinning.
When attempting to use the ADO in my VBA procedures, I would get an error about unknow object or some such. The solution was to include the ADO components in the project as follows:
On the Project menu, select References, and then set references to Microsoft ADO Ext. for DDL and Security and Microsoft ActiveX Data Objects Library. This sample code works with both ADO 2.5 and ADO 2.6, so select the version appropriate to your computer.
I am having troubles with the connections
Recieving Error
>Runtime Error 13Mismatch Type
I have connected a SQL Server and also get value from a table of Database and now wants to access the value from database without using query,I meant to access value directly from the database table.
Pls tell me How it is possible bcs I ‘m so wory due to this problem If u help me then I will be great thankful to u for this consideration.
Thanks and Regards,
Amir Irshad Gondal
Email-irshad0207@hotmail.com
Email-irshad_207@yahoo.com
Email-irshad.gondal@gmail.com
I have one column in my Excel sheet, for which the recordset always returns value Nothing, although it clearly has (mixed texed and numeric) content.
Other columns (with text or numeric values) do not show this behaviour. Changing the format of the cell, manually typing the values again in Excel, … nothing helped. Only if the cell has text content, it is recognised. Except for when I would type into Excel ‘100 (including the quote), then it returns value 100.
Any idea why and how to solve this curious behaviour ?
Everything is ok byt I can’t find the ODBC driver for the database Postgres.
Just wondering, during the query, does it run in the background or will it run in the foreground and freeze everything until the results come out? I’m successful in doing this using MSSQL server as the backend, but when I use PostgreSQL and the mODBC driver or the psqlodbc driver, I’m faced w/ the query freezing that instance of excel until the time when the query results are returned to excel. This does not happen w/ MSSQL server.
Would really appreciate a response
Kristof Vydt wrote:
“I have one column in my Excel sheet, for which the recordset always returns value Nothing, although it clearly has (mixed texed and numeric) content…”
see: http://support.microsoft.com/kb/257819 add IMEX=1 to extended
even better is http://support.microsoft.com/kb/194124/EN-US/
I was trying to read some data from access to excel. One of the fields constains a string of 0s and 1s. When I used
Set AcsRst = New ADODB.Recordset
AcsRst.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
Cells(x,x).Value = AcsRst.Fields(”xx”).Value
Another easy way to get the connection string is to turn on the macro recorder when you build the external data table.