All,
I admin an Access 2007 ADP that is distributed to our user base as an ADE. The back end is MSSQL 2005. Recently, we have begun deploying this software to employees in the field who connect to the corporate network via VPN. Because our field employees are sometimes connecting from some seriously flaky Internet connections, we have seen an uptick in Connection Failure errors.
I've been researching ways to detect and reestablish a failed network connection from VBA so that the user doesn't encounter the errors, or the need to close and reopen the application to continue working. This post appears to get me most of the way there, in that it both suggests a way to detect the failure and reestablish a connection on the fly:
http://www.utteraccess.com/forum/lof.../t1962513.html
Basically, it suggests replacing the call to CurrentProject.Connection with a call to your own custom function, looking something like this:
Code:
Function fCurrentConnection () As ADODB.Connection
If CurrentProject.Connection.State = 0 Then
CurrentProject.OpenConnection "YourConnectionString"
End If
Set fCurrentConnection = CurrentProject.OpenConnection
End Function
My problem is that I define my connection in the built in server properties of the project. My users connect using Windows NT Integrated security. I don't know what I should put in the "YourConnectionString" area. Is there a way to tell Access to use the stored settings to reconnect, or alternatively, can someone point me to an example string that connects to a server name and database name using Windows NT Integrated security?
Thanks!