Sum in Footer on Form with ADO Recordset (1 Viewer)

[Galaxiom]

Why would Sum(fieldname) not work in a form based on an ADO Recordset drawing from a Stored Procedure?

I had a form with a DAO query from linked tables as its RecordSource. Textboxes in the footer showed the Sum for various fields.

When I changed the form to use an ADODB Recordset based on a Stored Procedure in MS SQL Server 2005 the Sum textboxes return Error. There were no other significant changes to the form.

There are certainly other ways to get the Sum but I wonder why it doesn't work the same as the Recordset coming from the DAO RecordSource.

 

[Pat Hartman]

It could be a disconnect between DAO and ADO. The recordsets behind Access forms are naturally DAO so I'm not sure how replacing a DAO query with ADO actually works.

Just out of curiosity, why did you change the form? In my years of working with Access, I have found that using the Access "style" created simpler and more stable applications because I didn't have to write code to fight the built in methods.

 

[Galaxiom]

It could be a disconnect between DAO and ADO. The recordsets behind Access forms are naturally DAO so I'm not sure how replacing a DAO query with ADO actually works.

The Recordset property of the form is simply Set to the ADO Recordset. I have used ADO stand alone recordsets before and Count worked fine though I hadn't used Sum on them. I assumed the Sum function would work too but it seems to be expecting DAO for some reason, at least when the recordset is connected.

I will try some experiements today and report back. Maybe it is related to the recordset type or cursor type.

Just out of curiosity, why did you change the form? In my years of working with Access, I have found that using the Access "style" created simpler and more stable applications because I didn't have to write code to fight the built in methods.

The MS SQL Server table has over 26 million records and growing by several thousand per day. A query to the ODBC linked table sometimes took a second or two. The recordset returned from the ADO command (connected via OLEDB) running a parameterised stored procedure is quite literally instant.

The performance using LinkMasterFields and LinkChildFields was terrible (several seconds) when I first moved the backend to MS SQL Server. It had been reasonable (couple of seconds) using Link Fields when the table was in an Access Back End and held less then ten million records.

After the move, abandonning the Link Fields and running a dynamic query to the linked table OnCurrent brought it back down to a second or two again depending partly on the number of records but otherwise the time seemed somewhat random.

Another alternative would have been a dynamic PassThrough query. However this still has the hesitation while the server generates an execution plan each time. The Parameterised Stored Procedure does not have this overhead an consistently loads the form in less than a blink of an eye.

As a workaround I will have the server calculate the Sums in the Stored Procedure and return them as output parameters. Another alternative would be to run a loop through the recordset but I would expect it is better to give the job to the server if the performance of the query is any indication.

 

[davidcie]

(Apologies for raising an old thread from the grave but seems like I'm having the exact same problem; considered it wise to continue with prior wisdom and report.)

The Recordset property of the form is simply Set to the ADO Recordset. I have used ADO stand alone recordsets before and Count worked fine though I hadn't used Sum on them. I assumed the Sum function would work too but it seems to be expecting DAO for some reason, at least when the recordset is connected.

@Glaxiom I realise it's been a while but do you perhaps recall if you've ever managed to find a solution to this problem? In my case whenever I use any formula referencing ADODB Recordset fields as text box control source I get an instant Access crash when opening the form. It seems that code is the simplest possible, and continuous form runs just fine without a "=Sum([SomeField])" or "=Count([SomeField])" in its footer:

Public m_ado As ADODB.Recordset

Private Sub Form_Load()
    Dim conn As ADODB.Connection
    Set conn = CurrentProject.Connection
    Set m_ado = New ADODB.Recordset
    With m_ado
        .ActiveConnection = conn
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Open "SELECT * FROM MyData"
    End With
    
    Set Me.Recordset = m_ado
End Sub

Will be happy to share a test case DB if anyone's interested.

 

[Galaxiom]

I used the aggregate in the query. I expect the problem with using the aggregate on the control source is that it expects to talk DAO to the engine.

 

[sonic8]

In my case whenever I use any formula referencing ADODB Recordset fields as text box control source I get an instant Access crash when opening the form. It seems that code is the simplest possible, and continuous form runs just fine without a "=Sum([SomeField])" or "=Count([SomeField])" in its footer:

From your code I gather your are still using an ADP-Project. There it should definitely work to use those expressions in the form footer.


If you can provide a sample to reproduce the issue, please do so. - However, I can't promise anything!

 

[davidcie]

I used the aggregate in the query. I expect the problem with using the aggregate on the control source is that it expects to talk DAO to the engine.

Thanks for letting us know! Great to have some closure regarding your experience, unfortunate as it may be

From your code I gather your are still using an ADP-Project. There it should definitely work to use those expressions in the form footer. If you can provide a sample to reproduce the issue, please do so. - However, I can't promise anything!

No, I'm on the latest and greatest Access 2016, .accdb. More than happy to share a test case, please find it attached to this post.

What bugs me about this issue is that we don't seem to be doing anything illegal per se, i.e. Access does not raise a syntax or runtime error but crashes instead. Think it may be worthwile raising it with Microsoft? Or is Access is mostly abandonware at this point and chance of them fixing it are too slim for this to be woth the time going through multiple tech "support" levels?

 

[sonic8]

What bugs me about this issue is that we don't seem to be doing anything illegal per se, i.e. Access does not raise a syntax or runtime error but crashes instead.

In Access 2010 it does not crash but simply displays #Error in the SUM-Control. However, in Access 2016 I am able to fully reproduce the crash you are experiencing.

Well, one might argue that there is not much to gain from using a bound ADO recordset in a plain Access setting. However, with a SQL-Server backend this issue would be a serious problem for me. - I haven't had the time to test this scenario, yet.

Access is not "abandonware"! Microsoft has once again established an active developer team working on Access and developing new features. The team is quite small, so their capabilities are limited. Still, they pay attention to the Access-Community more than ever before.

So, I would recommend to raise this issue with Microsoft. Sure, it will take some effort to escalate this through support levels and there is no guarantee for a fix but it is not hopeless either.

If it should turn out that the very same issue exists with SQL-Server backends, I will certainly take the matter to Microsoft support.

 

[davidcie]

Well, one might argue that there is not much to gain from using a bound ADO recordset in a plain Access setting. However, with a SQL-Server backend this issue would be a serious problem for me. - I haven't had the time to test this scenario, yet.

This is exactly where I'm coming from - we use a lot of detached ADO recordsets that are results of queries run on a centralized SQL Server in a multi-user environment. Thought the crashes might stem from something really strange we're doing on the database front and hence the test case to double check the results taking all other variables out the equation.

Thanks for your confirmation and words of encouragement regarding reporting this upstream. Will try to go down this path to see where it leads us, but in the meantime stick to the solution provided by @Galaxiom.

EDIT: submitted a bug report to Microsoft and to my great surprise they reached out to me a month later asking for a test case, which I duly submitted immediately. Have not heard from since but perhaps we may see a fix for this in Access 2024? Fingers crossed.