ADO connection/SQL query in Excel function

[Steve]

I'm new to this, so be kind and answer in plain English please.

I have written a user-defined function that connects to an Access db
and performs a query, returning a value to a cell in Excel. It works
great. But I have discovered that I need to do an initial query first
and, depending on its results, have my function perform one of several
possible second queries.

My problem is I don't quite understand the mechanics of how the
connection to the db works. The code I am using, after building my
query and assigning it to the string variable SQLstr is:

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=G:\DB\CMdb.mdb;"
rst.Open SQLstr, conn
DataFetch = rst.Fields(0).Value

(DataFetch is the function's name, and the query always returns one and
only one value)

What do I need to do if I want to establish the connection, build a
SQLstr and perform a query with it, then build a second SQLstr and
perform a second query with it (the results of which will become the
function's ret val, as above)? Do I need to re-establish a connection
for each query, or will a connection early on in the function stay open
until the end? I hope I am being specific enough here. Thanks.

 

[Dick Kusleika]

Steve

I'm new to this, so be kind and answer in plain English please.

I have written a user-defined function that connects to an Access db
and performs a query, returning a value to a cell in Excel. It works
great. But I have discovered that I need to do an initial query first
and, depending on its results, have my function perform one of several
possible second queries.

My problem is I don't quite understand the mechanics of how the
connection to the db works. The code I am using, after building my
query and assigning it to the string variable SQLstr is:

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=G:\DB\CMdb.mdb;"
rst.Open SQLstr, conn
DataFetch = rst.Fields(0).Value

(DataFetch is the function's name, and the query always returns one
and only one value)

What do I need to do if I want to establish the connection, build a
SQLstr and perform a query with it, then build a second SQLstr and
perform a second query with it (the results of which will become the
function's ret val, as above)?

Make two recordset variables and two sql string variables, e.g.

Dim sqlFirst as String
Dim sqlSecond as String
dim rsFirst as ADODB.Recordset
dim rsSecond as ADODB.Recordset

sqlFirst = "blah, blah"
sqlSecond = "blah, blah"

con.Open etc..
Set rsFirst = New etc..

rsFirst.Open sqlFirst, con

If rsFirst.Fields(0).Value = "x" Then
Set rsSecond = New etc..
rsSecond.Open sqlSecond, con
FunctionName = sqlSecond.Fields(0).Value
Else
FunctionName = 0
End if

There's some pieces missing there. If you can't identify them, post back
and I'll be more complete. But it looks like you've got the basics down.

Do I need to re-establish a connection
for each query, or will a connection early on in the function stay
open until the end? I hope I am being specific enough here. Thanks.

Not for each query - you can run any number of recordsets off of a
connection. But you are reconnecting every time the function is called. If
you're only using the function once, that's not a problem. But if you use
this function for every row in you spreadsheet, that's a lot of connecting
time. You may be interested in this:

http://www.dicks-blog.com/archives/2005/01/31/using-ado-in-functions/

 

[Steve]

Thanks, Dick, I'll give it a try and let you know...

 

[Guest]

Hi Steve:

Without seeing the function it is difficult to see where you are
copying the return values to. You could very well be seeing the
last one, or the first one. Below are a couple of ways to assign those
values.

Dim rg As Range
Set rg = ThisWorkbook.Worksheets(1).Range("a1")
rg.CopyFromRecordset rs

Also you can use:

With rs
.MoveFirst
Do Until .EOF
‘If you are coping to a listBox
ListBox1.AddItem rs("Field") & vbTab & rs("Field")

‘If you are coping to a Worksheet not using CopyFromRecordset

Worksheets("Sheet1").Range("A" & iRow) = rs("Field")
Worksheets("Sheet1").Range("C" & iRow) = rs("Field")
.MoveNext
iRow = iRow + 1

Loop
End With


Also you can base a SQL statement on another SqL Statement but you might
want to practice a bit before that.

Also it is recommended to close the connection when not in use by those
that recommend things like that.

Search the post in this NG for “Get Names From Access Table†5/25/05
For an function example by one of the MVP and a procedure by myself.

Good Luck
TK

 

[Steve]

Dick (or anyone),

Tried your suggestions, I am still having problems. Below is the basic
structure of my code. What is curious is that if I create it as a Sub
and run the macro, using CopyFromRecordset to dump the final results of
my two queries into a worksheet, this code runs fine. But if I do it
as a Function, then it does not work and exits the function when I get
to the second recordset open (rst2.Open) command. Any ideas?

Function DataFetch (input parameters here)

Dim conn As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
Dim SQLstr1 As String
Dim SQLstr2 As String
Dim ProjNum As String

SQLstr1 = "SELECT blah blah ..."

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=G:\DB\CMdb.mdb;"
rst1.Open SQLstr1, conn

If rst1 = condition Then (change the value of one of the input
parameters)

rst1.Close

SQLstr2 = "SELECT blah blah ..." (this query will return one and
only one value)

rst2.Open SQLstr2, conn
DataFetch = rst2.Fields(0).Value

rst2.Close
conn.Close

End Function