Access VBA with custom function in SQL

Question

I need to open a query or recordset or something (datasheet view) with some sql i build in my vba based on form control values. I have a "many to many" relationship in my data (kind of). Pretend we have a Person table, a Pet table and an ID Pet_Person table. I want to have a list of my People with a concatenated string of all their pets in one row of returned data. So....

Row    Person       Pets
1      Kim          Lucius, Frodo, Cricket, Nemo
2      Bob          Taco

And I googled and I found you can write functions in the VBA to be called from the SQL. So. Here's the problem. I have a lot of records and once opened, I cannot move around the datasheet view/query/whatever without that concatenation function being called everytime I click on a row. I only need it called once when the sql is initially ran... like a snapshot or something.

I'm not too well versed with Access and the possibilities. I've tried some things I found online that all had the same result... that concatenation function being called when I touched that resulting dataset at all.

Last thing I tried looks something like:

With db
        Set qdf = .CreateQueryDef("Search Results", q)
        DoCmd.OpenQuery "Search Results", , acReadOnly
        .QueryDefs.Delete "Search Results"
End With

StackOverflow really never formats my stuff correctly. Probably user error.... oh, well.

Edit:

Oh Bart S. Thank you but you went away too soon for me to understand the answer if it is there. Thank you.

Oh Remou. Yes, I saw your post. I used your post. I've used many of your posts while working on this project. Why access doesn't support all SQL functions I am so used to with MySQL I have no idea. You're a great addition to this site. I should have linked to it with my question but the coffee hadn't kicked in yet.

I have my concatenation function and I am calling it within the sql. I was opening it with the docmd to open that recorset or query or whatever. But here is my issue (and I may be creating this myself by trying too many solutions at once or I might be overlooking something)... it keeps calling that function each time I touch the resulting dataset/query/thing and there's too much data for that to be happening; I am seeing the hourglass simply too much. I am positive this is because of the way I am opening it. This is intended to be the result of a search form screen thing. I'm thinking I need to just literally make another form in access and populate it with my resulting recordset. I think that is what you guys are telling me. I'm not sure. This is a weird example. But... you know with Excel, when you write an inline function of some kind to get some value for each row... and then you do a copy and paste special for just values (so not the function)... I need that. Because this function (not in Excel, obviously) must query and that takes to long to reapply each time a row is clicked on (I think it's actually requerying each row if a single row is clicked on, almost like it's rerunning the sql or something). Like the NIN/Depeche Mode song Dead Souls... It keeps calling me/it.

Answer 1

I always do it like this:

Dim strSql As String

strSql = "SELECT * FROM table WHERE field=something;"
Set rs = CurrentDb.OpenRecordSet(strSql)

Then use RS to perform actions. There may be better ways. You can, for example, create a query directly in Access and call it from VBA.

While looping the recordset, you can concatenate the string:

Dim strResult As String
While (Not rs.EOF)
  strResult = strResult & rs!yourfield
WEnd

Answer 2

Here are a few thoughts and strategies for coping with the issue of constant data re-loading:

1. Make sure your query is set to snapshot. Same for the form.
   This of course makes the data read-only, but it may help a bit.

2. Cache the result of your query into a local table, then show/bind that table instead of the query itself.
   This will make the user wait a bit longer initially while the query is executed and saved into the local table, but it makes the interface much smoother afterwards since all data is local and doesn't need to be re-calculated.

   • Create a local table localPetResult (on the client side) that has all the fields matching those of the query.

   • Instead of binding the query itself to the datasheet form, bind the localPetResult to it, then in the form's VBA module handle the OnLoad event:

     Private Sub Form_Load()
         ' Remove all previous data from the local cache table '
         CurrentDb().Execute "DELETE FROM localPetResult"
     
         ' Define the original query '
         Dim q as String
         q = q & "SELECT Row, "
         q = q & "       Person, "
         q = q & "       Coalesce(""SELECT PetName FROM Pets WHERE Person='"" & [Person] & ""',"","") AS PetNames "
         q = q & "FROM MyData"
     
         ' Wrap the query to insert its results into the local table '
         q = "INSERT INTO localPetResult " & q
     
         ' Execute the query to cache the data '
         CurrentDb().Execute q
     End Sub
     

     One you have it working, you can improve on this in many ways to make it nicer (freeze the screen and display the hourglass, dynamically bind the ersult table to the form after the data has been calculated, etc)

3. Cache the result of each call to the coalescing function.
   I've used that to calculate the concatenation once for each record, then store the result in a Dictionary whose key is the ID of the record. Subsequent calculations for the same ID are just pulled from the Dictionary instead of re-calculated.

   For instance, add the following to a VBA module. I'll assume that you use Remou's Coalesce function as well.

       Option Compare Database
       Option Explicit
   
       ' A Scripting.Dictionary object we'll use for caching '
       Private dicCache As Object
   
       ' Call to initialise/reset the cache before/after using it '
       Public Sub ResetCoalesceCache()
           If Not (dicCache Is Nothing) Then
               dicCache.RemoveAll
           End If
       End Sub
   
       ' Does the Same as Coalesce() from Remou, but attempts to '
       ' cache the result for faster retrieval later '
       Public Function Coalesce2(key As Variant, _
                                 sql As String, _
                                 sep As String, _
                                 ParamArray NameList() As Variant) As Variant
   
           ' Create the cache if it hasn't been initialised before '
           If dicCache Is Nothing Then
               Set dicCache = CreateObject("Scripting.Dictionary")
           End If
   
           If IsNull(key) Then
                ' The key is invalid, just run the normal coalesce '                                       
                Coalesce2 = Coalesce(sql, sep, NameList)
           ElseIf dicCache.Exists(key) Then
               ' Hurray, the key exists in the cache! '
               Coalesce2 = dicCache(key)
           Else
               ' We need to calculate and then cache the data '
               Coalesce2 = Coalesce(sql, sep, NameList)
               dicCache.Add(key, Coalesce2)
           End If
   
       End Function
   

   Then, to use it in your query:

       ' first clear the cache to make sure it doesn't contain old '
       ' data that could be returned by mistake '
       ResetCoalesceCache
   
       ' Define the original query '
       Dim q as String
       q = q & "SELECT Row, "
       q = q & "       Person, "
       q = q & "       Coalesce2([Row], ""SELECT PetName FROM Pets WHERE Person='"" & [Person] & ""',"","") AS PetNames "
       q = q & "FROM MyData"
   
       ' Bind to your form or whatever '
       ...