Issue doing update queries over local network

[alvingenius]

Hello
We have a db splitted into FE/BE, and the backend is in a server machine
and regarding the front end... First, it was In Server too and users have a shortcut from it on local machines and everything was good except a button that execute 3 update queries, and I've put a msgbox that appears after executing the 3 queries, and sometimes I got the msgbox, but after revision, I might find that not all queries executed!
So, sometimes 1 or 2 queries, and I've to press the button again to re-execute the 3 queries, and this time it executed

then I thought it happened because the frontend / backend is opened through the network, and I've changed how users open the front end file, by making a batch file to copy the front end to the user local PC and open it, and this issue still exists when executing multiple update queries, I've even put a wait command to wait 3 seconds between every update query and still, sometimes they are not executed from the first time!

Does anyone face this issue working on DB over a local network ? or what do I do wrong here?

 

[CJ_London]

never happened to me. As to what you did wrong? anyone's guess without seeing the full code relating to this action.

I would say that by having users share the FE as you had it originally may have caused a corruption, so unless you decompiled>recompiled then compact repaired, it might still be there once you provided your users with a local copy. So decompile, recompile the compact, see if that fixes your problem

If you are going to post your code, make sure you use code tags to preserve indentation

 

[alvingenius]

never happened to me. As to what you did wrong? anyone's guess without seeing the full code relating to this action.

I would say that by having users share the FE as you had it originally may have caused a corruption, so unless you decompiled>recompiled then compact repaired, it might still be there once you provided your users with a local copy. So decompile, recompile the compact, see if that fixes your problem

If you are going to post your code, make sure you use code tags to preserve indentation

Thanks, @CJ_London for your reply
the code of the button is simple, here's the code in the button

and I've done decompile/recompile, and compact repair.

I believe the issue is executing these 3 update queries through the network !!, maybe it got interrupted !

 

[CJ_London]

is this problem specfic to a particular file? i.e. can be replicated? or completely random?

disable your setwarnings and see what errors are generated

you can step through the code as well

Also what is the code for waitfor? perhaps that is causing the issue and the same for your queries - perhaps there is something in the data (such as nulls or a wrong value) causing the error

 

[alvingenius]

is this problem specfic to a particular file? i.e. can be replicated? or completely random?

disable your setwarnings and see what errors are generated

you can step through the code as well

Also what is the code for waitfor? perhaps that is causing the issue and the same for your queries - perhaps there is something in the data (such as nulls or a wrong value) causing the error

it's the only button that has 3 update queries, so I can't replicate it
if I disabled set warnings it will execute!
and regarding the "wait for" code, it's for waiting for * seconds after executing every query, I thought it may fix the issue

again, I think it's because of the network ! because it's random on all other users but if I execute it from the server it self, it will be executed without any issues

 

[CJ_London]

you have not answered all my questions or tried/investigated my suggestions other than disabling setwarnings

I'll also add are your users connecting wirelessly to the BE or using a hard wired network?

 

[alvingenius]

you have not answered all my questions or tried/investigated my suggestions other than disabling setwarnings

Hello
I think I did !!,

• I said I can't replicate the issue, because it's the only location that can execute 3 update queries
• the code is so simple, it runs update query so, nothing in the code
• regarding setwarnings I said the queries execute normally with warnings !, if I execute the queries manually, it will execute normally too.
• the waitfor code is being used after this issue appeared !!! i thought I need time between executed queries, so, the wait code is a try to fix the issue, it's not causing it ! because it exists before the wait code!
• regarding decompile/recompile, already done

AND AGAIN !!!​

I think it's because of the local network! because it's random on all other users, sometimes it work, sometimes it didn't work,!​

but if I execute it from the server itself, it will be executed without any issues from the first time​

 

[CJ_London]

Since this is random for the users, the implication is there is a problem with your source file and will be difficult to track down if you cannot replicate it - but then you say it works fine on your server, but that is presumably after a user has tried to import - and you said a second import worked OK.

And since this only affects users using a local FE the implication is there is something different in their setup to the one on the server. Might be different Access versions, might be they are missing a library, might be the way they are connecting, might be they are connecting the the BE wirelessly, might be their user rights. Might be something else.

 

[alvingenius]

And since this only affects users using a local FE the implication is there is something different in their setup to the one on the server. Might be different Access versions, might be they are missing a library, might be the way they are connecting, might be they are connecting the the BE wirelessly, might be their user rights. Might be something else.

By random i mean sometimes it work from the first time and sometimes its not from the same users pc
so its nothing related to access version or user rights, ( while the access version are identical and user rights are good )

when i tried it on server it self that have the BE, to just test the if the code will execute the way i want, i open the same exact batch file used with other users use, to make a copy of the FE, to the local machine, then i open the db and click the button that execute the 3 update queries, and it work normally without issue.

i think its related to NIC for the users maybe speed is not 10/100/1000 Mbps , since i made a try on my high end PC and it works normally too, and all our network is supporting this speed

So i’ll check tomorrow if the NIC of one of users PC is at least 100 Mbps or just replace and test , and will update this post

Thanks @CJ_London

 

[SHANEMAC51]

I believe the issue is executing these 3 update queries through the network !!, maybe it got interrupted !

you have blocked the output of warnings, so you do not know the result of the request

 

[The_Doc_Man]

Some thoughts, some of which will perhaps be helpful.

1. If the FE is on the user's local machine, that eliminates any problems with file locking of anything in the FE because that is now a PRIVATE copy and has no competition for the locks. That locking competition is one reason we advise private FE/shared BE. For a shared FE, you end up locking forms and reports. That can become an issue. Not to mention that the local FE means local locks, which operate at memory speed, not network speed, and thus are resolved MUCH faster. Like maybe 30 times faster on a 100 MHz NIC or 300 times faster on a 10 MHz NIC.

2. If the BE is a native Access DB then the WAITFOR(1) does nothing useful because Access back-ends are passive. ALL repeat ALL Access actions in a native FE/BE case are synchronous. (Well, OK, it is POSSIBLE to do some API calls to change that in a limited way - but it is a pain in the toches to do it.) There is nothing to wait for because everything is being done in the workspace associated with the FE file. There is nothing external with which to synchronize. So I would suggest that if that BE file is a true native Access .MDB or .ACCDB file, drop the WaitFor. All it does is pointlessly slow you down. At best it is a smoke-screen. If your users are on a slow network card, that won't change the synchronous nature of the operation. If this turns out to be related to a slow network, see item #3 below.

3. The fact that you run 3 queries sequentially makes me wonder if the queries are set for PESSIMISTIC or OPTIMISTIC or NO locks. At least in theory on a LAN that doesn't involve any wireless connections, and particularly given the synchronous nature of Access queries, the best setting is either NO LOCKS or OPTIMISTIC LOCKS for all BE-targeted queries. A slow NIC combined with PESSIMISTIC LOCKING is almost CERTAIN to give you an occasional "hit" on a busy application if two or more people are using it at the same time and touching the same records. That hit would be reported as some variant of a locking error. Disabling your warning messages also disables your error messages, which is why we have to guess at the real cause. With an error message, there should be no question as to the cause of the problem.

If you want to do your updates WITHOUT a warning message (even with warnings enabled), use this:

Instead of: DoCmd.OpenQry "Update_in"
Use this: CurrentDB.Querydef("Update_in").Execute dbFailOnError
(and of course, apply the same treatment for the other two queries.)

Here is a link on the .Execute method including how to use it to execute specific stored queries.

If you have an SQL string:

Database.Execute method (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

If you have a stored SQL query already defined:

QueryDef.Execute method (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

The .Execute operation is silent but the dbFailOnError option triggers an error message (and a rollback) if something actually DOES fail within the query, like "duplicate index" or "syntax" or "type mismatch" or whatever. But you won't get messages like "You are about to update...." because the .Execute takes care of hiding that. Also, .Execute is SLIGHTLY faster than the .RunSQL method. VERY slightly.

NOTE that if the BE is NOT a native Access BE file, you should have specified that earlier, because that would change the advice I just gave you in my point #2. (But it wouldn't change #1 or #3.)