Using Excel Macros (VBA) you can connect to any Databases like SQL, Oracle or Access DB. In this Article you will learn, how you can do a connection with a SQL Server. SQL Connection can be of different types, like using windows authentication or by SQL Server Authentication.
Before running the below code, you need to Add reference for ADODB Connection. Follow below steps to Add Reference:

1. Go to VB Editor Screen (Alt+F11)
2. Tools –> References…
3. From the List of Available References Select Microsoft ActiveX Data Objects 2.0 Library You can select 2.0 Version or any other higher version of this reference.
4. Click OK

 
Using SQL Server Authentication || Using Windows Authentication

Using SQL Server Authentication:


    Sub SQL_Connection()  
      
    Dim con As ADODB.Connection  
    Dim rs As ADODB.Recordset  
    Dim query As String  
    Set con = New ADODB.Connection  
    Set rs = New ADODB.Recordset  
      
    '---- Replace below highlighted names with the corresponding values  
      
    strCon = "Provider=SQLOLEDB; " & _
            "Data Source="your ServerName"; " & _
            "Initial Catalog=database name;" & _
            "User ID=uid; Password=pwd; Trusted_Connection=yes"  
      
    '---  Open   the above connection string.  
      
    con.Open (strCon)  
      
    '---  Now connection is open and you can use queries to execute them.
    '--- It will be open till you close the connection  
      
    End Sub  

Using Windows Authentication:


    Sub SQL_Connection()  
      
    Dim con As ADODB.Connection  
    Dim rs As ADODB.Recordset  
    Dim query As String  
    Set con = New ADODB.Connection  
    Set rs = New ADODB.Recordset  
      
    '---- Replace below highlighted names with the corresponding values  
      
    strCon = "Provider=SQLOLEDB; " & _
            "Data Source="your ServerName"; " & _
            "Initial Catalog=database name;" & _
            "Integrated Security=SSPI"   
      
    '---  Open   the above connection string.  
      
    con.Open (strCon)  
      
    '---  Now connection is open and you can use queries to execute them.
    '---  It will be open till you close the connection  
      
    End Sub  

Read this Also: