vba - Excel UDF to return array from ACE SQL recordset -

I'm trying to use AE SQL to return the array from the reset set. The function works well with table categories.

Issue: The function returns the correct number of records if the query returns 2 or more records. However, if only one record is found, then all rows are duplicated with one line. This is wrong but I do not know the reason.

In addition, I want my function to return the column names from the recordsetset. I do not know how to set up with the array to return from the record set

Here is the code, credits are due to the author of another solution which I am trying to adapt to my needs:

  function different application as SQL (string in the form of the datestrain, in the form of crates). Volatile Dim cn ADODB.Connection as ADODB.Recordset dim currAddress in the form of a dim string string currAddress = ActiveSheet.Name & amp; "$" & Amp; DataRange.Address (false, false) strFile = ThisWorkbook.FullName strCon = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" & amp; Strong_ and amp; "; Extended Properties =" "Excel 12.0; HDR = Yes; IMX =" 1 ";" Set cn = CreateObject ("ADODB.Connection") set rs = CreateObject ("ADODB.Recordset") cn.Open strCon strSQL = "SELECT * FROM [" & amp; CurrAddress & amp; "]" & Amp; _ "Where [a] = '" & amp; Crit A & A "'" & "Amp" _ "1 order by command" rs.Open strSQL, cn 'sq = rs.getString sql = application.Transpose (rs.GetRows) set RS = nothing set CN = nothing end function  

If you want a field name, you must also loop through the array from the records:

< If not for IOF again (vData, 2) + 1, LBound (vData, 1) for vdata = .getrows () redeem vDataOut (Udaada (vData, 1)) with pre> For I = 1 for Fields Count vDataOut (0, i - 1) = .filds (i - 1) .Name forward, I copy data for U = Bound (vData, 2) for V = Bound (vData, 2) Y = Lbound (vData, 1) For Example with UBound (vData, 1) vDataOut (x + 1, y) = vData (y, x) next y next x end if end end with

.