1)Windows Authentication

2)SQL Server Authentication

Windows Authentication - Connection String

SQL OLE DB:

Provider=SQLOLEDB;:= Mention the OLE DB provider in connection string

Data Source\Server Name:

Name of the computer while installing\Name of server

Ex: Data Source=PavanSys-PC\PAVAN;"

PavanSys is the system name and Pavan is SQL Server Name, which was mentioned while installing sql server

Database Name:

Initial Catalog: Denotes about DATABASE NAME

In this case ForExcel is the database name

Windows Authentication:

Integrated Security=SSPI: denotes about Windows authentication

Connection string for windows Authentication:

Dim str As String
str = "Provider=SQLOLEDB;Data Source=PavanSys-PC\PAVAN;" & _
"Initial Catalog=ForExcel;Integrated Security=SSPI"

SQL Server Authentication - Connection String

Dim str As String
str = "Provider=SQLOLEDB;" & _
"Data Source=SRIRAM-PC\PAVAN;" & _
"Initial Catalog=Forexcel;" & _
"User ID=xxxx;Password=xxxxx;"

Once we connected to database We can retrieve data based on two methods

Query Tables
RecordSet

To activate the ADODB we need to activate below mentioned weblink

SQL Server OLE DB CONNECTION

Sub RetrieveData_From_SQLDatabase_To_Excel()
'Step1: # Activate Microsoft ActiveX Dataobjects 6.1 Liabriary
'Step2: # Define variable for ADODB connection
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
'Step3: # Define variable for Record Set
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
'Step4: # Create a variable to accomplish the connection with SQL
Dim str As String
str = "Provider=SQLOLEDB;Data Source=SRIRAM-PC\PAVAN;" & _
"Initial Catalog=ForExcel;Integrated Security=SSPI"
'Step5: # Connect to SQL
cn.Open str
'Step6: # Open table(record set), Apply SQL Syntax
rs.Open _
Source:="Select P.Period, P.Item, P.Purchase_Qty, P.Purchase_Price, S.Sales_Qty, S.Sales_Price from Purchases_Q1 as P Right outer join Sales_Q1 as S on P.Item = S.Item", _
ActiveConnection:=cn, _
CursorType:=adOpenDynamic, _
LockType:=adLockReadOnly, _
Options:=adCmdText
'Step7: # Create new workbook to display output in new workbook
Dim Wkb As Workbook
Set Wkb = Workbooks.Add
'Step8: # Apply Query Tables properties\Methods
With ActiveSheet.QueryTables _
.Add(Connection:=rs, _
Destination:=Wkb.Sheets("sheet1").Range("D5"))
.FieldNames = True ' True = dispalys column headings
.RowNumbers = False 'Create Row Numbers as 1st column
.FillAdjacentFormulas = False
.PreserveFormatting = True ' Apply Previous cell formatting for current cell
.RefreshOnFileOpen = False ' Refresh the database when opened workbook
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells ' Refresh styles in 3 ways
.RefreshPeriod = 0 ' To mention refresh period
.AdjustColumnWidth = True ' To autofit column width
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
.EnableEditing = True 'True = user can edit the query table, false doesn't edit
.SavePassword = True
.SaveData = True
End With
Call Formatting_Output
End Sub

Designing Template - SQL to Excel Connection

Through this method i would like to share how to connect SQL Database and retrieve records., through QUERY TABLES method

Joining all the columns from multiple tabels

This video enunciates how to JOIN multiple tables

Download the template from the fist video