Try this function.
It covers a wide variety of connection types:
Code:
'############################################
' GetConnectionString 04/08/2010
' Used to create a connection string
'############################################
Function GetConnectionString(strLinkType, _
strLinkServer, _
strLinkDatabase, _
strAppName, _
strLinkUsername, _
strLinkPassword) As String
Select Case strLinkType 'Get the link type to determine the connection string
#If SQL_DRIVER = "Native" Then
Case "SQL"
GetConnectionString = "ODBC;DRIVER={SQL Native Client};" & _
"SERVER=" & Nz(strLinkServer, "") & ";" & _
"DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
"APP=strAppName;" & _
"UID=" & Nz(strLinkUsername, "") & ";" & _
"PWD=" & Nz(strLinkPassword, "")
Case "SQL-Trusted"
GetConnectionString = "ODBC;DRIVER={SQL Native Client};" & _
"SERVER=" & Nz(strLinkServer, "") & ";" & _
"DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
"APP=strAppName;" & _
"Trusted_Connection=yes;"
#ElseIf SQL_DRIVER = "MDAC" Then
Case "SQL"
GetConnectionString = "ODBC;DRIVER={SQL Server};" & _
"SERVER=" & Nz(strLinkServer, "") & ";" & _
"DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
"APP=strAppName;" & _
"UID=" & Nz(strLinkUsername, "") & ";" & _
"PWD=" & Nz(strLinkPassword, "")
Case "SQL-Trusted"
GetConnectionString = "ODBC;DRIVER={SQL Server};" & _
"SERVER=" & Nz(strLinkServer, "") & ";" & _
"DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
"APP=strAppName;" & _
"Trusted_Connection=yes;" & _
""
#End If
Case "Access"
If Nz(strLinkPassword, "") <> "" Then
GetConnectionString = "MS Access;" & _
"PWD=" & Nz(strLinkPassword, "") & ";" & _
"DATABASE=" & Nz(strLinkServer, "") & Nz(strLinkDatabase, "")
Else
GetConnectionString = "MS Access;" & _
"DATABASE=" & Nz(strLinkServer, "") & Nz(strLinkDatabase, "")
End If
Case Else
GetConnectionString = ""
End Select
End Function
HTH