SQL to list function - turning a table column into a delimited list string I wrote this very "general use" function, because I needed that in some reporting. I was wondering if there is any suggetsion for improvement.

Function sql2List(sSql As String, Optional quote As String, Optional separ As String = ", ") As String
    Static db As DAO.Database
    Dim rs As DAO.Recordset
    Dim result As String
    'Debug.Print Now, sSql
    If db Is Nothing Then Set db = CurrentDb
    Set rs = db.OpenRecordset(sSql, dbOpenSnapshot)
    Do Until rs.EOF
        result = result &amp; quote &amp; rs.Fields(0).Value &amp; quote &amp; separ
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    'Set db = Nothing
    If Len(result) &gt; Len(separ) Then sql2List = Left(result, Len(result) - Len(separ))
End Function


I call it from control or module or other queries like this:

strList = sql2List("select dt from planning where client = " &amp; clientId)
In the context of the function

Function sql2List(sSql As String, Optional quote As String, Optional separ As String = ", ") As String
Static db As DAO.Database
Dim rs As DAO.Recordset
Dim result As String
'Debug.Print Now, sSql
If db Is Nothing Then Set db = CurrentDb
Set rs = db.OpenRecordset(sSql, dbOpenSnapshot)
Do Until rs.EOF
    result = result &amp; quote &amp; rs.Fields(0).Value &amp; quote &amp; separ
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
'Set db = Nothing
If Len(result) &gt; Len(separ) Then sql2List = Left(result, Len(result) - Len(separ))
End Function


The static declaration of db is probably not a good idea.

This (my reformatting is just my preference) is probably better

Set db = CurrentDb
strList = sql2List(db, "select dt from planning where client = " &amp; clientId)
Set db = Nothing

Function sql2List(db as DAO.Database,sSql As String, Optional quote As String, Optional separ As String = ", ") As String
Dim rs As DAO.Recordset
Dim result As String
Dim RetValue as string
  RetValue = ""
  'Debug.Print Now, sSql
  If not(db Is Nothing) = True Then
     Set rs = db.OpenRecordset(sSql, dbOpenSnapshot)
     Do Until rs.EOF
         result = result &amp; quote &amp; rs.Fields(0).Value &amp; quote &amp; separ
         rs.MoveNext
     Loop
     rs.Close
     Set rs = Nothing
     If Len(result) &gt; Len(separ) Then
        RetValue = Left(result, Len(result) - Len(separ))
     endif
  ELSE
     RetValue =""
  endif
  sql2List= Trim(RetValue) ' I tend to want to remove leading and training spaces
End Function


There was nothing (much) wrong with what you did other that the static inside the function as that made the function less utilitarian because there was no way that function could ever be used for any connection other than the first one. It just so happens to work because you do not desire a different connection.