Recordset Query String Character Limit Workaround

[kd2017]

I'm building a generic function for my application which is something of a replacement for DSum(). I suppose the standard approach for something like this would be to open a DAO.Recordset with a sql string that is built from Expr, Domain and Criteria parameters. However, in my intended use the resulting sql string built by the function could sometimes exceed the 255 character limit of a string by quite a bit (multiple table joins with descriptive but long names). My workaround is to instead create a temporary query def, build it's SQL, then finally open a recordset based on that QueryDef.

My intended use for this function will primarily be used in data validation subs. In an extreme case this function could be called up to a half dozen times when validating a single record, and I do intend for the application to have some "batch" record entry functionality.

I haven't really seen this approach before, so my question: Is this approach of creating several of these temporary QueryDefs in succession a bad idea for general stability of my application? Can anyone foresee corruption issues or anything like that? Is it terribly inefficient?

(I personally have no reason to see anything wrong with this approach, but like I said I havn't seen examples of this before and since I'm a rank amateur I wanted to check with the experts to make sure I'm not asking for trouble.)

Here is my (untested) function at the moment:

Public Function ESum(expr As String, domain As String, criteria As String, Optional criteria2 As String = "", Optional use_first_criteria As Boolean = True) As Currency
'Works just like DSum() except it accepts 2 different criteria strings.
'   if use_first_criteria = true then it will use the first criteria
'   if use_first_criteria = false then it will use the second criteria

On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim crt As String
    
    If use_first_criteria Then crt = criteria Else crt = criteria2
    
    Set db = CurrentDb
    Set qd = db.CreateQueryDef("") 'Unnamed temporary query
    
    qd.SQL = "SELECT SUM(" & expr & ") my_sum FROM " & domain & " WHERE " & crt & ";"
    Set rs = qd.OpenRecordset()
    
    If IsNull(rs!my_sum) Then ESum = 0# Else ESum = rs!my_sum
    
    rs.Close
    qd.Close
    db.Close
    
ExitHandler:
    Set rs = Nothing
    Set qd = Nothing
    Set db = Nothing
    Exit Function
    
ErrHandler:
    MsgBox "Error in 'ESum' Function. #" & Err.Number & ": " & Err.Description
    ESum = 0#
    Resume ExitHandler
End Function

This is a general function. Would I be better off creating a bunch of saved parameter queries for all my scenarios (would be dozens)? I wanted to stay away from that to avoid the possibility of the user changing things, for my validation procedures I like the idea of having my sql hard coded.

[CJ_London]

using querydefs in this way is not uncommon. Whether it is appropriate for your requirement is for you to decide. Providing you close the querydef objects and set to nothing, it should not have a detrimental effect on the stability of your application.

At the moment I don't see the benefit - why have two criteria when you are selecting one based on a Boolean value in the same call? you might as well set that in the calling code

[kd2017]

using querydefs in this way is not uncommon. Whether it is appropriate for your requirement is for you to decide. Providing you close the querydef objects and set to nothing, it should not have a detrimental effect on the stability of your application.

Okay thank you. I'll keep going with this strategy then.

At the moment I don't see the benefit - why have two criteria when you are selecting one based on a Boolean value in the same call? you might as well set that in the calling code

It's a shortcut. I just have the logic there once rather than in each calling code. At the moment I have about a dozen different functions that follow the same basic formula:

Public Function get_some_sum( group_id as long, optional exclude_id as variant = null )
    Dim res As currency
    res = ESum("select clause goes here", _
                    "from clause goes here", _
                    "where clause goes here", _
                    "alternate where clause goes here", _
                    IsNull( exclude_id ) )
    get_some_sum = res
End Function

which to me feels a little cleaner than this in every function repeated:

Public Function get_some_sum( group_id as long, optional exclude_id as variant = null )
    Dim res As currency
    Dim criteria As String

    If IsNull( exclude_id ) Then 
        criteria = "where clause goes here"
    Else 
        criteria = "alternate where clause goes here"
    End if

    res = ESum("select clause goes here", _
                    "from clause goes here", _
                    criteria )
    get_some_sum = res
End Function

[pbaldy]

the resulting sql string built by the function could sometimes exceed the 255 character limit of a string by quite a bit

Are you talking about a string variable? I don't know what the limit is offhand, but it isn't 255. I just checked one I use for an email body and it was 2,400 characters when it sent the email.

[kd2017]

Oh wow! You're absolutely right. I don't know why I had that stuck in my head...? I suppose I just got it from the max length of the short text data type for the db engine.