Cannot copy Recordset After Using VBA Function

[CPGDeveloper]

I have an MS Access based Reporting Applications that outputs reports to Excel. In the back-end I have a table similar to this:

ID, Comment
1, Comment1
1, Comment2
1, Comment3


And from this, I have a request to create something like this:

ID, Comments
1, Comment1, Comment2, Comment3

I found a Function that achives this --

Public Function GetList(SQL As String _
, Optional ColumnDelimeter As String = ", " _
, Optional RowDelimeter As String = vbCrLf) As String
'PURPOSE: to return a combined string from the passed query
'ARGS:
' 1. SQL is a valid Select statement
' 2. ColumnDelimiter is the character(s) that separate each column
' 3. RowDelimiter is the character(s) that separate each row
'RETURN VAL: Concatenated list
'DESIGN NOTES:
'EXAMPLE CALL: =GetList("Select Col1,Col2 From Table1 Where Table1.Key = " & OuterTable.Key)


Const PROCNAME = "GetList"
Const adClipString = 2
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sResult As String


On Error GoTo ProcErr


Set oConn = CurrentProject.Connection
Set oRS = oConn.Execute(SQL)


sResult = oRS.GetString(adClipString, -1, ColumnDelimeter, RowDelimeter)


If Right(sResult, Len(RowDelimeter)) = RowDelimeter Then
sResult = Mid$(sResult, 1, Len(sResult) - Len(RowDelimeter))
End If


GetList = sResult
oRS.Close
oConn.Close


CleanUp:
Set oRS = Nothing
Set oConn = Nothing


Exit Function
ProcErr:
' insert error handler
Resume CleanUp


End Function


It works well -- however, after running a query using this function, I cannot Export the Recordset or even Copy it. I don't get an error, but it simply hangs for well over 20 minutes before not responding. Perhaps there is a more efficient way to achieve this result? Any help would be greatly appreciated.

 

[stumac]

I haven't used the function you are using (I have seen it before), but have used this one: http://allenbrowne.com/func-concat.html and was able to export it in the past.

Have you tried using it with a subset of your data to see if it is just the amount of data that you are passing is causing the crash? If so then it may be worth running the initial query as a make table query and exporting the resulting table.