Dale Fye
asked on
Intercepting SQL Server connection errors in Access VBA code, over a VPN
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.
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?
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Have not been able to pursue this as I've been working several other short suspense tasks. Hope to get back to this soon.
ASKER
Thanks guys. Finally got back to this decided to use Christian's method but liked Jim's approach as well.
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"