Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Intercepting SQL Server connection errors in Access VBA code, over a VPN

Posted on 2014-03-06
6
Medium Priority
?
1,098 Views
Last Modified: 2014-06-26
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:
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.

Open in new window

which is not intercepted by the Access error handler.  How can I intercept that error and handle it cleanly within my application?  Or is their some easy to test whether the VPN connection is open?
0
Comment
Question by:Dale Fye
6 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 39911464
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"
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39911537
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
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 800 total points
ID: 39912233
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.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 1200 total points
ID: 39915519
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
0
 
LVL 49

Author Comment

by:Dale Fye
ID: 39957685
Have not been able to pursue this as I've been working several other short suspense tasks.  Hope to get back to this soon.
0
 
LVL 49

Author Closing Comment

by:Dale Fye
ID: 40161420
Thanks guys.  Finally got back to this decided to use Christian's method but liked Jim's approach as well.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

971 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