At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.
Course Of The Month
10 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Intercepting SQL Server connection errors in Access VBA code, over a VPN
Posted on 2014-03-06
I have an Access application that is running on the computers of numerous individuals at one of my clients, running various different versions of Windows and Office. We have migrated a number of tables to SQL Server but the connection strings for the different computer configurations are different, so I have a function which attempts to open a pass-thru query that selects the first record from one of the tables on the SQL server. If that process raises an error, the error handler attempts several other connection strings until it finds the proper one for the current users computer; that part works fine.
However, some of the users of the application do so over a VPN, with the application on their home or laptop and if they have failed to open the VPN connection prior to running the application, the receive the following error message:
However, some of the users of the application do so over a VPN, with the application on their home or laptop and if they have failed to open the VPN connection prior to running the application, the receive the following error message:
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 is denied.
[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
-
2
- +2
6 Comments
Active 1 day ago
here you are:
Potential causes of the "SQL Server does not exist or access denied" error message
http://support.microsoft.com/kb/328306
looks like you are using DSN ODBC that may need to be checked with potential adjustment of uid\pwd sql server name there and verification of the sql uid\pwd sql server name by attempt to login on sql server via ssms to make sure that "sql server exists and used sql login has access to sql server\DB there"
Potential causes of the "SQL Server does not exist or access denied" error message
http://support.microsoft.com/kb/328306
looks like you are using DSN ODBC that may need to be checked with potential adjustment of uid\pwd sql server name there and verification of the sql uid\pwd sql server name by attempt to login on sql server via ssms to make sure that "sql server exists and used sql login has access to sql server\DB there"
Take one of the machines home with you and set it up on your home network. You'll get an IP something like 192.168.0.4 or 192.168.1.5. There's a list available of class C addresses that can be used by anyone because they're kept behind a NAT and not seen by the outside world - hence lots of people have the same IP addess but don't collide.
Anyhow, then open up the VPN and everything and get into your work network. At this point you'll be on another network and you'll probably have a different address range - 10.something, in all likelihood.
Anyhow, have a server in work that Access can try to find: if it does then it's operating via the VPN, and if not it can tell the user to open up the connection.
hth
Mike
Anyhow, then open up the VPN and everything and get into your work network. At this point you'll be on another network and you'll probably have a different address range - 10.something, in all likelihood.
Anyhow, have a server in work that Access can try to find: if it does then it's operating via the VPN, and if not it can tell the user to open up the connection.
hth
Mike
Active today
I think I would simply take the server name from the connect string and try to ping it. That will tell you if the network is up or not.
Jim.
Jim.
Active today
Hi,
in all connection strings always use the FQDN (full qualified domain name) and not only the name of the server. As an alternative you can also use the IP address but it's better to use the FQDN as an IP address could change sometimes (not very often in case of servers).
Sometimes a big network is divided into several subnets and without the FQDN it only searches in the direct reachable subnet and not all the others (depending on your network configuration).
Cheers,
Christian
in all connection strings always use the FQDN (full qualified domain name) and not only the name of the server. As an alternative you can also use the IP address but it's better to use the FQDN as an IP address could change sometimes (not very often in case of servers).
Sometimes a big network is divided into several subnets and without the FQDN it only searches in the direct reachable subnet and not all the others (depending on your network configuration).
Cheers,
Christian
Active today
Have not been able to pursue this as I've been working several other short suspense tasks. Hope to get back to this soon.
Featured Post
Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat. The purpose of this eBook is to educate the reader about ransomware attacks.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette.
This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month10 days, 5 hours left to enroll
624 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.