Dear Friends,

Using Excel Macros (VBA) you can connect to Databases like SQL, Oracle or Access DB. In this Article, I am going to teach you, how you can do a connection with a SQL Server.
SQL databases has mainly two type of authentication :
1. Windows Authentication
2. SQL Server authentication.
Here in this article, I will share VBA code for both type of connection – Windows authentication and SQL Server Authentication.
Before you start running the below code, make sure that reference for ADODB Connection is added in your Excel Workbook.
If you do not know how to add references to your excel workbook, refer the below steps:

How to add ADODB Connection references in Excel

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

SQL Connection String – Using SQL Server Authentication
SQL Connection String – 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:

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!