Copy & update multiple tables from within forms?

[twgonder]

I understand perfectly well. That is pretty much what I was describing with my client. But what do I know. You go ahead do it your way.

Excuse me, it seems I wasn't following what you were trying to say then. In my case, it's pretty rare to have people change their names so many times that it creates the bloat you describe.

Oooops, spoke too soon, I can now see your answer.
As I post, it goes to a new page and I can't see the old page, or old posts get lost/hard to find in slow scrolling internet.



Anyways, my client isn't looking duplicate for every little change like phone number. Even tax id change isn't all that relevant, since it's the same legal entity. But when a person marries or a company is bought, those are situations that require a separation of history from future events. But I can see how a bank could go crazy with the notion.

As a side note, I ran a service bureau for a commercial banking application that had millions of accounts for hundreds of thousands of customers (of the 40+ clients) and it all fit on a DB with around 400MB, so I can't imagine I would have a problem with a single db max of 2GB and only one client per db file.

[June7]

You could build a query object with the INSERT SELECT. Then VBA calls the object to run it. DoCmd.OpenQuery "query name"

[twgonder]

Yes, code can certainly copy data from one record to another. One way:

[1)] CurrentDb.Execute "INSERT INTO table(field1, field2, field3) SELECT field1, field2, field3 FROM table WHERE ID =" & Me.tbxID

[2)] Another utilizes recordset objects.

You bring up something that I've wondered about. I've seen examples, as I've meandered through documentation and forums, of the two approaches. But I have yet to find a good explanation of when to use one approach over the other and why. Have you seen anything that explains this well?

[twgonder]

per post #7 - tried it, experienced all the complications and the performance hit, multiuser or not. Key case in point, client (a financial institution) had a legal requirement to maintain a record all changes. had around 110,000 'primary records' which with changes built up to around 700,000 records. Performance dropped off primarily due to the requirement to get the current record. Changed it to maintaining the data in a history table (which included the current 'view') but users worked off the 'current' table and performance back to instant. Generation of invoices, copy statements etc referenced the history table.

I had a similar problem and used history/archive files to store completed transactions. This was necessary in the early days as the db didn't have indexing (we had to write it ourselves, what a lot of work!). But when the db developer finally added indexing to the db natively, all the history files became moot. We just had to index search the active or processing status of transactions and bang, no more searching through a huge file (table) to find what small percentage of the records were needed for the immediate task.

I'm surprised you had performance issues. One of the first things I did when Access was released was import close to a million transaction records. This was back in the early 90s. I was impressed at how fast Access could find the desired records without indexes (were talking minutes, but impressive compared to the mainframe). I added a few indexes and was further amazed at how fast Access was able to get the data in a query.

[June7]

You bring up something that I've wondered about. I've seen examples, as I've meandered through documentation and forums, of the two approaches. But I have yet to find a good explanation of when to use one approach over the other and why. Have you seen anything that explains this well?

Mostly personal preference but some things to consider:

1. SQL injection risk with concatenation building SQL statements - can mitigate with use of parameters (sql - How do I use parameters in VBA in the different contexts in Microsoft Access? - Stack Overflow) whereas using recordset approach avoids any SQL injection risk; Access can't execute multiple SQL actions in one statement so damage that can be done with SQL injection is limited but could still be devastating

2. building string with concatenation can be somewhat confusing

3. with recordset approach can create only one record at a time and creating multiple records would require looping structure, whereas an INSERT SELECT is a way to batch create multiple records in one action

[CJ_London]

probably irrelevant to this thread but just to answer these questions

Even tax id change isn't all that relevant, since it's the same legal entity.

I'm talking about tax domicile. My client paid interest on the accounts. tax domicile had to be be reported to the UK tax authorities and dependent on the arrangement with other countries could impact whether UK tax had to be deducted (and paid to the UK tax authority) on interest payments or not. Around 10% of their clients lived abroad.

1) One has to list all the fields to insert, a big pain when there are dozens of fields to insert and you can't use * (I'm not sure what Access would do trying to insert the autonumber from the old record)

one of the benefits of using a history table - you can just use * because in a history table you have a few extra fields, typically it's own PK, a field for the user name and a timestamp.

I'm surprised you had performance issues.

Performance expectations were set at the beginning - instantaneous to a year plus on, 'getting a bit slow' and the following year 'unacceptable'. It was the first and last time I allowed a client to dictate how they wanted the data stored. The app was used for 20+ years before they were taken over and was replace by their new owners own systems.

[twgonder]

@ June7 Thank you for the time to give me the ideas and links, I'll read up now that I have something to read.

@ CJ_L Nothing is irrelevant. I have a set of knowledge that spans a lot of years, but I understand how miniscule it is. I absorb what other knowledge others are willing to share. It broadens my perspective and prepares me for the problems you already conquered. Thank you.

I'm curious, did your indexes get so large that they too were responsible for a performance hit? In my old design, at the detail transaction level, I had only three indexes, customer, claim, and processing status. These indexes were able to handle 20+ years of data without degredation. So, I'm curious how Access will compare. A big considerration between the mainframe vs. PC-client-server platforms is the centralized nature of mainframes and the fact that gobs of data didn't have to move across networks to serve data hungry clients. (Another great advantage of centralized computing: we spent our time doing what needed to be done rather than asking dozens of questions on forums to avoid thousands of little pitfalls. The godfather of the db I used once quiped, "If God had of wanted brains to be distributed, he would have put them in our fingertips.")

Yes, and tax issues are getting even crazier with reporting requirements being different from country to country. I now live in Colombia most of the time, and I have to report a whole other set of information that is irrelevant to my taxes in the USA. For example, in Colombia I get taxed on my net worth. Talk about a stupid incentive to keep the populace poor and unprepared for retirement!

[CJ_London]

I'm curious, did your indexes get so large that they too were responsible for a performance hit?

long time ago - but on the system your are proposing, if I recall correctly there was PK, accountFK, name, datefrom, timestamp. One of the clients requirements was they wanted a full list of customers which they could filter - so that required a subquery to identify the current record - the form worked a bit like a split form. Bear in mind also tyhat networks were slower back in those days