I Need VBA code to Update multiple tables over network (1 Viewer)

Moore71

DEVELOPER
Local time
Today, 19:41
Joined
Jul 14, 2012
Messages
158
Hi, can someone provide me with VBA code to update multiple tables on a split backend.
There are 2 or 3 system interconnected to the same backend and my puzzle is when 2 systems are updating the same Item, one will freeze while the other will update successfully
I have been using queries to perform this task, but when 2 system are updating at the same time, one freezes-up and the application must be restarted before the system can operate again



Please I need quick solution to this challenge.
Thank you
 

Mark_

Longboard on the internet
Local time
Today, 12:41
Joined
Sep 12, 2017
Messages
2,111
Just to make sure, you've checked to see that one of the processes has not opened the back end in exclusive mode? This could happen for one of many reasons.

Also, is this happening when doing batch inserts?

To help identify what could be causing the issue, can you post the code that is freezing?
 

Moore71

DEVELOPER
Local time
Today, 19:41
Joined
Jul 14, 2012
Messages
158
It's not code that I am using, but query.


What I am looking for now is VBA that update and while one is updating, the second should wait until the first has finished updating
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:41
Joined
Oct 29, 2018
Messages
21,358
Hi. What are you referring to as a “system?” Are you talking about two users? Just curious...
 

Minty

AWF VIP
Local time
Today, 19:41
Joined
Jul 26, 2013
Messages
10,355
You're doing a good job of avoiding answering some questions here.

Assuming you are using a saved query, what are the record locking options saved with it? If it's an update query change it to Edited Record.

As Mark asked, is one or other user opening the database in exclusive mode?
 

Mark_

Longboard on the internet
Local time
Today, 12:41
Joined
Sep 12, 2017
Messages
2,111
It's not code that I am using, but query.

Can you view your query in SQL mode then post the SQL here?
That may give us an idea of the next question(s) to ask, as well as help identify if there is anything we can see that is odd.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 19:41
Joined
Jul 9, 2003
Messages
16,245
Can you view your query in SQL mode then post the SQL here?

Further to Mark's request, I did video recently showing several ways that you can extract information from your database so that you can easily to post it in a forum to help people answer your question. Have a look at this link here:

Show Table Structure to a Forum
 

Moore71

DEVELOPER
Local time
Today, 19:41
Joined
Jul 14, 2012
Messages
158
Ok, assuming the query is opened in locked/exclusive mode, how do I get change it permanently for the 2 or more users to use the same query simultaneously?


I will try and paste the sample query here for all to view and comment
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 19:41
Joined
Jul 9, 2003
Messages
16,245
You're doing a good job of avoiding answering some questions here.

Yes, I agree. I will wait until the requested information is provided before responding.

Sent from my SM-G925F using Tapatalk
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:41
Joined
Feb 28, 2001
Messages
27,001
One thing that would probably not help is if you used pessimistic locking as opposed to optimistic locking or no locks.

It should be trivial to update a table that is shared over a network. I've designed things that way a hundred times or more. But getting multiple users to play together depends on getting the sharing done correctly so that they don't step on each other.

Has this EVER worked correctly or is this sudden "freezing" a new development?
 

Mark_

Longboard on the internet
Local time
Today, 12:41
Joined
Sep 12, 2017
Messages
2,111
Ok, assuming the query is opened in locked/exclusive mode, how do I get change it permanently for the 2 or more users to use the same query simultaneously?

Until we see what you are doing we won't be able to give advice.
You should also describe what you are TRYING to do, that way we can see if you are doing something different.
 

Users who are viewing this thread

Top Bottom