Update multiple joined tables with recordset from another table

[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?

 

[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

 

[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

 

[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

Access VBA conceptual documentation

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