ADODB Connection from Excel to SQL Server Database

To  create an ADOBD connection from Excel to SQL Server, first make sure to add a reference to Microsoft ActiveX Data Objects Library X.X Library (choose the highest version available).


Private Sub Worksheet_Activate()
On Error GoTo ErrorHandler
Dim con As ADODB.Connection
Set con = New ADODB.Connection

con.ConnectionString = "Provider='sqloledb';Data Source='SERVERNAME';" & _
        "Initial Catalog='DBNAME';Integrated Security='SSPI';"
    con.Open
    MsgBox "Con state: " & GetState(con.State)
   
   
    ' clean up
    con.Close
    Set con = Nothing
    Exit Sub
   
   
ErrorHandler:
    ' clean up
    If Not con Is Nothing Then
        If con.State = adStateOpen Then con.Close
    End If
    Set con = Nothing


    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub



Public Function GetState(intState As Integer) As String

   Select Case intState
      Case adStateClosed
         GetState = "adStateClosed"
      Case adStateOpen
         GetState = "adStateOpen"
   End Select

End Function