Solved

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

Posted on 2014-09-23
11
472 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

919 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

21 Experts available now in Live!

Get 1:1 Help Now