Using Excel 2016 64bit with an Access DB. I created a function that gets historical data from the DB. It runs in the debug window and populates the recordset. But as soon as I try and write the data to an excel sheet, VBA just exits without an error.
I've tried several different ways of populating the data into excel including .copyfromrecordset, converting the data to strings, passing to a temporary variable. No luck. If I run the same code (adjusted as needed) as a Sub, it works perfectly. If I try passing the values to a sub first, and then to the worksheet, it also fails. It fails as long as a function is involved. I've searched for answers and can't find anything. Any ideas?
Function DAM_DD(Type_input As String, Tenor_input As String, Field_output As String, Optional Date_input As String, Optional Date_input2 As String, Optional target_cell As Range) As Variant
Dim rst As ADODB.Recordset
Dim cnt As ADODB.Connection
Dim stQuery As String, tbl_input As String, tenor_coupon As String
'<<< Bunch of code to fill in sql variables >>>
stQuery = "Select " & Field_output & " From " & tbl_input & " Where (" &
Date_input & " and Type=""" & Type_input & """ and " & tenor_coupon & ");"
'Execute query and return to a recordset
Call DAM_DB_Connection(stQuery, rst, cnt)
'Return results to excel
If rst.RecordCount <= 1 Then
DAM_DD = rst.Fields(0).Value
Else: Call Get_DAM_Hist(rst, target_cell)
End If
'Close database connection and clean up
Call Close_DB(rst, cnt)
End Function
Sub DAM_DB_Connection(stQuery As String, rst As ADODB.Recordset, cnt As ADODB.Connection)
Dim cmd As ADODB.Command
Dim stcon As String 'SQL Connection string
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
Set cmd = New ADODB.Command
'Define database connection string
stcon = "Provider=Microsoft.ACE.OLEDB.16.0;"
stcon = stcon + "Data Source='C:\Users\DAM_DB.accdb';"
'Open database connection
cnt.ConnectionString = stcon
cnt.Open
cmd.CommandType = adCmdText
cmd.ActiveConnection = cnt 'Set the command connection string
cmd.CommandText = stQuery
'Execute query and return to a recordset
rst.CursorLocation = adUseClient 'Needed for recordset count
rst.CursorType = adOpenKeyset
rst.Open stQuery, cnt, adOpenDynamic, adLockOptimistic, adCmdText
rst.MoveFirst
'Close connection in separate Sub Close_DB
End Sub
Sub Get_DAM_Hist(rst As ADODB.Recordset, target_cell As Range)
Dim ws As Worksheet
Dim Rw As Long, Col As Long, rst_end As Long, counter As Long
Dim rst_array As Variant, target_array As Variant
Set ws = Worksheets("Sheet1")
rst_end = rst.RecordCount
ReDim rst_array(0 To rst_end, 0 To rst_end)
rst_array = rst.GetRows(rst.RecordCount)
PrintArray rst_array, target_cell
' counter = 1
' Do While rst.EOF = False
' ws.Cells(counter, 8) = rst.Fields(0) <- Alternate version that fails Here
' ws.Cells(counter, 9) = rst.Fields(1)
' counter = counter + 1
' rst.MoveNext
' Loop
End Sub
Sub PrintArray(Data As Variant, target_cell As Range)
Dim Rw As Integer, Cl As Integer, i As Integer, j As Integer
Dim out_string As String
Rw = Range(target_cell).Row
Cl = Range(target_cell).Column
For i = LBound(Data, 1) To UBound(Data, 1)
For j = LBound(Data, 1) To UBound(Data, 1)
out_string = Trim(CStr(Data(i, j)))
Activesheet.Cells(Rw, Cl) = out_string '<--------- Fails here
Cl = Cl + 1
Next j
Rw = Rw + 1
Next i
End Sub
