QUERY MS SQL SERVER WITH EXCEL VBA

'BEFORE WE CAN START YOU'LL NEED TO ADD A REFERENCE TO YOUR VBA PROJECT

'MICROSOFT ACTIVEX DATA OBJECTS X.X LIBRARY

Option Explicit

Private CN As ADODB.Connection

Function Connect(Server As String, Database As String) As Boolean
    Set CN = New ADODB.Connection
    On Error Resume Next
    With CN
        .ConnectionString = "Provider=SQLOLEDB.1;" & _
            "Integrated Security=SSPI;" & _
            "Server=" & Server & ";" & _
            "Database=" & Database & ";"
        .Open
    End With
    If CN.State = 0 Then
        Connect = False
    Else
        Connect = True
    End If
End Function

Function Query(SQL As String)
    Dim RS As ADODB.Recordset
    Dim Field As ADODB.Field
    Dim Col As Long
    Set RS = New ADODB.Recordset
    RS.Open SQL, CN, adOpenStatic, adLockReadOnly, adCmdText
    If RS.State Then
        Col = 1
        For Each Field In RS.Fields
            Cells(1, Col) = Field.Name
            Col = Col + 1
        Next Field
        Cells(1, 1).CopyFromRecordset RS
        Set RS = Nothing
    End If
End Function

Function Disconnect()
    CN.Close
End Function

Sub vmkSQL()
    Dim SQL As String
    Dim Connected As Boolean
    Connected = Connect(".\sqlexpress", "TNNT")
    SQL = "SELECT * FROM HANGHOA"
    If Connected Then
        Call Query(SQL)
        Call Disconnect
    Else
        MsgBox "COULD NOT CONNECT"
    End If
End Sub