Access network issue

Hi Experts,
I had today experienced an interesting problem, where by some users when opening an Access file on the network and tried opening the tables, got an error message, "Disk or network Error", however this did only happen to a few, while the majority of users didn't experience this, now my questions are:

A- What is the cause of it?
B- What is the solution?
C- How can I avoid it in the future?
D- Is it safe to run a batch file on schedule every hour to make a copy the Access file, so in case of an error, I can simply replace the file with the latest backup?

FYI- This file is the BE where all the tables of my FE App reside.
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
Probably is a network issue..have you changed something recently in your infrastructure.....
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
There are a wide range of issues which can cause that error message, even something as simple as Access not being able to create a temp file.

But as John said, probably a network issue.  

First drop to a command prompt on these stations and make sure that the environment variables TMP and TEMP point to valid drives and directories and they have plenty of free disk space (at least 2GB).

Next on the NIC setup, disable any diagnostic protocols and "sleep" options.  

If that doesn't improve anything, then replace any patch cables.

Also Access/JET will not work well over wireless connections   If your running wireless, run a cable instead.

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I missed part D of the question, the answer to which is no.

 While you can make a copy, doing so without the use of backup software with an open file option can give you a copy of the DB that is inconsistent.

 Backup software with an open file option pauses I/O in the OS, takes a snapshot, and then resumes.   So you end up with a copy of the entire file at one specific point in time.

 With a batch file and a simple file copy, that may not end up being the case as one part of the file may be copied, while another part is being updated.

Jim.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and one other problem that is common:

If your running the BE on a mapped drive, windows XP and up has a timeout on inactive mapped drives.   What may be happening is the mapped drive may be disconnecting.   Normally you see this in explorer where you have a red X on a mapped drive.   You click on it, it pauses a few seconds while it re-establishes the connection, and your off and running.

 That's death though to JET.

 Check out the other things I mentioned first though.

Jim.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I have also seen this exact error returned (erroneously) wherein a db is corrupted - the type of corruption that can only be fixed using Decompile.  But for most cases, it really is a network error, usually caused by a momentary glitch - unless ongoing. We see this occasionally at work - via my log file or user reports. It usually does not affect all users.
PatHartmanCommented:
Is the network wired or wireless?  Wireless networks are frequently too unstable to support Access FE/BE operations.
bfuchsAuthor Commented:
Hi Experts,

We had a meeting with our IT guy regarding this, and he claimed that its not a network issue, since users had access to that drive at the time this started happening, then I mentioned its possible just one user got disconnected while doing a critical task and this corrupted the file partially..then he suggested we run the following batch file in all pc's to verify if there are no network issues.
ping x.x.x.xxx -t > C:\testping.txt

Open in new window

Meanwhile we realized that all pc's having this issue are Windows XP Pro, while the Win7 pc's are fine..

FYI- we moved to a new location and then is when the fun started..

What are your thoughts?

Thanks,
Ben
bfuchsAuthor Commented:
Forgot to add that last night after this problem started, I had everyone logged off and did a compact/repair on the BE, then those pc's who originally had didn't have anymore, however this morning it started again, first by other users, then also by those who had yesterday as well..
PatHartmanCommented:
Access is the proverbial "canary in the coal mine".  If the network has "blips", Access will feel the pain.
bfuchsAuthor Commented:
@Pat,
My main concern now is how to prove it to the IT guy (or perhaps to our manager) that the issue is purely network related and not to point at the programming direction..
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I would do a Decompile, reCompile and Compact/Repair on the Front End and redistribute first.
bfuchsAuthor Commented:
@Joe,
what is the syntax of decompile? just type on the command prompt accessPath file name\decompile?
bfuchsAuthor Commented:
@Joe,
This is definitely not solely a FE issue, as I also tried opening the BE at the time the problem happened (currently when most users are not in the problem is not happening) and had this msg popup when double clicked on tables to open them, in addition to that, suddenly all table names disappeared from the DB container, and when I opened Access again they were all back..
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<ping x.x.x.xxx -t > C:\testping.txt>>

 That's a good test.   You really would like to see a consistent <10ms and no dropped packets.

 But that is just testing general connectivity on the network.  

<<Meanwhile we realized that all pc's having this issue are Windows XP Pro, while the Win7 pc's are fine..

FYI- we moved to a new location and then is when the fun started..>>

   That is significant.   I going to hazard a guess that network connectivity is fine and it's something higher up the chain and probably related to SMB (Small Message Block) protocol.  Microsoft has had issues with SMB 2 and 3 over the years and some directly effected Access.  

 It centers around OPLOCKs or client side caching.   Access/JET maintains it's own cache, so what the OS does just gets in the way.  Unfortunately, Microsoft has made it in later OS's that you now cannot turn it off.    But if you force SMB1 mode where you can turn it off, you loose some of the newer features.

 So what you can do is this: on the server itself or a station not typically used, maintain two open instances of Access with the app open and just leave them sit there.  That effectively disables the client side caching as the OS will see the file constantly open by more than one user.

 See if that resolves the issue.   Also, was a new server part of the new location deal?  or is it the same server you had?

Jim.
bfuchsAuthor Commented:
Hi Pat,
on the server itself or a station not typically used, maintain two open instances of Access with the app open and just leave them sit there.
We currently have this all the time that more then one user is connected, I dont think there is ever a minute in 24 hours period where there are less then 2 users connected, unless you are referring to the app be opened more then once by the same user?
Also, was a new server part of the new location deal?
Yes and no, we got a new server in place, however its just part of the network but nothing to do with either the Access or SQL BE, as these are in a different server.

Another point to bring up here, the FE is 60% linked to SQL back end, just about 40% that are Access based.

Thanks,
Ben
bfuchsAuthor Commented:
just adding another fact, this issue not always happens by those users, I was testing today one of those pc's and had opened the BE file couple of instances, they all worked fine, then suddenly one of the instances started having this issue, and soon after all tables disappeared.. while the other instances were all fine, so this proves its not simply getting disconnected from the server, as this would affect all instances, correct?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

on the server itself or a station not typically used, maintain two open instances of Access with the app open and just leave them sit there.


We currently have this all the time that more then one user is connected, I dont think there is ever a minute in 24 hours period where there are less then 2 users connected, unless you are referring to the app be opened more then once by the same user?

  Doesn't matter if it's one station with two instances, or two stations with one instance each.   Do you know with certainty that there are always at least two connections open?   What we are trying to rule out is a problem with SMB and client side caching.  If the problem happens with two instances already open, then it's not related to that.

  When your troubleshooting, you need to certain of your facts or you'll be chasing your tail.   I'm not trying to be a jerk here, but I've seen way too many situations over the years where someone says "I know that for certain...", and they really didn't, and it totally turns the problem on it's head.     Years ago, I worked with a system that had a reset button, we were having program issues, and it was blamed on the programming.   We were pretty sure the reset was being hit and the program restarted, but the users said they *never* touched it.    That was until we constructed a logging system to prove that they did.  Then it was a whole different story.

  Just make sure you know exactly what is happening.

<<just adding another fact, this issue not always happens by those users, I was testing today one of those pc's and had opened the BE file couple of instances, they all worked fine, then suddenly one of the instances started having this issue, and soon after all tables disappeared..>>

 By this you mean the XP users?   You were on a Windows 7 or 8 station?  If so then network connectivity is getting lost somehow...

 Also, has anyone checked the event logs on the server and stations for errors or warnings?

Jim.
John TsioumprisSoftware & Systems EngineerCommented:
I had an unusual situation some time ago very similar to yours and it turned out it was due to the fact our BE server had 2 IPs...it has 2 NIC so when it took an additional role in order to avoid changing settings the 2nd NIC took an IP (till the incident it was unplugged)..so we started having issues and it was only resolved when we disabled the 2nd NIC
bfuchsAuthor Commented:
@Pat,
If the problem happens with two instances already open, then it's not related to that.
This is absolutely the case here, it happens when other instances are open, in fact I think it mostly happens then when many users are hoked up.
just adding another fact, this issue not always happens by those users
By this you mean the XP users?   You were on a Windows 7 or 8 station?  If so then network connectivity is getting lost somehow..

XP users.
What I meant to say is, that although this is only occurring in Win XP computers, its not necessarily always happening there, for example I was testing one pc yesterday that had this problem during the day, but in my testing it only happened once out of many attempts to duplicate it.
Also, has anyone checked the event logs on the server and stations for errors or warnings?
I had yesterday someone checking these stuff, apparently nothing was found.

@John,
This is what our IT responded.
I I Usually always disable the 2nd NIC

Thanks,
Ben
bfuchsAuthor Commented:
Hi Experts,
At this moment we finally were able to detect from where the problem is coming from, and this is a new device from sonic wall that is causing problems for large files (and data as well) to be transferred over the network, however for yet unknown reason, this only affected XP computers.

@Pat,
Access is the proverbial "canary in the coal mine".  If the network has "blips", Access will feel the pain.

This statement is 100% true and I just wanted to add the following.
Perhaps I would suggest as a good practice to test network connectivity, to have at least one part of the program linked to an Access DB, so in case there are some network glitches you are immediately notified..
In our case, if not for this we would been operating with a problematic environment for the longest time and never realize there is something wrong here.. (It happen to be that one of our ADP users complained that once they got a connection failure, but this would never be paid too much attention).

Thanks,
Ben

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Thanks to all participants.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.