Solved

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

Posted on 2014-09-23
11
495 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 58
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 58
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 58
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
 

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 58

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 58
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 58
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

Retailers - Is your network secure?

With the prevalence of social media & networking tools, for retailers, reputation is critical. Have you considered the impact your network security could have in your customer's experience? Learn more in our Retail Security Resource Kit Today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

724 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