Steve

Steve wrote:
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/2...-in-functions/

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com