Export MS SQL data to/from Excel using VBA (free coding attached) | Session 1

A complete video on VBA Coding for extraction/updating data SQL Server to Excel sheet..

We are Consultant and created channel to help the professionals those are facing critical issues. Our efforts to provide best and quick solutions to the viewer.

Subscribe this channel to get new videos alert.

Code:

------------General Section-------------------------
Public fMyConn As ADODB.Connection
'Public MyRec As ADODB.Command
Public strSQL, sSQL As String

------------Connection Function------------------------
Function GetConn() As ADODB.Connection
On Error Resume Next

Static myConn As ADODB.Connection

If myConn Is Nothing Or myConn.Status = 0 Then

Set myConn = New ADODB.Connection
myConn.ConnectionString = "Provider=SQLOLEDB;Data Source=NAZ-DELL\SQLEXPRESS;Trusted_connection=yes;"
myConn.Open

End If
Set GetConn = myConn

End Function

Sub CloseConn()

mcnn.Close
Set mcnn = Nothing

End Sub
------------Import Data Procedure-------------------------

Public Sub PullData()

Set MyRecordset = New ADODB.Recordset

'Open Connection'
Set fMyConn = GetConn

'Set and Excecute SQL Command'
strSQL = "SELECT * FROM dConn_V where Conti='Yes'"

'Open Recordset'
Set MyRecordset.ActiveConnection = fMyConn
MyRecordset.Open strSQL

'Copy Data to Excel'
Application.Sheets("Cust_Detail").Range("A5").CopyFromRecordset (MyRecordset)

fMyConn.Close

End Sub
------------Clear Data Procedure-------------------------

Public Sub CleanData()
Cells.ClearContents
End Sub

------------Update Procedure-------------------------
Public Sub UpdateData()
'MsgBox (Application.ActiveSheet.Cells(Application.ActiveCell.Row, 2))
Dim sSQL As String

Set fMyConn = GetConn
sSQL = "UPDATE dConn_V Set customerContact=" & Application.ActiveCell.Value & " where OrderNumber =" & "'" & Application.ActiveSheet.Cells(Application.ActiveCell.Row, 2) & "'"

fMyConn.Execute sSQL
MsgBox "Updated: " & sSQL

End Sub

-----------------------------------------------------------------

Private Sub b_DataClear_Click()
Call CleanData
End Sub

Private Sub B_Update_Click()
Call UpdateData
End Sub

Private Sub PullData_B_Click()
Call PullData
End Sub