Microsoft Access VBA: Convert Query or SQL to String

I wanted to take a query and format part of its output into English; a list of names separated by commas.

The DAO Object Library includes a completely pointless Recordset.GetRows command which returns an unwieldy multi-dimensional array. How pointless and unwieldy? Well, the supplied example to Debug.Print FirstName, LastName and Title from Northwind.mdb’s Employees table is 90 lines long. Without GetRows it would take way less than 10 lines. Amazingly, when Microsoft introduced ADO, it would retain the spectacular uselessness of this command by continuing to return a multi-dimensional array.

These functions take a query or table name or SQL command and a simple string to format the output which can contain any text with fields from the query surrounded by square brackets.

The default parameters produce a comma-delimited list with the final item delimited with an “, and” but these can be replaced with anything.

Parameters governing what is returned when there is no data in the query and what is displayed on screen as the function executes are also included and are documented in the source code.

Examples

?FromQuery(“MyTable”,”[Name] ([Age]-years-old)”)

Peter (71-years-old), Paul (71-years-old), and Mary (72-years-old)

?FromQuery(“MyTable”,”[Name] ([Age]-years-old)”, vbCrLf, vbCrLf)

Peter (71-years-old)
Paul (71-years-old)
Mary (72-years-old)

Source Code

''' <summary>
''' Converts a query to a string. Each record is formatted using Fields where field names
''' are surrounded by square brackets. Each record is separated by Delimiter.
''' </summary>
'''
''' <paramref name="Fields">A string used to format each record. Field names surrounded by square
'''                         brackets are replaced with the field value. For example, "Name: [Name]"
'''                         would produce a record that looks something like "Name: Paul J. Champion"
'''
''' <paramref name="Delimiter">Each record is separated with this string.
'''                            For example, if the Delimiter is ", " you would get
'''                            "Record 1, Record 2, Record 3, " etc
'''
''' <paramref name="FinalDelimiter">The last record is separated with this string.
'''                                 For example,if the FinalDelimiter is ", and " you would get
'''                                 "Record 1, Record 2, and Record 3"
'''
''' <paramref name="NoRecords">If there are no records available, this message is returned unaltered.
'''
''' <paramref name="ProgressText">If ProgressText is supplied, then a progress bar is shown in status bar.
'''                               If ProgressText is set to an empty string, no progress bar is shown.
''' </paramref>
Public Function FromQuery( _
                QueryName As String, _
                Fields As String, _
                Optional Delimiter As String = ", ", _
                Optional FinalDelimiter As String = ", and ", _
                Optional NoRecords As String = "No data", _
                Optional ProgressText As String = "Processing FromSQL query") _
                As String
  FromQuery = FromSQL(CurrentDb.QueryDefs(QueryName).SQL, Fields, Delimiter, FinalDelimiter, NoRecords, ProgressText)
  End Function

''' <summary>
''' Converts a SQL query to a string. Each record is formatted using Fields where field names
''' are surrounded by square brackets. Each record is separated by Delimiter.
''' </summary>
'''
''' <paramref name="Fields">A string used to format each record. Field names surrounded by square
'''                         brackets are replaced with the field value. For example, "Name: [Name]"
'''                         would produce a record that looks something like "Name: Paul J. Champion"
'''
''' <paramref name="Delimiter">Each record is separated with this string.
'''                            For example, if the Delimiter is ", " you would get
'''                            "Record 1, Record 2, Record 3, " etc
'''
''' <paramref name="FinalDelimiter">The last record is separated with this string.
'''                                 For example,if the FinalDelimiter is ", and " you would get
'''                                 "Record 1, Record 2, and Record 3"
'''
''' <paramref name="NoRecords">If there are no records available, this message is returned unaltered.
'''
''' <paramref name="ProgressText">If ProgressText is supplied, then a progress bar is shown in status bar.
'''                               If ProgressText is set to an empty string, no progress bar is shown.
''' </paramref>
Public Function FromSQL( _
                SQL As String, _
                Fields As String, _
                Optional Delimiter As String = ", ", _
                Optional FinalDelimiter As String = ", and ", _
                Optional NoRecords As String = "No data", _
                Optional ProgressText As String = "Processing FromSQL query") _
                As String

  Dim Record As String

  Dim rs As DAO.Recordset ' requires Reference to DAO Object Library
  Set rs = CurrentDb.OpenRecordset(SQL)

  If Not rs.EOF Then

    ' Setup progress bar if required
    ' Initialise boolean variable as it will be quicker for later on
    Dim ShowProgress As Boolean
    ShowProgress = ProgressText <> ""

    If ShowProgress Then
      SysCmd acSysCmdInitMeter, ProgressText, 100
      rs.MoveLast ' to ensure recordset is populated
      rs.MoveFirst
    End If

    Dim FirstField As Boolean: FirstField = True

    Do While Not rs.EOF

      ' Reset Record
      Record = Fields

      ' Insert fields from SQL into RecordHTML
      Dim fld As DAO.Field
      For Each fld In rs.Fields
        Record = Replace$(Record, "[" & fld.Name & "]", Nz(fld.value), , , vbTextCompare)
      Next

      ' Add this record to return value FromSQL
      If Not FirstField Then
        If rs.AbsolutePosition + 1 = rs.RecordCount Then
          FromSQL = FromSQL & FinalDelimiter
        Else
          FromSQL = FromSQL & Delimiter
        End If
      End If
      FromSQL = FromSQL & Record

      ' Update progress bar, then move to next record
      If ShowProgress Then
        SysCmd acSysCmdUpdateMeter, rs.PercentPosition
      End If
      rs.MoveNext
      FirstField = False

    Loop ' While Not rs.EOF

    ' Clear progress bar if necessary
    If ShowProgress Then
      SysCmd acSysCmdClearStatus
    End If

  Else
    FromSQL = NoRecords
  End If

FromSQL_exit:
  On Error Resume Next
  rs.Close

  End Function

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s