Access connection works in Macro, not in user-defined Function

[Steve]

Still trying to figure this one out. I am connecting to Access in a
user-defined function and trying to run two separate queries off the
same connection. 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. Below is the basic
structure of my code. 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

 

[Tom Ogilvy]

Some functions don't work or have an altered behavior when used in a UDF. I
wouldn't be surprised if copyfromrecordset is one of these.

Just some added, a UDF used in a worksheet can not alter any value on the
worksheet - it can only return a single value to the cell in which it is
located which is done by assigning the result to the name of the UDF. This
is one big reason I wouldn't see copyfromrecordset working in a UDF.

However, you don't show any copyfromrecordset commands in your routine.



--
Regards,
Tom Ogilvy


Still trying to figure this one out. I am connecting to Access in a
user-defined function and trying to run two separate queries off the
same connection. 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. Below is the basic
structure of my code. 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

 

[Steve]

Maybe I wasn't clear. I don't use CopyFromRecordset in my function. I
put the same code as my function into a Sub, with the only difference
being inserting a CopyFromRecordset so that I could check if the query
values were being returned correctly. They were, and the macro ran
fine. But when I take the CopyFrom out and use the code in a function
(which doesn't alter any values in the worksheet), it fails and exits
at the second rst2.Open command.

 

[Steve]

Got it working, it was my bad, changing the value of the wrong
parameter after the first query. Thanks for all the help.