Solved

ODBC DSN error in MSaccess database only when disconnected from wireless and only on one laptop

Posted on 2014-09-23
11
467 Views
Last Modified: 2014-10-08
We have a small MS Access database that is being used (for a while now) on 6 laptops (win 7).  The database runs in earlier version (mdb) mode but all laptops are using Access 2007.  The users work in it locally until they want to sync with the office.  We have an upload/download procedure that uses the odbc linked tables to their sql server data.  Recently a new laptop was purchased and set up the same exact way as the others.  On this new laptop, everything works fine in the database until the wireless internet connection is disconnected, then while accessing local forms and data, we get a few DSN, odbc errors and the user cannot use it.  In the office, this happens even though they are still connected to the network that the dsn uses.  It is only when they disconnect the automatic wireless connection for the internet.  No linked tables are being accessed when this happens.  I'm stumped!  Does anyone have any ideas?  The sql server driver is also the same version number as the other laptops.  The company's network IT says it's our problem (software) but I can't find any reason or any difference between the working laptops and this one.
0
Comment
Question by:HometownComp
  • 6
  • 4
11 Comments
 
LVL 57
ID: 40340337
<<The company's network IT says it's our problem (software) but I can't find any reason or any difference between the working laptops and this one. >>

  First, not true.   Access can't even tell a wireless vs any other connection.  It just uses the network.   It is sensitive to latency however.

<<then while accessing local forms and data, we get a few DSN, odbc errors and the user cannot use it. >>

 What kind of errors?

<<It is only when they disconnect the automatic wireless connection for the internet. No linked tables are being accessed when this happens.  >>

 Not sure I understand the setup...

Jim.
0
 
LVL 12

Expert Comment

by:danishani
ID: 40340367
For sure using WAN, you need to have a persistent connection to your back-end.
Check this link of Daniel Pineault on how to accomplish this.

Persistent Connection in a Split Database

Hope this helps,
Daniel
0
 

Author Comment

by:HometownComp
ID: 40342756
In this situation we do not want a persistent connection to the back-end.  The database is more of a stand alone app that users use out of office 90% of the time.    All the functions they use are using local tables.  When they need to send the data they've entered to the system at the office, they will go to the office, connect to the network and then run a process (within the db) that moves the local data to their sql server database using linked tables.  This has been working fine for a long time now and we only have a problem right now on one laptop which is new.  If I was to try to open a sql linked table in the app (on the working ones), I would get the expected "Microsoft Office Access" "ODBC -- Connection to 'ourSqldataDsnName' failed." error.  

The laptop with the problem is giving a connection error (this is close but may have different error #'s as i cannot connect to this laptop at the moment) Connection failed:
SQLState: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][DBNETLIB]   ConnectionOpen(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]  SQL Server does not exist or access denied.

I would expect that error if I set up the dsn incorrectly or this user's network log in or something was not allowing them access.

But the user works with the application with out any error or trouble UNTIL they leave the office (or disconnect the wireless connection).  

Thanks for any ideas!!!
0
 
LVL 57
ID: 40342779
<<SQL Server does not exist or access denied.>>

 This is client setup on the driver, security, or a DNS problem.

1.  Have the laptop connect with wireless
2.  Ping the SQL Server by IP
3.  Ping the SQL Server by name
4. Switch to a wired connection.
5. Repeat steps two and three.

That covers the basic networking and DNS.   After that it would be security.

Jim.
0
 
LVL 57
ID: 40342783
<<But the user works with the application with out any error or trouble UNTIL they leave the office (or disconnect the wireless connection).  >>

 and clarify this some more please; you said when out of office, they don't work with the SQL server tables at all, so why are they getting an error?

Jim.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:HometownComp
ID: 40342825
The 6 users using their laptops out of the office are NOT getting errors.  Only the new laptop is having this trouble and only when they disconnect from the wireless connection.  For example.. this laptop is now in their office, they connect me to their desktop (so are connected to wireless internet) and I open the access app, do some test work and have no problems at all.  I then disconnect from them and they turn off the laptop's wireless connection (they are still connected to their Network).  The app freezes and gives them the sql connection error etc.  They turn wireless back on, re-connect me to their desktop and I see the errors they left for me to see.  I close and re-open app and can work again.  OR  they will leave the office and be somewhere without internet and then they can't work at all in it.  This is why I'm having such a hard time with this.  It just doesn't make sense! They can even disconnect from their network connection and keep the wireless connection on and no problems.  Its only when disconnected from the wireless connection.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40343701
I guess I have to ask why they are messing with the wireless at all if sitting in the office, and a better question is why do they switch to wireless for you to remote-in.

But more than certain, it's nothing to do with the app.    It's network connectivity and/or security.  Plain and simple.

You can prove this simply enough; setup a DSN and use the test connectivity button.  Access is not involved at all at this point.  Try it both ways.   If it fails (which it will), you can then go back to the IT folks and tell them to figure it out starting with the tests I gave you.

The problem when running through the wired network is either that:

a. the SQL Server cannot be reached - that's either routing or DNS

b. SQL Server security

Jim.
0
 

Author Comment

by:HometownComp
ID: 40344317
I think you are right although I am far from that office and cannot test it locally.  You've confirmed what I've been thinking all along.. that it has to be something set incorrectly on that machine and they are not seeing it..  It's a strange setup as it appears that they have some kind of wireless connection to the network that is separate from the wireless connection to the internet.  It's only when they disconnect the one to the internet that this happens.  Its almost as if the dns, odbc to sql server is finding the sql server through the wireless internet connection.  I'm going to ask that they use a wired connection to the network, turn off all wireless connections and reset it.  I will get back to you with the results.  Thanks!!!
0
 
LVL 57
ID: 40344326
<<It's a strange setup as it appears that they have some kind of wireless connection to the network that is separate from the wireless connection to the internet. >>

 That is not unusual...most routers today have a wireless network connection (connects user to LAN and WAN), and a "guest" network, which allows them access only to the internet (WAN)

 Jim.
0
 

Author Closing Comment

by:HometownComp
ID: 40368103
We had the Network IT re-set up the dsn/odbc connections while disconnected from the internet and this led him to find that there was a problem with security in the user profile.  Thanks for all your help!
0
 
LVL 57
ID: 40368187
Nice to hear you got it solved.

and +1 for the developers<g>

I always chuckle a bit when I get the standard "it's your app that's the problem" comment.

Jim.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now