This function will always return false because you are not returning any value from it.
Try returning True if the State is 'Connected'
[EDIT] - To check if the State is connected use the following
If (Conn.State And adStateOpen) = adStateOpen Then
Return True
Else
Return False
End If
[EDIT] OP still struggling...
Part of the problem here appears to be the connection string to the database. It looks like a mixture of OLEDB and ODBC. If possible use Windows Authentication as recommended by MS. But the connection string needs tidying up anyway.
Apologies for misleading you above with
Return True
- I forgot that VBA struggles to get up to even the dizzy heights of VB6 syntax. The following function works - note I've given two versions of the connection string
Function ConnectToDB(Server As String, Database As String, User As String, Pwd As String) As Boolean
Set Conn = New ADODB.Connection
On Error Resume Next
Conn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & Server & "; Initial Catalog=" & Database & "; UserId=" & User & "; Password=" & Pwd & ";"
Conn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & Server & ";Initial Catalog=" & Database & ";Integrated Security=SSPI;"
Conn.Open
If (Conn.State And adStateOpen) = adStateOpen Then
ConnectToDB = True
Else
ConnectToDB = False
End If
End Function