SQL - Access connectivity issue

Hi Experts,

I am currently having an issue with my FE Access application suddenly being disconnected from SQL.

So far I didnt hear anybody in our office having this issue besides me.

Attached is a screenshot of the error msg that pops up when this happens.

What is interesting here is that its not just a network issue, as the Access tables linked to the same server pc dont get disconnected, only the SQL tables are having this issue.

In addition, I am able all the time to browse to that network server thru windows explorer.

Any Idea how to troubleshoot this problem?
untitled.bmp
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.

lcohanDatabase AnalystCommented:
Kind of typical connectivity/networking error and many different solutions/options to try to fix or workaround it described at links below:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b7a4c6f2-0d1b-44ba-bb55-0e7848d64198/microsoftodbc-sql-server-drivercommunication-link-failure?forum=sqldataaccess
https://support.microsoft.com/en-us/kb/942861

And one more from another posting:

<<
From the googling and reading I have done on this topic, it seems to me that this might simply be a bug in MS Access that they've never bothered to fix, ie: there is no resolution.

The symptoms I am seeing and that others seem to have is that the connection Access has to SQL Server becomes "bad", and once it reaches this state, nothing will fix it except restarting Access, period. Although it blows a person's mind, this is entirely possible - if the code related to connection management (either in MS Access itself, or within the ODBC provider) doesn't check validity of the connection state and always assumes it is fine, then we would see exactly the symptoms we are seeing.

You'd think, surely Microsoft would fix this, but it wouldn't be the first time.

>>

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
PatHartmanCommented:
I don't think there is any solution we can implement from the application side.  You can test the connection before you use it but it could still fail and since it probably works 99.99% of the time, testing first would be awfully wasteful.  You can attempt to trap the error and give the user a better error message.
bfuchsAuthor Commented:
Hi Experts,

@lcohan,
The two solutions I saw in that link

1- Turn off Auto updates
I would not think that's causing it as I still have Win XP and there are no updates for that OS anymore.

2- Replace network card
As stated above, this issue does not seems to be pure network related, as that would definitely affect my Access linked tables as well, in fact I would rather expect that should happen to Access than to SQL, as Access is much more sensitive to Network issues.

@Pat,
The issue just happens in middle of the work out of the blue, and only to my pc..therefore I am not so much concerned about displaying error msg to the user, neither do I want to change anything on the app site, rather would like to know if there is any thing I can do fix that in my pc, or perhaps its time to change it..
Attached are the computer specs.

Thanks,
Ben
untitled.bmp
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

David Johnson, CD, MVPOwnerCommented:
is the 'server' using a server o/s or a workstation o/s if workstation you could be hitting the 20 concurrent connections limit. (note: this does  not mean 20 computers)
bfuchsAuthor Commented:
@David
Its a server OS.

@All,
I will be out of the office next two days, hope to resume on this on Sunday.

Thank you!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Since it's only on your machine, could be troubles with the SQL driver you're using. Try updating/reinstalling that driver. Also be sure your machine is fully updated regarding Windows and Office, including any hot fixes.
John TsioumprisSoftware & Systems EngineerCommented:
if you try everything else and you don't get a fix then the only solution is to use Wireshark to check what is going on....not an easy task...but you can take a look here
bfuchsAuthor Commented:
@Scott,
1- I have this pc for long time already and only now occasionally did this started to occur, do you think something got corrupted?
2- I did remove the DSN setting and re-created it, testing shows everything fine, see attached.
3- I do have the latest service packs and hot fix for my office version.
4- The only thing that comes up in my mind is that we recently moved to a new location, however wondering how that can be affecting solely my SQL connection..?

@John,
that looks like a software requires purchasing, correct?

Thanks,
Ben
untitled.bmp
bfuchsAuthor Commented:
Hi Experts,

I just had a table opened for a while and got out for half hour, when I returned I see the attached.
untitled.bmp
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes, you could most certainly have experience corruption recently. Just happened to my development machine about a month back. I chose to go ahead a do a full rebuild, since my machine was a bit long in the tooth, but I most likely could have done an in-place reinstall had I chosen to do so.

It's not at all unusual for updates/service packs/hotfixes to get munged up with installing, and to hopelessly corrupt other items (and I think that's what happened to my machine, in fact). Many things in Windows are very, very "version-dependent", and update failures can cause lots of troubles. If I recall, you're working with a much older version of Access, so those sorts of issues are heavily compounded as well.

The only thing that comes up in my mind is that we recently moved to a new location,
Perhaps you have networking issues? As John mentioned, you'd have to investigate that with other software (like WireShark) to determine the validity of your network.
bfuchsAuthor Commented:
Hi Scott,

It's not at all unusual for updates/service packs/hotfixes
FYI I dont recall installing anything for the longest time, neither do I receive any windows updates on this pc, as its Windows XP and Microsoft stopped supporting it..unless you mean to say that updates to the server could also affect the older pc's linked to them via DSN?

Perhaps you have networking issues?
As mentioned above, this issue is only happening to my SQL tables and not to Access linked tables (linked to the same server location), and besides all other stuff are working well, like the windows browser to that location..

However our IT guy suggested today I run a ping constantly to that server's IP and see what happens when the problem occurs

(While writing this up, I thought of another idea, maybe I place the Access FE application in the server as well, and if there were a network dis-connectivity I should get the expected error msg for that case..not just ODBC failture)

Thanks,
Ben
bfuchsAuthor Commented:
Hi Experts,

I think have news for you.

Looks like lcohan first link provided has the solution

it says there the following:
I happened to see the application log in the Event Viewer...and lo and behold!

I saw Automatic Updates were sometimes starting and stopping the SQL Server(Instance) Services. When Automatic Updates were disabled the errors reduced drastically
And I see the same, when I turn off that option in my pc, I stop getting that error!

Just waiting another day to prove that..

Thanks,
Ben
bfuchsAuthor Commented:
Hi Experts,

I came in today at work and see attached what awaited me..
(after turning off auto updates).

However I think will keep that setting as it definitely reduced the quantity of this prob.
Besides I am not expecting any updates for my OS (Win XP) anyhow..

Perhaps someone can come up with an additional solution how can I eliminate this altogether? (Preferred without involving purchasing 3rd party software)

Thanks,
Ben
untitled.bmp
Hamed NasrRetired IT ProfessionalCommented:
Try to connect using a File Data Sourse dsn.
If issue persists, type here the contents of the File Data Source.
bfuchsAuthor Commented:
@hnasr,

What is file data source, isn't it the ODBC you setup under control panel and then use the access linked table manager to locate that DSN?

How can I open that dsn file and look whats in there.

fyi- the dsn when being setup shows successful connected.

Thanks,
Ben
Hamed NasrRetired IT ProfessionalCommented:
Yes, but you select File Data Source tab instead of Machine Data Source.

It is saved in a location, such as Documents.
It can be edited using notepad.
Select Data Source
bfuchsAuthor Commented:
@hnasr,

Actually, I take back what stated above that this is a SQL issue and not a network issue, as further testing do indicate a general network problem.

Just wondering why only My Access app gets affected..?

Thanks,
Ben
Hamed NasrRetired IT ProfessionalCommented:
--- this is a SQL issue ---
This why I ask for a dsn file.
Just wondering why only My Access app gets affected..?
Compare with a dsn file from another working environment.

Another thing to try, take a File Data Source from your system, and use it to connect on another machine.
bfuchsAuthor Commented:
@hnasr,

I Compared and both DSN are exact the same.

However as I see the issue is not specificly related to SQL, rather is it a network issue.

Is there anything you can tell me in that direction?

Thanks,
Ben
Hamed NasrRetired IT ProfessionalCommented:
However as I see the issue is not specificly related to SQL, rather is it a network issue.

When disconnection occurs:

Can you browse the internet?
Can you browse the server?
Do you have any hp network printer attached?
Do you have a firewall directing traffic to your specific pc?
bfuchsAuthor Commented:
Yes I can browse the internet
I can browse the server
I have some hp network printers attached, do you think they're causing it?
Not sure what firewall directing traffic means and how to check that out?

Fyi- while that disconnection take place, its only for that server, while my other Access app residing on the other server remain connected.

Thanks,
Ben
Hamed NasrRetired IT ProfessionalCommented:
@bfuchs

From your last comment, it appears to be a SQL issue.

Can you manage the server using SSMS?

If yes, then you may need to reinstall/repair office.
If no, then you may need to repair MSSQL SERVER.
bfuchsAuthor Commented:
@hnasr,

Originally I thought its a SQL issue, however after copying my app to that server I realized that this is a network issue, as the whole file got truncated, exactly what happens when I get disconnected out of a network prob.

the issue is not involving ssms as I dont use that from my pc, its purely a client issue.

Interestingly, I didn't have that the last two days..

Thanks,
Ben
bfuchsAuthor Commented:
It turned out that the turning off the auto updates helped, which is one of the solution suggested in that link.

Thank You!
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 SQL Server

From novice to tech pro — start learning today.