Connection – SQL Server – ExcelOMG Cookbook

NOTE: ???? Sub says Access!!!

On local PC

Public objConn As New ADODB.Connection

Sub DBConnectionAccess()

    If CBool(objConn.State And adStateOpen) Then objConn.Close

    Dim strPathToDB As String
                
    On Error GoTo ErrHandler:
            
    'open connection to SQL Server database
    objConn.Open "Provider=SQLNCLI11;Server=(local);Database=OnlineCollaboration; Trusted_Connection=yes;"


    'MsgBox "Connection is Ok"
    
    Exit Sub
    
ErrHandler:
    ' error handling code
    MsgBox "No connection!" & vbCrLf & "Check whether you have the correct path."

End Sub

Sub DBConnectionClose()

    If CBool(objConn.State And adStateOpen) Then objConn.Close
    Set objConn = Nothing

End Sub

Public objConn As New ADODB.Connection

Sub DBConnectionAccess()

    If CBool(objConn.State And adStateOpen) Then objConn.Close

    Dim strPathToDB As String
                
    On Error GoTo ErrHandler:
            
    'open connection to SQL Server database

MODIFY THIS
    objConn.Open "Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Uid=myUsername;
Pwd=myPassword;"

    'MsgBox "Connection is Ok"
    
    Exit Sub
    
ErrHandler:
    ' error handling code
    MsgBox "No connection!" & vbCrLf & "Check whether you have the correct path."

End Sub

Sub DBConnectionClose()

    If CBool(objConn.State And adStateOpen) Then objConn.Close
    Set objConn = Nothing

End Sub

Connection to SQL Server on Newtek : ‘ijamrock_admin’ IP: 216.119.113.102

Sub DBConnectionSQL()

    If CBool(objConn.State And adStateOpen) Then objConn.Close

    Dim szConnect As String

    szConnect = "Provider=sqloledb; Data Source=216.119.113.102;Network Library=DBMSSOCN; Initial Catalog=ijamrock_beatlesjam;User ID=ijamrock_admin;Password=starples99@;"
    
    objConn.CommandTimeout = 120
    objConn.ConnectionString = szConnect
    objConn.Open

End Sub