Connect to a SQL Server database with Outlook

Question

I want to connect to a MS SQL Server database using an Outlook macro. But I don't know if the code is wrong or I need to add a library/driver or what happens here but it doesn't work.

Private Sub Application_Startup()
On Error GoTo ExitHere
    'adodb connection to other database
    stg_cn.Open "Provider = SQLOLEDB;" & _
                        "Data Source = 192.168.100.100;" & _
                        "Initial Catalog = hugeDB;" & _
                        "Integrated Security=SSPI;" & _
                        "User ID = oneuser;" & _
                        "Password = onepassword;"

    sQuery = "SELECT * FROM documents where location = 'IE'"

    'set reference to query
    Set cmd = New ADODB.Command
        cmd.ActiveConnection = stg_cn
        cmd.CommandType = adCmdText
        cmd.CommandText = sQuery
    Set rs = cmd.Execute
    Do While Not rs.EOF
        For i = 0 To rs.Fields.count - 1
            MsgBox (i + 1)
        Next
        rs.MoveNext
    Loop
ExitHere:
    If Not stg_cn Is Nothing Then stg_cn.Close
    Set rs = Nothing
    Set stg_cn = Nothing
    Exit Sub

End Sub

Answer 1

On eye-test I am not able to figure out whats wrong, I think it has to do something with the way you are doing the ADO operations.

But I am just putting up the last macro I wrote to connect to SQL-Server from Macro. Hope it helps.

Private Sub Workbook_Open()
On Error GoTo ErrorHandler
    '**************************************Initialize Variables**************************************
    sServer = "<SQL SERVER Server>"
    sDBName = "<SQL SERVER DB>"

    '**************************************Open Connection**************************************
    'adodb connection to other database
    stg_cn.Open "Provider=SQLOLEDB;Data Source=" & sServer & _
                  ";Initial Catalog=" & sDBName & _
                  ";Integrated Security=SSPI;"

    sQuery = "SELECT * " & _
             "FROM Table "

    'set reference to query
    Set cmd = New ADODB.Command
        cmd.ActiveConnection = stg_cn
        cmd.CommandType = adCmdText
        cmd.CommandText = sQuery
    Set rs = cmd.Execute
    Do While Not rs.EOF
        For i = 0 To rs.Fields.Count - 1
            <PERFORM OPERATIONS>
        Next
        rs.MoveNext
    Loop

ExitHere:
    If Not stg_cn Is Nothing Then stg_cn.Close
    Set rs = Nothing
    Set stg_cn = Nothing
    Exit Sub

End Sub

Answer 2

The connection string @CodePhobia has provided should work for you.

The below just includes User ID and Password functionality, as your original question showed trying to connect using this.

Dim rsConn as ADODB.Connection
Set rsConn = New ADODB.Connection
With rsConn
    .ConnectionString = "Provider = sqloledb;" & _
                        "Data Source = myServerName;" & _
                        "Initial Catalog = myCatalog;" & _
                        "Integrated Security=SSPI;" & _
                        "User ID = myUserID;" & _
                        "Password = myPassword;"
    .Open
End With

You can use this website to find connection strings in the future. It should cover all possible connections you wish to establish.