Update multiple joined tables with recordset from another table (1 Viewer)

[Moore71]

Hi,
I am having a problem update multiple tables that are joined together in a query.
I want to update them with recordset in a multi-user environment
Please can someone show me the correct synthax to do that?
Thanks in advance
Moore

 

[CJ_London]

not enough information to do that.. you can only update one table at a time using sql

Show some example data, the outcome required and a description of the process you are trying to create

 

[theDBguy]

Hi. Are you running into locking issues? Can you show us your code?

 

[Pat Hartman]

ALL the tables individually MUST be updateable. That means they need unique primary keys. Then you have to join them. I would join tbl1 and tbl2 and save that as a query. Then join the query to the table you want to update from. The resulting query should be updateable but we don't know what your rules are so we can't offer any other suggestions.

 

[Moore71]

Hi. Are you running into locking issues? Can you show us your code?

Yes since it's in a multi-user environment
The users always run into locking issues when they seems updating the same thing in a split backend. So I decided that using recordset with (.Edit) will resolve the issue

 

[Moore71]

not enough information to do that.. you can only update one table at a time using sql

Show some example data, the outcome required and a description of the process you are trying to create

What I mean is that I created a Query of about 4-co-joined tables being updated simultaneously from the sales form or table with a criteria from the table matching the criteria on the form. About 6 different field/column are being updated in a row
All I need is the correct syntax to do this in a multi-user environment

 

[Moore71]

ALL the tables individually MUST be updateable. That means they need unique primary keys. Then you have to join them. I would join tbl1 and tbl2 and save that as a query. Then join the query to the table you want to update from. The resulting query should be updateable but we don't know what your rules are so we can't offer any other suggestions.

That's just what I did. My problem is that sometime the query does not update and sometime it does.
Then whenever 2 or more users are engage in the same item, the system freezes until one closes without updating

 

[Pat Hartman]

Generally it is poor practice to actually update multiple tables from any form/query. Although the RecordSource query for a form commonly contains joins to lookup tables, the lookup data isn't updated on the form since it is too dangerous. People loose sight of the fact that the lookup data relates to many records, not just the one they are updating.

We're going to know more about what you are doing. Is this a form where you are having a problem? Is it a query? If it is an update query, that is frequently a sign of some design flaw.
Are you attempting to control record locking? Your best option is to stick with the defaults. Access is quite capable of handling contention for recourses.

One particular problem with updating two tables at once is what is called deadly embrace. Updates are always done within a transaction which ensures that the entire update succeeds or if any part fails, all pending updates are backed out. If you have two users who might be doing different processes that involve one or more of the same tables you have the potential to lock up the whole system as you seem to be doing: In order for the transaction to work, the database engine needs to get a lock on the data from ALL tables being updated.

So, userA starts a process and Access enqueues table1 but UserB sneaks in with another process and enqueues tabld2. When the UserA process goes to enqueue table2, it can't because UserB has it locked and UserB can't enqueue table1 because UserA has it locked -- deadly embrace. Neither can proceed because some required object is locked by a different user. The database engine loops and retries but eventually either locks up (some versions) or just fails (other versions). That is why we don't normally try to make one update dependent on another.

Using recordsets will not resolve anything. We need to understand WHY you have to update all these tables in the same query because there is most likely a better option.

 

[Moore71]

Well thanks for your explanations so far
The whole idea of update this or that is based on the fact that it is an inventory application

I have the customer table whose account must be billed
The Product table must be update (Items purchased/ordered should be deducted from the stocked quantity)
The Sales Table hold the data that updates these other tables

I have no problem using query to update these tables all at once, but when 2 or more people are updating the same record at he same time, one or more user system got freeze up.
Although they have been managing it for a while, but now complaining bitterly about this error

 

[Pat Hartman]

I've created a dozen inventory applications for different types of businesses and I have never encountered this problem.

You are combining processes that should not be combined. Adding a transaction to the inventory table happens when you add an item to an order and neither have anything to do with billing.

I'm guessing that you are trying to update counts in an inventory record rather than adding a transaction that adds/deletes and indicates where the product came from or went to. The problem with this type of inventory management is that there is no audit trail and it is quite easy for the inventory to get out of whack and you have no way of figuring out what caused the problem.

I have no problem using query to update these tables all at once, but when 2 or more people are updating the same record at he same time, one or more user system got freeze up.

You might want to post your schema if you want some advice in a better structure.. If you want to continue with your present course, you should reread my last post so you can understand why conflicts occur. There is no magic way to avoid them when you use poor practices.

 

[The_Doc_Man]

The way you do what you describe is SORT of like encapsulation of disparate operations.

Instead of doing a monolithic update with some god-awful JOIN, make the updates linear - but THEN encapsulate them inside a transaction. See this link, for an example of starting and finishing a transaction that is the sum of various updates.

Use transactions in a DAO Recordset

Office VBA reference topic

docs.microsoft.com

Here is another example that uses SQL rather than recordset operations but that DOES use transactions.

Do we have transactions in MS-Access?

I am developing a small desktop application using C#.NET and MS-Access. I don't have any prior experience of MS-Access. I want to know if we can use transactions in Ms-Access or not. I have the be...

stackoverflow.com