Get Data from MSSQL (VBA)
Please using this code you need to Active Microsoft ActiveX Object Library, to Active MS ActiveX Object Library you in Developer Tool you just to to Reference then search Microsoft ActiveX Object 6.2 Library
you can watch video to learn this
Get Data (Simple Connection with SQL)
Sub SQLDB()
Dim Conn As ADODB.Connection
Dim ConnSet As ADODB.Recordset
Dim ConnQry As String
Dim ws As Worksheet
Set Conn = New ADODB.Connection
Set ConnSet = New ADODB.Recordset
Set ws = ThisWorkbook.Sheets("Raw")
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
ws.Range("A1").CurrentRegion.Delete
ConnQry = "Provider = SQLOLEDB; Data Source = DOK_ACADEMY\SQLEXPRESS03; Initial Catelog = DOKLtd;User ID = dokafzal ; Password = pass"
Conn.Open ConnQry
sqlqry = "select * from [Sale Call Report]"
Conn.CommandTimeout = 0
Set ConnSet = Conn.Execute(sqlqry)
For i = 0 To ConnSet.Fields.Count - 1
ws.Range("A1").Offset(, i) = ConnSet.Fields(i).Name
Next
ws.Range("A2").CopyFromRecordset ConnSet
ws.Activate
Conn.Close
End Sub
Get Data from MYSQL (VBA)
Please using this code you need to Active Microsoft ActiveX Object Library, to Active MS ActiveX Object Library you in Developer Tool you just to to Reference then search Microsoft ActiveX Object 6.2 Library
Please insure that you have installed Mysql ODBC unicode Driver,
you can watch video to learn this
Sub DatafromMySQL()
Dim MyConn As ADODB.Connection
Dim Myset As ADODB.Recordset
Dim WS As Worksheet
Set MyConn = New ADODB.Connection
Set Myset = New ADODB.Recordset
Set WS = ThisWorkbook.Sheets("Raw")
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With
MyConn.Open "Driver= {MYSQL ODBC 8.1 Unicode Driver}; Server = localhost; Database= dokltd; user =root; Password = myPass;"
sqlcode = "select * from salescalls limit 100;"
MyConn.CommandTimeout = 0
Set Myset = MyConn.Execute(sqlcode)
For i = 0 To Myset.Fields.Count - 1
WS.Range("A1").Offset(, i) = Myset.Fields(i).Name
Next
WS.Range("A2").CopyFromRecordset Myset
WS.Activate
MyConn.Close
End Sub