Hi,
I create a .udl file (In Windows 2000 or XP create a text file, then rename
it to have a .udl extension. Finally double-click to open it and connect to
your data source - EG SQL Server)
In Access I usually have the following code, it checks teh connection and
resets it if anything has changed:
Sub InitialiseAdoConn()
Dim Path As String
Dim Conn As ADODB.Connection
Dim ConnCS As String, ConnCurrentCS As String
On Error Resume Next
Path = GetSetting("ApplicationName", "UDL", "Path", "")
If Path = "" Or Dir(Path) = "" Then
Path = GetUdlPath("Please find the udl file on your network")
If Dir(Path) = "" Then
MsgBox "Udl file not found or user cancelled operation, cannot
continue"
End
Else
SaveSetting "ApplicationName", "UDL", "Path", Path
End If
End If
'Open new connection from udl file so we can check nothing's changed
Set Conn = New ADODB.Connection
Conn.Open "File Name=" & Path
'If connection has changed, close current connection. Test Server name
and catalog only.
'The connections strings are in a different format so just compare
server and database names
Set ConnCurrent = CurrentProject.Connection
If Not (ConnCurrent Is Nothing) Then
If Not (Conn Is Nothing) Then
ConnCurrentCS = ConnCurrent.ConnectionString
ConnCS = Conn.ConnectionString
If GetStringPart(ConnCurrentCS, "Catalog=") <>
GetStringPart(ConnCS, "Catalog=") _
Or GetStringPart(ConnCurrentCS, "Data Source=") <>
GetStringPart(ConnCS, "Data Source=") Then
Disconnect
Set ConnCurrent = Nothing
End If
End If
End If
If ConnCurrent Is Nothing Then
If Not (Conn Is Nothing) Then
CurrentProject.OpenConnection Conn.ConnectionString
Set ConnCurrent = CurrentProject.Connection
Else
MsgBox "Please select File, Connection: locate your SQL Server
database and connect to the Assets database." & vbCrLf _
& "Please talk to your SQL Server administrator if you are
not sure whether to use NT authentication or use a SQL Server Login."
End
End If
End If
Conn.Close
Set Conn = Nothing
End Sub
Function GetStringPart(Str As String, FindStr As String) As String
Dim i As Integer
Dim length As Integer
Dim Result As String
i = InStr(Str, FindStr)
If i = 0 Then
GetStringPart = ""
Else
Result = Mid$(Str, i + Len(FindStr))
i = InStr(Result, ";")
If i > 0 Then
Result = Left$(Result, i - 1)
End If
GetStringPart = Result
End If
End Function
The GetUdlPath function is a simple one wrapped around teh get file name
Windows API code you can find at
www.mvps.org/Access/
The beauty of this system is that wherever you want to get SQL Server data
(Access, Excel, Word etc) you use the same code to get the connection via
the udl file. When the server or database name changes (EG you go from test
server to production server) simply re-direct the udl file and all users
seamlessly get re-directed.
--
Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit
www.projectlearning.com/