ODBC Communication Link Failure - Connection Lost : MS Access to SQl Server

Question

ODBC Communication Link Failure - Connection Lost : MS Access to SQl Server

rangu

Default port

Points: 1407
More actions
December 15, 2015 at 10:06 am

#73657

Hello
I have an MS Access Application running on users desktop, which connects to Sql Server Back end.
Users have to vpn from home to access application. The issue is when the network connection is lost, the Access is showing a bunch of crazy errors which is obvious.
All I need is to trap these errors and let these errors show a customized message to the user.
How and where exactly I need to trap these errors, while the user is already in the database and working on it.
Thanks in advance..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WendellB SSCrazy Eights Points: 8678 More actions · Answer 1

The errors you are getting should have a numeric code on them. As long as you have some VBA code running you can use error-trapping to identify the error and display a customized error message to the user. If the users are always displaying the form you described in your other thread, you might consider using the OnCurrent event, or the AfterUpdate event of the form.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

rangu Default port Points: 1407 More actions · Answer 2

Hi Wendell, Thanks for your response.

I keep getting the DSN Sql Server Login prompt, attached screenshot. I have to cancel this 3 times then it goes to form_Current event. Here I have used the logic you have suggested to avoid the communication link failure error message.

I have created a function in a module and trying to close the database and all the open forms but it says..

RunTime error 2486 - You can't not carry out this action at the present time..

From here I had to kill the DB from task manager..

I am completely out of options now, been like 2 days working on this now but no results.

I want the initial prompts (DSN) to by pass, not sure how to do that. Then exit the DB.

rangu Default port Points: 1407 More actions · Answer 3

I have done some tweaking by creating a blank database and tried to replicate the scenario.

Its all about the linked tables, the password was not saved. I deleted and relinked, this time checked the save password.

Now I no longer see the Sql Server login prompts, also most of those errors I pointed were suppressed.

I always thought saving passwords on linked tables is not a good idea.

can anyone suggest, the better way please...

Any inputs is highly appreciated, thankyou.

WendellB SSCrazy Eights Points: 8678 More actions · Answer 4

The issue of security in SQL Server is complex. It sounds as if you are using SQL Server Standard security, and in that case you do have to store the password in the table, or the user would have to enter repeated passwords each time the table was opened. The preferred alternative is to use Integrated Security but with a VPN connection that may not be possible. It requires a Domain Controller, and each user needs their own userid and password. That has several advantages as it let's you track who makes changes, and you can control the permissions of each user. You might want to look at the security topics on this site.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!