Public con As New ADODB.Connection, sh As Worksheet

Public Function Establish_Connection()
Dim ConnectionString As String
ConnectionString = "driver={Sql Server};Server=PAVAN-PC\PAVAN;Database = ExcelToSQL;"
' "User ID=pavan;Password=abcd;"
con.Open ConnectionString
End Function

Public Function Close_the_Connection()
con.Close
End Function

Function SelectWorksheet()
Set sh = ThisWorkbook.Sheets("VBA_SQL")
sh.Activate
End Function

Sub ConnectToDatabase_CreateTable()
On Error GoTo ErrorOccured
Establish_Connection
con.Execute "Create table Company (Item varchar (15),Sales int, Location varchar(15))"
If ErrorOccured > 0 Then
ErrorOccured:
MsgBox Err.Description & vbNewLine & "Error Happend"
Else:
MsgBox ("Hi Table Created")
End If
Close_the_Connection
End Sub

Sub Droptable()
On Error GoTo ErrorHappend
Establish_Connection
con.Execute "drop table Company"
If ErrorHappend > 0 Then
ErrorHappend:
MsgBox Err.Description & vbNewLine & "Not Deleted the Table"
Else:
MsgBox ("Hi Table deleted")
End If
Close_the_Connection
End Sub

Sub InsertExistingValuesIntoTable()
Establish_Connection
SelectWorksheet
Lastrow = sh.Range("A2").End(xlDown).Row
For r = 2 To Lastrow
Itemvalue = sh.Range("A" & r).Value
Salesvalue = sh.Range("B" & r).Value
Locationvalue = sh.Range("C" & r).Value
'con.Execute "insert into Company values('Almond',300, 'North')"
con.Execute "insert into Company (Item, Sales,Location) values ('" & Itemvalue & "', '" & Salesvalue & "','" & Locationvalue & "' )"
Next
Close_the_Connection
MsgBox ("Hi Inserted the values into the table")
End Sub

Sub RetrieveDataThroughSelectQuery()
Establish_Connection
SelectWorksheet
sh.Range("F8").CurrentRegion.Clear
Sql = sh.Range("I3").Value
Set rs = New ADODB.Recordset
rs.Open Sql, con
sh.Range("F8").CopyFromRecordset rs
Close_the_Connection
MsgBox ("Hi Data Retrieved")
End Sub

Sub Updatedata()
Establish_Connection
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandText = "Update Company set Sales = 400"
cmd.Execute
Close_the_Connection
MsgBox ("Hi Data Updated")
End Sub

Sub DeleteTheRecords()
Establish_Connection
con.Execute "Delete from Company where Sales >= 65"
Close_the_Connection
MsgBox ("Hi Deleted the records")
End Sub