Error Calling A subroutine from a User Defined Function (UDF) that

[Carlos]

I don't understand why I get an "Unspecified Automation Error" when calling a
subroutine from a UDF. If I run the code from an Excel Button calling the
subroutine as a macro no errors are reported. Any light on this... here is
the code:

----------------------------------------------------------------------------------
Option Explicit

Public Sub GetAccessData()
On Error GoTo ErrorHandler
'Step 1: Declare your Variables
Dim MyConnect As String
Dim DBConn As ADODB.Connection
Dim MyRecordset As ADODB.Recordset

'Step 2: Declare your Connection String
MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= C:\Documents and Settings\csanchez\My
Documents\My Databases\TrendGraphics.accdb"

'Step 3: Instantiate and Specify your Recordset
Set DBConn = New ADODB.Connection
DBConn.Open MyConnect
Set MyRecordset = New ADODB.Recordset
MyRecordset.Open "BrokersQry", MyConnect, adOpenStatic, adLockReadOnly

'Step 4: Copy the Recordset to Excel
Sheets("Test").Select
ActiveSheet.Range("A2").CopyFromRecordset MyRecordset

'Step 5: Add Column Labels
With ActiveSheet.Range("A1:C1")
.Value = Array("Product", "Description", "Segment")
.EntireColumn.AutoFit
End With
Exit Sub

ErrorHandler:
MsgBox "Error Captured"
End Sub
Function Test() As Integer
Call GetAccessData
End Functio
----------------------------------------------------------------------------------
If I set the fomolling formula in a given cell: =Test() , an error is
triggered once the GetAccessData() code reaches this statement:

ActiveSheet.Range("A2").CopyFromRecordset MyRecordset

But it will execute without problems if called by running as a Macro from a
button.

----
I am not knowledgable about ADO and this is part of my testing for the
ultimate goal: Need to execute a parameter query in Access 2007 that returns
a single value to an Excel 2007 cell many times over (50+). I need to place
the returned value to a cell and then analyze the results within excel. THis
is why I want to use a UDF to return the Access query result.

THanks in advance for any help.

 

[Barb Reinhardt]

This might help

http://support.microsoft.com/kb/170787

 

[Carlos]

FOr those that may be interested here is a solution to the problem, at least
in my case it does resolve the issue:

Option Explicit

Public Sub GetAccessData(ByRef RSLT As String)
On Error GoTo ErrorHandler
'Step 1: Declare your Variables
Dim MyConnect As String
Dim DBConn As ADODB.Connection
Dim MyRecordset As ADODB.Recordset
Dim Brokers As Variant

'Step 2: Declare your Connection String
MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= C:\Documents and Settings\csanchez\My
Documents\My Databases\TrendGraphics.accdb"

'Step 3: Instantiate and Specify your Recordset
Set DBConn = New ADODB.Connection
DBConn.Open MyConnect
Set MyRecordset = New ADODB.Recordset
MyRecordset.Open "BrokersQry", MyConnect, adOpenStatic, adLockReadOnly

'Step 4: Copy the Recordset to Excel
Brokers = MyRecordset.GetRows(1, 0, 0)
RSLT = Brokers(0, 0)

Exit Sub

ErrorHandler:
MsgBox "Error Captured"
End Sub
Function Test() As Integer
Dim RSLT As String
Call GetAccessData(RSLT)
Test = RSLT
End Function