Question about Recordsets and Transactions

[GK in the UK]

I've coded a big update routine which involves 6 tables all wrapped up in a DAO transaction workspace.

Typically in normal use, it could run for some minutes where a batch of transactions updates various control accounts, supplier and nominal account balances, history tables, that sort of thing.

There are two source tables, tblTransHeaders and tblTransLines. Currently it gets the right results by traversing tblTransHeaders and dealing with the linked TransLines in turn for each header. It works as long as all the transactions are posted to a single control account. First I create a recordset of all the TransHeaders, then as each one is processed, it creates a recordset of the TransLines linked to it and does all the updates.

But I've realised there is a flaw if the supplier or customer account has a creditors/debtors control override and I need to re-engineer it to deal with all the TransLines followed by all the associated TransHeaders, which will then be grouped by control account.

So my plan is, when the user clicks the button to proceed, to create two recordset from a sql select, a subset of the two transaction tables.

My question relates to the two recordsets which are 'in sync' at the moment of starting. But what happens to the records in the tables that are being updated? What happens if one of the records that is in the recordset is edited after the routine starts? Would Access allow it? What would happen if a transaction line record was deleted, or an additional transaction line was added (which would be linked to one of the TransHeader records in the Recordset)?

Just not sure what is going on with the Access Transaction workspace. My routine can seemingly churn away for quite a while then at the end I can cancel it and it's as if absolutely nothing happened. There are plenty of articles about what it does but I'd like to understand a bit about how it does it.

 

[The_Doc_Man]

Nobody is 100% sure of what is happening in the transaction workspace, because MS does not release Access internals info. There are four basic operations within SQL workspaces that apply to what you are doing.

INSERT INTO (APPEND) adds records but doesn't change existing records. So you are unlikely to run into collisions in a properly normalized database. (Not impossible, just unlikely.)

SELECT doesn't change anything and what you see will be a "snapshot" copy of what was there at the time of the operation. Due to internal automatic checks, that copy might reflect changes made later if you left the SELECT open, say, as a tableview on your screen, for longer than the internal update rate.

DELETE would remove records - but the way to protect against that is to never delete a record until/unless you are sure. Instead, you simply include a flag that says "this record is no longer in use." (Obviously, pick a shorter name like "Obsolete.")

UPDATE would alter extant records, so you can protect against that by minimizing the update operations. The thing about updating, though, is that your described problem is an inventory-like system or a purchasing system. (You mentioned suppliers, e.g.) If everything is transactions, you don't update a transaction once it is written. You make a new one, and INSERT INTO doesn't step on toes in that case. Then your balances are NEVER written to tables. They are formed by aggregation queries that sum extant records.

In cases where an update IS required, your fears about simultaneous access by multiple users updating the same record are addressed by considering locking modes. If you want to control whether someone else can get to the records then you might wish to create your queries with pessimistic locking (which you can look up for details).

You can also try a "reservation" system that puts tags in a "reservation" field in a record before trying to do anything to alter it. That is terribly clunky and has its own pitfalls, such as what happens if a user system crashes without releasing reserved records, but it would prevent destructive interference from another user. You update the record then as a separate action, check whether your tag is now in the record's reservation field. If you own ALL of the related records, update them and release the tags. This is a very complex can of worms, though, and I would not recommend it. I mention it at all because I am trying to give you a more complete answer.

 

[GK in the UK]

OK. Quick question: When you talk SQL workspaces, does that include the recordset operations? I think it does. My recordset is initialised by a SQL select, but the changes to the fields are done by vba commands on the recordset (.edit and .update)

If everything is transactions, you don't update a transaction once it is written. You make a new one, and INSERT INTO doesn't step on toes in that case.

Well, I'm certainly talking about transactions here. If I need to adopt what you're saying, that's a seismic engineering change.
Let's say I have an existing invoice record which comprises a header and n linked lines. You're saying if I re-open it for editing, I must create an entirely new one and insert it?

Currently I just edit the existing lines in the table, and update the header with the new totals. The invoice is editable until my batch posting routine runs, then it gets flagged as 'posted' and updates all the account tables.

In cases where an update IS required, your fears about simultaneous access by multiple users updating the same record are addressed by considering locking modes.

I hope that's my get-out clause.

Then your balances are NEVER written to tables. They are formed by aggregation queries that sum extant records.

Well that I think would be difficult. And as I've discovered by having a look in the tables of my ancient system, balances are (apparently) written to the table. If I open a supplier record, I want to see the balance NOW, not wait whilst n records get queried.

Anyway, if I understand you correctly, the proper robust way to do this would be:

Create new invoice with new lines and save to the table
Re-open saved invoice, create duplicate of the header and all the lines. Where? How? a temp table?

Re-save the duplicated invoice and lines by doing an INSERT INTO / APPEND followed by a DELETE of the original version (presumably within a transaction workspace)

Is that about the measure of it ?

For the bulk update routine that I described, I did wonder if I should save a temp table and only run it when everyone else is locked out, and make the form modal so nothing else can happen.

"when everyone else is locked out". Ha ha, as if. This is a personal project and the chance of anyone else every using it is about zero. It's already (almost) in a state that I can retire my old system. But I like to do things the 'right' way, thank you for your help.

 

[The_Doc_Man]

When you talk SQL workspaces, does that include the recordset operations? I think it does. My recordset is initialised by a SQL select, but the changes to the fields are done by vba commands on the recordset (.edit and .update)

Actually, this is a fine point. The recordset was opened via SQL and all you are doing is using the recordset structure as a magnifying glass to see what is in a given record. The .Edit followed by diddling with the fields of the record doesn't actually do anything until the .Update occurs, at which time an SQL operation occurs for the record on which you did the .Edit. Until you commit the update, you do nothing of any permanent nature.

Let's say I have an existing invoice record which comprises a header and n linked lines. You're saying if I re-open it for editing, I must create an entirely new one and insert it?

If the header has its own internal prime key (PK) then the n linked lines should each associate with the header via a PK/foreign key relationship. What would you change in this header? Adding another line but linking it to the same header by copying the header PK to a foreigh key field is no biggie. Surely you would not change an extant detail line that reflects something you did? Doing so would lose history and your original question clearly expressed interest in transaction history.

The mindset for a transaction system is that you keep details in child records. You can add child records easily enough via INSERT INTO and that won't remove or change any other extant records. But with Access, there are more things than just tables. Queries are the power of Access - and your friends.

Queries to take summations and counts of the child records, perhaps filtered by that PK we mentioned, will tell you the current states of everything current for the header. If the detail lines include dates, you can filter by age (and sort by age). If the child lines include statuses, you can also filter by status. If they contain information about specific products, you can filter by product. Queries can do ALL SORTS of filtering. And sorting ... and computations. Queries are the true workhorses of a good DB.

The headers can represent all sorts of things, I suppose - but are they individual Purchase Orders? Suppliers? Sales Invoices? The only things you keep in those "header" records shouldn't change as the result of addition of a new detail record or a change in status of a child order, at least in a truly normalized DB. Instead, you use those aggregate queries to count statuses. There is almost NOTHING relating to child records that you should keep in a header that has child records if you are to maintain sanity and truly normalize the DB.

If I open a supplier record, I want to see the balance NOW, not wait whilst n records get queried.

In a well-structured DB where the balance is based on child transactions, each with a particular status and amount, the queries to do this will take at most a few seconds. How long is too long? Five seconds? I doubt it would take that long. But then again, if you are talking 1 million records, it might take a moment. A million of anything would take a moment.

NOTE that I said "well structured." When you keep stuff in the header that is the sum or other aggregate function of the individual child records, you are letting the tail wag the dog. You also invite disaster if you start to update the header and your system hangs.

If you have multiple users, each of whom could do something to perform this update, you run in to destructive interference... iin computer terms this is literally "the left hand does not know what the right hand is doing." And those numbers in the header QUICKLY become totally meaningless. Even for a single-user system, you run into crash sensitivity if you update your header separately from the child tables. That is because you have to commit everything at least twice - once for the header and once for each involved child record.

This forum is absolutely LOADED with discussions of inventory and running-balance financial systems. Search for "inventory control" and "sales" topics. You are thinking in flat-file logic but Access is capable of so much more. From your discussion, I am thinking that your old system was NOT built on a relational database platform because your ideas about how your new system should look are absolutely archaic.

Please understand, no insult was intended. But if you have an archaic model you invite archaic results. What you are doing violates normalization principles by having data in a (header) record that does NOT depend solely on the record's PK. May I strongly but respectfully suggest that you need to read up on normalization and try to understand the "purity of purpose" concept that I just described. The stuff in a header in a well structured table will almost never depend on its children and CERTAINLY will not depend on the statistics related to its children.

I sense you are having troubles with this because of a mind-set issue. Please search the Access forum for normalization articles. The SEARCH function is in the thin blue ribbon near the top of the page that looks like a menu bar. SEARCH is 3rd from the right. Also you can search the general web - but if you do so, look for "database normalization" because in isolation, the word "normalization" has mathematical, diplomatic, psychological, and chemical interpretations as well as database meanings.

 

[GK in the UK]

Thank you again it's all helpful.

One of my main startup forms is a tabbed form with various lists.
I changed the queries to fetch the detail lines and sum them on demand instead of taking the saved balance in the invoice header, or the customer record, etc. etc.
From opening the form to it being ready for input has gone from 1 second to a full 35 seconds, every time. And that's without calculating the VAT element of each detail line so the time may well double.
So I need to do some optimisation there.

I haven't yet found a way to do what I want without breaking normalisation.

The SQL query is having to go 'external' to a public function to calculate the line value and it's getting called several hundred thousand times.
For lists that are 'net of VAT' I think I can solve the speed issue by saving the Net Total in the line - but that is another violation of normalisation.

When I need to query invoices 'gross' for a ledger datasheet it's more tricky because the vat total is rounded differently to the line values.

If there's a way to do this in SQL, without the performance hit then I can dispense with values in the header.

I need a statement that does this:

For each customer
For each header
get all detail lines
calculate net value
calculate vat value to 0.1p
add up all net
add up all VAT to 0.1p
round vat total to 1p
add up net and vat to get total
add up all totals to get customer balance
display customer balance on form, if <> 0 (or put the record in the dataset or whatever)

I do this (except the last 2 lines) in the header every time the invoice is saved or lines are updated. I requery all the lines, do the sums and update the header.

Of the handful of accounting databases I've used and dissected, they all save line totals in the header. I found this:

"For performance reasons, it is common to roll up the summation of nett, tax and gross of all child line items on the parent Invoice level as well. Although this is potentially redundant, it does have performance benefits over continually re-deriving this data"

I think it's the norm to do this.

 

[The_Doc_Man]

Here is a thought for you, then. "Breaking normalization" can be an issue to consider as a good thing even for smaller data sets if you have something for which you have to remember what you did AT THE TIME because the rules can change. So in that case you store the value and the rule that led to that value - because you have history issues to consider.

However, it is NOT the "norm" to denormalize anything of an accounting roll-up nature. I will have to back away from this, though, and invite my UK-resident colleagues who are more well-versed in VAT issues to perhaps tell you how they manage the kind of problem you describe. In the USA we don't do VAT so are not accustomed to its issues.

 

[GK in the UK]

Well this has been going round in my head for days (and nights). I've found the solution (subject to looking at code but I think it will work). The big issue is: summing up and saving child values in the parent record.

First let's just have another look at another normalisation issue: Saving a calculated field.
I'm suffering a massive performance hit when I calculate the line value in a SQL query, because, I think, the SQL has to go to VBA twice to get the value, for every transaction line. Once to do the actual calculation, and again to see if the value is 0 (because frequently I'm not interested in zero balance lines). Can I do it with just one evaluation ? I don't know. Can I do the calculation within the SQL? I don't think so. The line value calculation is qty x conversion factor x unit price less discount percent, and it has to be rounded correctly. Currently I have just one source for the result and it's my public function and I'd like to keep it that way.

In another thread, Dave mentioned that users may want to export data from the table. What is the chance of them getting precisely the same net value result for every line when they don't know what the algorithm is? Pretty slim. So I'm thinking I really should save the net value which should have a big impact on the SQL query performance because I won't have to 'leave' the SQL to go to my VBA function.

So two good reasons for saving the calculated net value in the line.

The parent/child (headers/transactions) issue. I simply could not find a way to get rid of the header totals, because I decided to go with the option to calculate VAT to 0.1p per line, then round to 1p at the end. It turns out that was a bad decision which led me into the parent/child problems.

I've revisited the VAT guidance, and there is an option to calculate line VAT to the nearest 0.5p OR the nearest 1p. Bingo.

If I calculate the line VAT to the nearest 1p, AND save the calculated line VAT in the transaction line, along with the calculated net value the problem has gone away. The performance hit has gone because I don't need to go to my VBA function to get the value, it's just a select and sum all within the SQL. I no longer need to save the sum of child values in the parent. The VAT total is a simple sum of the line values with no last minute rounding.

So for a customer ledger display I can go to every transaction line, fetch the calculated net and vat and sum up the balance, and it's independent of headers. I haven't tested it yet, but I'm fairly sure speed won't be an issue as long as I have the net and vat values saved in the line.

My batch posting routine update is then dependent entirely on transaction lines.

Got there I think. I couldn't find a way to get rid of the header totals, probably because there wasn't a way. Maybe no-one uses the fractional VAT calculation methods, I really don't know. Thanks Doc_Man for making the point forcefully.