Link to home
Create AccountLog in
Avatar of E-Squared
E-Squared

asked on

ADP calculated column and form footer sum that update when a row is updated

Requirement: Display the sum of a calculated column in the footer of continuous form and keep the column and the sum updated when rows are updated. The recordsource can be a stored procedure (preferred), selection from a view, or any other method that will work. Back-end server is M SSQL 2000, though it will in a matter of months be upgraded to SQL 2005

Issue: Access ADP manages updates and refreshes behind the scenes. Because it queries the "unique table" directly to refresh updated values and skips the stored procedure entirely, it does not update the calculated column and thus does not update the sum expression. A requery of the entire recordset is not acceptable because then row selection and form scroll position are lost and it generates an additional hit on the database.

Setup:
See the attached code to create a SQL Server database, two tables, and a stored procedure. See the attached file for a working ADP connected with integrated security to "(local)" which demonstrates the problem (rename .txt to .zip and extract). Or use the instructions in the attached code to reproduce the ADP.

To reproduce the problem:
Open ExampleForm. See that the sum of the queried rows is displayed in the footer. If you change Number1 or Number2, the CalcTotal field is not updated and the sum is not updated.

Don't worry about the given form and subform's ability to add new records or other functionality, as I have that covered.

Please provide a solution that doesn't store the calculation in the database (or give good reasoning why this denormalization would be acceptable and how data integrity would be maintained). A calculated field is okay, but performance also matters: a calculated field that uses a function will be very slow, whereas a query that does the same calculation inline will be faster. Keep in mind potential reporting scenarios with where clauses using the calculated value.

Ideas:
- Create a WITH VIEW_METADATA view and make the SP select from this: in tests this failed because the recordset was not updatable, even with SCHEMABINDING and a clustered primary key index on the view. Maybe I messed this up.
- Calculated column in table. I am trying to avoid a function which would be required in order to pick up the multiplier value from the main table.
- Database redesign: suggest a design that reproduces the conceptual layout that doesn't suffer from the same problems.

I am open to better ways to link parent and child subforms, but a SQL profiler session to see what Access really does behind the scenes is necessary to help gauge the performance and elegance of the result.
CREATE DATABASE Example
GO
USE Example
GO
CREATE TABLE ExampleMain (
	ExampleMainID int NOT NULL identity(1,1) CONSTRAINT PK_ExampleMain PRIMARY KEY CLUSTERED,
	Multiplier decimal(20,5) NOT NULL CONSTRAINT DF_Example_Main DEFAULT (0)
)
 
CREATE TABLE ExampleDetail (
	ExampleDetailID int identity(1,1) CONSTRAINT PK_ExampleDetail PRIMARY KEY NONCLUSTERED,
	ExampleMainID int NOT NULL CONSTRAINT FK_ExampleExampleDetail_ExampleMainID FOREIGN KEY REFERENCES ExampleMain(ExampleMainID),
	CalcID int NOT NULL CONSTRAINT CK_ExampleDetail_CalcID_Valid CHECK (CalcID BETWEEN 1 AND 5),
	Number1 decimal(20,5) NOT NULL CONSTRAINT DF_Example_Number1 DEFAULT (0),
	Number2 decimal(20,5) NOT NULL CONSTRAINT DF_Example_Number2 DEFAULT (0)
)
CREATE UNIQUE CLUSTERED INDEX IX_ExampleDetail ON ExampleDetail (ExampleMainID, CalcID)
 
INSERT ExampleMain VALUES (2)
INSERT ExampleMain VALUES (3)
INSERT ExampleMain VALUES (4)
 
INSERT ExampleDetail
SELECT
	M.ExampleMainID,
	C.CalcID,
	Power(2, CalcID),
	Power(3, CalcID)
FROM
	ExampleMain M
	CROSS JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) C (CalcID)
GO
CREATE PROCEDURE ExampleP @ExampleMainID int, @ExampleDetailID int = NULL
AS
SELECT
	M.ExampleMainID,
	D.CalcID,
	D.ExampleDetailID,
	M.Multiplier,
	D.Number1,
	D.Number2,
	CalcTotal = M.Multiplier * D.Number1 * D.Number2
FROM
	ExampleMain M
	INNER JOIN ExampleDetail D ON M.ExampleMainID = D.ExampleMainID
WHERE
	M.ExampleMainID = @ExampleMainID
	OR D.ExampleDetailID = @ExampleDetailID
GO
/*
Now use the provided ADP, and set its connection properties to the database you just created.
It is set up to use (local) and integrated security.
 
If you want or need to make the ADP yourself:
 
1. Open MS Access 2002 and select New Project (Existing Data). Provide connection information to the database you just created.
2. Create a form and set the following form properties:
Record Source: EXEC ExampleP 0
Unique Table: ExampleDetail
Resync Command: EXEC ExampleP NULL, ?
Default View: Continuous Forms
Navigation Buttons: No
Scroll Bars: Vertical Only
3. Drop all available fields from the field list onto the form, creating text boxes.
4. Lock and slightly darken the background of the ExampleDetailID, Multiplier, and CalcTotal text boxes to indicate they cannot be edited.
5. Add the prefix "txt" to all text boxes.
6. Right-click on the detail bar and choose "Form Header/Footer" to add these. Add a locked and slightly darkened textbox in the form footer called txtSumCalcTotal with Control Source: =Sum([CalcTotal])
7. Arrange the controls suitable for a continuous form (I put the labels in the header and the textboxes in a single row).
8. Save the form as ExampleSubform
9. Create a new form and set its properties:
Record Source: SELECT * FROM ExampleMain
10. Drop all fields onto the form from the Field List.
11. Make the ExampleMainID textbox locked and slightly darken the background.
12. Drop a subform control and set properties:
Source Object : ExampleSubform
Name: ExampleSubform
Record Selectors: No
Do not link the subform and the main form with the wizard (though built-in Access linking can be part of your solution if necessary).
13. Set form properties:
On Current: [Event Procedure]
and click the "..." button to access the VB code for this event and set it as follows:
Private Sub Form_Current()
   Me.ExampleSubform.Form.RecordSource = "EXEC ExampleP " & CStr(Nz(Me.ExampleMainID, 0))
End Sub
14. Set form properties:
After Update: [Event Procedure]
Set the code behind this event to:
Private Sub Form_AfterUpdate()
   Me.ExampleSubform.Form.Requery
End Sub
15. Save the form as ExampleForm.
16. Proceed as in the given experts-exchange question.
*/
GO
-- When you're done, close the ADP and then clean up (make sure to close all open connections to the database):
USE Master
GO
DROP DATABASE Example

Open in new window

Example.txt
Avatar of dportas
dportas

You realise that Microsoft consider ADP a legacy technology and recommend not to use it for future developments? Sorry if that sounds unhelpful but I'm just pointing out that there are better front-end options. ADP basically sucks for a whole lot of reasons.
Avatar of E-Squared

ASKER

Yes.

Believe me, I am well equipped to enumerate the reasons why ADP "basically sucks.".

However, if you would like to donate your time for free to convert my existing application to any technology you like (that I know) that will require minimal change to the back-end database and its existing stored proceduress, please contact me at your earliest convenience.  :)

The 500 points here are to be awarded to the person who helps me solve *this* particular problem. If I was allowed to offer more points, I would.
ASKER CERTIFIED SOLUTION
Avatar of Vadim Rapp
Vadim Rapp
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Dear vadimrapp1,

I'm glad I had Access 2003 as I was unable to open the file in Access 2002 due to security restrictions.

Your answer works and I will accept it as the solution. The only remaining question for me is, will this client-side calculated value give the same answer as the server when the columns in question are decimal(20,10)?

The final calculation goes something like this:

Convert(decimal(20,10), Convert(decimal(20,10), ParentTable.Value * Child.Factor * Child.Multiplier) * Child.AdjustMult + Child.AdjustAdd)

I was doing something almost identical to your CalculateTotal Sub (mine was named CalculateTotals) but I was trying to do it all with the server-calculated values.

Set R = Me.RecordsetClone
With R
   If Not .BOF Then .MoveFirst
   Do While Not .EOF
      nSum = nSum + !CalcTotal
      .MoveNext
   Loop
End With
Set R = Nothing
txbCalcTotal.Value = nSum
I did not try decimal; I'm sure you see that what I wrote is merely an idea, a skeleton that would certainly need much of further tuning. For example, there's no need to recalculate it on every on_current. But I don't see any reason why it wouldn't work with any datatype.

Generally, I think it's always good idea to have the server feeding only what's necessary, and calculate on client what can be calculated on client. Hence, have Access to calculate the rightmost column. The problem comes with the total in the bottom, since you can't traverse what's unbound; an alternative solution might be to have the total calculated on the server as totally independent query, and call it on every after_update. With right indexes, shouldn't be a problem.

I would be interested to learn why ADP "basically sucks". From all I've seen in microsoft.public.access.adp.sqlserver, 90% of the problems developers have with ADP is caused by their attempts to do some work on behalf of Access, rather than allowing it to do everything by itself in bound forms. Using a stored procedure as datasource is from the same league I think - especially given that Access, as you correctly noticed, goes inside the s.p. anyways, figures the primary table, and in the end works with it directly, as if it was a query.
Thank you for the help! I think I was getting tunnel vision.

A summary explanation of what you did in your post of your ADP solution would have been nice, especially as that would make this answer more useful to other folks who can't take the time to download, run, and examine everything.
vadimrapp1, thank you very much for your time and effort to help me.

I agree with you completely on your philosophy of having the client do as much work as possible. My concern was the accuracy of the calculations. I'd hate for the client to display one sum, and due to rounding errors, be different from the server calculation. I will experiment with the Variant Decimal type to see if it is appropiate, as I know any floating-point type is not trustworthy for exact calculations. Floats *might* have enough precision to handle decimal(20,10) multiplication, but might not.

Your idea about having the server calculate the total as an independent query occurred to me also, and I might just well use that, depending on what my research shows about the accuracy of Access's decimal calculations. In SQL Server, decimal can change data type on every operation and final values are dependent on this. Eventually there will be precision loss as digits "fall off the end", and the question is whether Access is going to lose the precision in the same way as the server. I may be able to reduce the data types to something like decimal(10,5) which would give much more room for expansion of the type as calculations are done and could circumvent any potential discrepancy between SQL Server and Access. I'll have to check with the customer for his requirements. Next time I post a question like this you can be sure I'll use the same data type as my real problem!

There *will* be times when the server has to calculate the values such as when doing queries based on the final calculation or when doing aggregates which return few rows, where it's really not appropriate to send hundreds of rows to the client for addition.

About ADP basically sucking. I find it to be a reasonable development platform, with some serious gotchas along the way. You're right that one big gotcha is trying to do things that Access will already do for you. The main advantage of stored procedures is that then a web-based front end is also possible, where if Access just has all the queries embedded in it, that task becomes much more difficult.

Things developers get tripped up on in ADPs:
Be sure you know what the "Input Parameters", "Resync Command", "Unique Table", and "Record Source Qualifier" form properties do and why they are required in order to have any chance at all of developing a working application. Know what the difference is between the "Filter" and "Server Filter" properties. The error messages that Access gives you when you don't use these properties properly are VERY cryptic and hard to understand.

Examples of ADP gotchas that I've had to solve:
- Controls bound to bit columns regularly crash Access when they are clicked on. After weeks of struggling I findally found an effective workaround that allows me to use bit columns rather than making them all tinyint.
- Some things that you can do in an MDB aren't possible in an ADP because the client and the server have been divorced. For example, "=Sum([a] + [b])" won't work in a form footer in an ADP. There are several more.
- MDB habits often allow for Access getting all the rows of a recordset, and then filtering or moving to the desired record. In an ADP you need to use input parameters so you aren't sending half your database over the network just to work with one row.
- Be aware that Access learns the source columns of every SP, view, or query that you throw at it and manages its selects, updates, and refreshes on its own directly to these tables. It will almost always use sp_prepare and sp_unprepare.
- ADPs manage user concurrency with WHERE clauses that repeat all the old data so that the update will fail if someone else has changed the record. This can defeat some ways of using the database that you might not have expected.
- ADPs uses @@identity instead of scope_identity(). If you have an insert/update trigger on a table that inserts to another table possessing an identity column, Access will blow up when trying to work with that first table. Some clever workarounds are possible to restore the @@identity value in the trigger, but this takes special knowledge.
- Various things that a SQL Server developer might be used to doing with triggers, calculated columns, views (especially INSTEAD OF trigger-driven updatable views), and so on are just going to screw up ADPs completely unless you know exactly what the ADP is doing behind the scenes and how to work with it. To use these features of SQL Server with ADPs will require significantly more knowledge and troubleshooting skills than the "simple use" scenarios you might expect.
- Reports with subreports need special handling because Access will grab the ENTIRE subreport's possible recordset EACH time that subreport is displayed, and then filter for the rows it wants. This is a huge performance killer unless you know special techniques to work around this.
- Access uses different syntax for its filters than standard SQL Server syntax, even in ADPs. Using one filter string as the other requires careful conversion.
- Other gotchas I can't remember now, but I am sure there are more.

Plus there are the gotchas of Access itself that have nothing to do with ADPs:
- No row-specific unbound data controls on a repeating form. If you put an unbound checkbox on a repeating form and then check the box in one row, all the rows will become checked.
- Various other problems any Access developer is familiar with.

All in all, I think Access is a good platform, especially for someone who doesn't want to write his own entire framework and form/reporting/filtering/GUI/everything. But it can't do everything and there is definitely a lot of learning involved.

Last, some comments about the data design that I am working with.  I could not stomach denormalizing by storing the calculation on the server. Any suggestions about a better way to do this are welcome! I hate the hit that doing the math each time takes, but storing the calculation is a serious problem as it is dependent on a value from another table.

I thought about the bank account balance problem: does a bank store the current balance and all the transactions? At first thought this might be considered denormalization because the balance is always implied by the sum of the transactions. But then I realized you can turn this around: instead of starting at zero and adding the transactions to get the current balance, start at the current balance and step backwards through the transactions to get a historical value. Now the apparent denormalization is gone: you have a base value which is current and accurate and not dependent on anything, and your transaction list can be truncated as old data is moved off. So similarly, I toyed briefly with the idea of storing the final calculation and all but one of the multiplied operands, but I don't like that so much either, since the calculation is still dependent on the other values and changing any of the values will change the calculation. It's not like transaction history where (typically) a transaction's amount will never change once it is posted.
Regarding denormalization: if you do  denormalize, and store the result of the calculation on server, and at some point it goes out of sync, then it's a signal to you that something is wrong. That something may be on the side of synchronization (and probably will be in most cases), but also may be on the side of initial data. If it was normalized, you wouldn't catch it. Now you will. This way, redundant data makes the solution somewhat self-healing, or at least self-diagnosing.

I would say that most of the "gotchas" you listed (very interesting indeed) only show that ADP is a tool that does not tolerate occasional use. People who come and try to use it as VB6 start with using unbound forms, soon are facing numerous problems, and don't see the benefits of neither vb6, nor ADP. But someone who is working with it on constant basis, will certainly know the ways to achieve what needs to be achieved, and will be able to create a desktop data-driven solution many times faster than in pretty much anything else.

If you tell little more about the specifics of your data normalization problem, I would be happy to give it a thought. You are right in your comparison with transaction - so the question is, is the total that shows up today has to be stored for later audit because some decision was made upon it? is a scenario possible that you will have to restore your database to what it was 1 second before user X did his thing one week ago, in order to find out what he did and why? if you can answer it on the business side, then you will have the answer in the data structure.
Hey, thank you for the idea to think about the business rules. That is very appropriate, always. One of the business rules is that these calculations can be finalized and the data "locked," requiring special unlocking in order to be editable again. Whether I ever store the final calculation for all rows or not, I now can see clear to at least storing the final calculation for just the locked rows. When an unlock occurs, I can clear the calculation. I will think more about this.

Would you be open to a discussion by email? It would be much easier to work in terms of the actual business objects I'm dealing with, but for some reason I feel hesitant to go into details about them here. Talking about Level1, Level2, and Level3 (or Grandparent, Parent, and Child) just makes it so very hard to understand what I'm trying to model. Or would it be better if I opened a new question with points available so that everyone has an opportunity to join in? I could also check to see what level fo detail is okay.
> Would you be open to a discussion by email?

sure, why not. vr at vadimrapp.com .
> - Be aware that Access learns the source columns of every SP, view, or query that you throw at it and manages its selects, updates, and refreshes on its own directly to these tables. It will almost always use sp_prepare and sp_unprepare.

which however will most likely hit the execution plan cache at the server, so the overhead will be zero. Visual Studio does the same, as well as probably most of database-aware tools. If you run profiler, the sight of lone sql statement not wrapped into some kind of sp_execute will be very, very rare.

As for learning the source columns, if I remember correctly, this happens only when the developer is working opening tables etc. directly, but it does not happen in forms and reports, i.e. when the user is working. Which does make sense.

> ADPs manage user concurrency with WHERE clauses that repeat all the old data so that the update will fail if someone else has changed the record. This can defeat some ways of using the database that you might not have expected.

This is the feature not of Access as such, but of the ADO using client-side cursor. These days, server-side cursors and locking the records are practically extinct, so the database in the aspect of concurrent work is being seen as one big excel table - which is sad. What's interesting is that during the development of ADP, there was an initial attempt to provide support for server-side cursors and proper locking mechanism, but it was abandoned in the middle. The remains of that attempt still exist in the form of methods BeginTrans & Co. of the connection object, if I remember correctly. The bottom line is, I would be very surprised if somebody was expecting anything other than client-side cursor and exactly this resolution of concurrency.

> Various things that a SQL Server developer might be used to doing with triggers, calculated columns, views (especially INSTEAD OF trigger-driven updatable views), and so on are just going to screw up ADPs completely unless you know exactly what the ADP is doing behind the scenes and how to work with it.

:-) I think the expectation that somebody who is already using triggers, calculated columns, and INSTEAD OF triggers, will behave professionally in Access as well, is not too much.

> - Reports with subreports need special handling because Access will grab the ENTIRE subreport's possible recordset EACH time that subreport is displayed, and then filter for the rows it wants. T

Just tried. Nope. Report's datasource select * from employees where firstname='Karin'      , subreport's: select <several fields> from RG ; subreport linked to report by column EmployeeId. From Profiler:

SQL:BatchCompleted      59      select * from employees where firstname='Karin'      
SQL:BatchCompleted      58      SET FMTONLY ON select "EmployeeID" from  (SELECT "RG"."RGNumber", "RG"."Date Opened", "RG"."EmployeeID" FROM "RG") AS DRVD_TBL SET FMTONLY OFF      
RPC:Completed      58      declare @P1 int
set @P1=47
exec sp_prepare @P1 output, N'@P1 int', N'SELECT "RGNumber", "Date Opened", "EmployeeID" FROM (SELECT "RG"."RGNumber", "RG"."Date Opened", "RG"."EmployeeID" FROM "RG") AS DRVD_TBL WHERE ((@P1 = "EmployeeID"))', 1
select @P1      
RPC:Completed      59      exec sp_executesql N'SELECT "RGNumber", "Date Opened", "EmployeeID" FROM (SELECT "RG"."RGNumber", "RG"."Date Opened", "RG"."EmployeeID" FROM "RG") AS DRVD_TBL WHERE ((@P1 = "EmployeeID"))', N'@P1 int', 19      

As you can see, it first opened the main report, figured employeeid=19, and then opened subreport with WHERE employeeid=19 (sql optimizer will penetrate the derived table with WHERE). Btw, note that the datasource for the main report even was executed directly, without any encapsulation.

Let me try some of those things in Access 2002 (is that what you're using?) and get back to you. I can't believe that four developers would be unable to figure out the subreport thing. But I will take a look at this later because it is interesting. Switching from Access 2002 was not a possibility for a long time as it was what the customer was using and he wasn't about to pay for an upgrade.

I don't use a lot of triggers, but sometimes they are useful. I know this is almost a philosophical discussion, but always arguing against triggers is in my book as indefensible as unwarranted use of them. (How are you going to maintain update history, even in the face of careless folks with direct database access, without triggers?) The reason I found out some of this stuff was 1) I was trying to get around Access's insistence on doing everything by itself and 2) some of my applications are unusual, like a database which has to model nearly every person and network-connected object (and much more) in the real world for a 2200-person organization. It doesn't have a LOT of information and speed is meaningless: what it needs is flexibility, so it is an E-A-V database. You can imagine the conniptions I've had to go through to get Access to handle updates to pivot query views, eh? It *shouldn't* be that hard.

About concurrency: I think many developers never think about concurrency. That Access manages it at all is good! It protects ignorant developers from themselves. It protects knowledgeable developers from ignorant managers who don't see the value in a bunch of seemingly valueless work for "just-in-case scenarios" and want to order their employees NOT to add that functionality. But at the same time, there are legitimate things that are defeated by the ADO library's all-column where clause.

I don't think the overhead of sp_prepare and sp_unprepare is 0. It is still going to consume more server resources to parse the prepare statements AND the statements they are preparing, rather than just the desired statements themselves. I'm all for sp_executesql as an aid to query caching, but I think the sp_prepare stuff can get ridiculous. Preparing statements only helps if you're going to execute them more than once, and then if you unprepare right away then prepare again later for the same statement, then you're not really being the most efficient.
> Access 2002 (is that what you're using?)

No, 2003.

> I'm all for sp_executesql as an aid to query caching

um... I'd say, plan caching does not have to do anything with executesql. If executesql had any impact, it probably would be negative, because of some dynamic. But starting with sql server 7, it caches the plan regardless of where the plan came from, so only the final result matters. Also for that reason, using stored procedures does not bring any performance gains, contrary to some recommendations that still can be heard.

> but I think the sp_prepare stuff can get ridiculous.

I suspect that what we see in profiler is only representation of what's going on internally. It's not impossible that under the hood, the code that runs when we see sqlprepare and when not is exactly the same. Equally,

> if you unprepare right away then prepare again later for the same statement, ...

... then, I bet, sql server does not do anything at all.

At least, in profiler all consumed resources against these lines are zero. You can actually take a look if in profiler you enable cache-related events. Cache hit, bingo. Though, I'd say that it compiles even quite nontrivial queries with such amazing speed that unless you are running 1,000 transactions per second, you wouldn't really feel the difference whether it recompiled or not.

> About concurrency: I think many developers never think about concurrency.

The best concurrency is mine :-). If Karin opens an order currently being worked with by Adam, she receives a message about that. Implemented by Karin's client broadcasting an UDP message "i'm opening order X", and Adam's responding "I 'm working with it". And in rare case when somebody is really blocking somebody else, they both receive helpful net send with mutual phone numbers (taken from active directory). Meanwhile, I'm having fun on EE.