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

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.
HometownCompAsked:
Who is Participating?
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
danishaniCommented:
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
HometownCompAuthor Commented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
HometownCompAuthor Commented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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

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
HometownCompAuthor Commented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
HometownCompAuthor Commented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
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.