Normally, Data > Get External Data is a fine option for using data from an external source in you worksheets. There are situations, though, where the structure of an external data table isn’t what you want. You may want to use pieces of the data in various places, rather than in a tidy table.
In a situation I recently encountered, I needed the user to specify a key field of a recordset and have certain information returned. Normally, I would create a hidden worksheet and put an external data table on it with the whole recordset. Then, I would use VLOOKUPs to get at the data based on the user’s entry. The problem was that the recordset was more than 65,000 rows. I tried to think of a way to limit the data in the recordset, but I couldn’t predict what the users would enter, and therefore needed access to the whole thing.
I ended up using a user-defined function with ADO automation. Here’s generally what it looked like. This example uses the Products table of Northwind.mdb.
Dim adoRs As ADODB.Recordset
Function GetFields(sKey As String, lField As Long) As Variant
Dim sCon As String, sSql As String
‘Create recordset if first call
If adoCn Is Nothing Or adoRs Is Nothing Then
sCon = “DSN=MS Access Database;” & _
“DBQ=C:Program FilesMicrosoft Office 2000OfficeSamplesNorthwind.mdb;” & _
“DefaultDir=C:Program FilesMicrosoft Office 2000OfficeSamples;” & _
“DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;”
sSql = “SELECT ProductID, ProductName, QuantityPerUnit, Products.UnitPrice “ & _
“FROM Products”
Set adoCn = New ADODB.Connection
adoCn.Open sCon
Set adoRs = New ADODB.Recordset
adoRs.CursorType = adOpenDynamic
adoRs.CursorLocation = adUseClient
adoRs.Open sSql, adoCn
End If
adoRs.MoveFirst
adoRs.Find “ProductID=” & sKey
If adoRs.EOF Or adoRs.BOF Then
GetFields = “Not found”
Else
GetFields = adoRs.Fields(lField).Value
End If
End Function
And it was used, more or less, like this:
Mmmmmm. Klosterbier…
Ha! Of all the products, I choose beer. It must be fate. That is beer right?
Dick, Because your cursor is client-side, you could disconnect your recordset and close the connection e.g. put these lines after the adoRs.Open:
adoRs.ActiveConnection = Nothing
adoCn.Close
I prefer to use Filter rather than Find but that’s just a lifestyle choice (mine’s a Steeleye Stout, cheers).
Jamie.
What if the user doesn’t have MS Access? Would it still work?
For instance, when I crunch my UPS files (50,000+ lines per month) I can’t ever do it on a quarterly basis. I have Access, but one of the managers who would like to see the data summed by quarter doesn’t.
Could I use this to get the quarter sums to them?
Stacie
Stacie
Yes, it will work fine. MDAC / ADO contains everything you need to read/write to databases (except a user interface, that is). I have lots of users that use Access mdb files from Excel and Word, without knowing it and without Access installed on their systems.
“MDAC / ADO contains everything you need to read/write to [Jet] databases”
I that is not 100% correct:
‘the Jet database engine is included in versions earlier than MDAC 2.6. MDAC also includes several ODBC drivers and OLE DB providers for common data sources, including … Jet (earlier than MDAC 2.6).
In MDAC versions that are later than MDAC 2.6, Jet is not included with the MDAC components set; therefore, the Jet engine core components, the Jet ODBC driver, and the Jet OLE DB provider are no longer included as a part of MDAC.’
However, there is still hope:
‘The latest Jet components are available as a part of MDAC 2.5 SP2. If you want to use MDAC 2.6 or later (including the latest Jet components), first install MDAC 2.5 SP2, and then upgrade to the latest Jet components.’
Extracts from:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/mdacsdk/htm/mdac_deprecated_components.asp
Jamie.
Doh! Wrong link. It should have been:
Microsoft Data Access Components (MDAC) Installation
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/data_mdacinstall.asp
For the MDAC donloads, see:
MDAC Downloads
http://msdn.microsoft.com/data/mdac/default.aspx
Jamie.
… although I’ve just noticed there may be a problem for Windows XP.
The table says in the first article reads:
Windows XP, MDAC 2.5 SP2: Cannot be installed
I can’t get Microsoft’s DLL database to confirm but I still think it is a safe bet that if a user has Excel then they will have ADO and a Jet version that can be updated to the latest version of Jet 4.0 via a Windows Update.
Jamie.
Hi Jamie
I wasn’t aware of this, thanks. 2.5 is definitely a good version anyway, does the job and more. (2.7.0 is btw known to be a buggy monster)
I think that Paradox and MySql are not in the drivers package, so “databases” is also below 100%. Oh well.
Best wishes Harald
I receive a compilation erro: Type not declared on the first line:
“Dim adoCn As ADODB.Connection”.
Could anybody explain if I need to set a reference to a library ( I already tried setting reference to Microsoft ADO Ext. 2.8 for DDL and Security) but that doesn’t help).
Thanks for any help!
Martin
P.S. I Use excel 2003
Martin,
You need to connect to the ‘Microsoft ActiveX Data Objects 2.X Library’ where X might be 2.5, 2.6, etc – whatever is the latest installed on your system.
This is not the same library as Microsoft ADO Ext. 2.8 for DDL and Security.
cheers,
Christopher