VBA FOR SQL CONNECTION IN EXCEL

• SQL FOR REFERENCES - THAM KHẢO CÁC CÂU LỆNH SQL TẠI ĐÂY
• KHÁI NIỆM SQL VÀ CÁCH KẾT NỐI TRONG VBA

 

SQL là viết tắt của Structured Query Language, nghĩa là ngôn ngữ truy vấn dữ
liệu có cấu trúc , là một ngôn ngữ máy tính phổ biến để tạo, sửa, xoá hoặc lấy dữ
liệu từ một hệ quản trị dữ liệu
                                      Dưới đây là kết quả của 1 câu lệnh SQL

---

Muốn sử đụng được SQL chúng ta cần kết nối cơ sở dữ liệu? 

* Đầu tiên các bạn vào trang web sau: https://www.connectionstrings.com/
* Ở đây chúng ta sử dụng kiểu kết nối sau nhé.
Microsoft ACE OLEDB 12.0 connection strings - trong phần trang web này có nói rất
rõ về phần kết nối của excel tới sql 
 * Để thực hiện kết nối dữ liệu trong Excel ta thiết lập kết nối ADODB connection như sau

Ấn Alt+F11: mở cửa sổ VBA==> Vào Tools ==> References (Chọn thư viện Recordset để kết nối)

---

 

Nội dung	Kết nối Early Binding	Kết nối Late Binding
Code VBA	Sub KetnoiEarlyBinding() 'Khai bao biến    Dim cnn As ADODB.Connection     Dim lrs As ADODB.Recordset     Dim sqlQuery As String    Dim dc As Long 'Thiết lập kết nối ADODB bằng lệnh New    Set cnn = New ADODB.Connection Set lrs = New ADODB.Recordset          'Tạo phiên bản kết nối của Microsoft cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";" 'Mở kết nối    cnn.Open  lrs.Open sqlQuery, cnn 'Xóa nội dung trong sheet 1 từ A1 đến dòng cuối dc = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row Sheet1.Range("A1:X" & dc).ClearContents  'Sử dụng câu truy vấn SQL: Chọn tất cả các cột từ bảng dữ liệu có tên Customers sqlQuery = " SELECT * FROM Customers" 'Thực thi lệnh SQL Sheet1.Range("A1").CopyFromRecordset lrs 'Đóng kết nối    lrs.Close    cnn.Close End Sub	Sub KetnoiLateBinding() 'Khai báo biến    Dim cnn As Object    Dim lrs As Object    Dim sqlQuery As String    Dim dc As Long    'Thiết lập kết nối ADODB bằng lệnh tạo đối tượng Object    Set cnn = CreateObject("ADODB.Connection")    Set lrs = CreateObject("ADODB.Recordset") 'Tạo phiên bản kết nối của Microsoft    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";" 'Mở kết nối    cnn.Open  lrs.Open sqlQuery, cnn 'Xóa nội dung trong sheet 1 từ A1 đến dòng cuối dc = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row Sheet1.Range("A1:X" & dc).ClearContents  'Sử dụng câu truy vấn SQL: Chọn tất cả các cột từ bảng dữ liệu có tên Customers   sqlQuery = " SELECT * FROM Customers"   'Thực thi lệnh SQL Sheet1.Range("A1").CopyFromRecordset lrs 'Đóng kết nối    lrs.Close    cnn.Close End Sub
Ưu điểm	Là phương pháp có hướng dẫn khi viết hàm	Khi chạy sang máy tính khác không cần phải tích chọn thư viện, vẫn hoạt động bình thường
Nhược điểm	Khi chạy sang máy tính khác bắt buộc phải vào  Tool→References→Chọn Microsoft ActiveX Data Object Reconrdset 2.8	Không có hướng dẫn khi viết hàm

---

'1. Ham Kiem tra  duong dan duoi file,
'tach chuoi duong dan sau dau "." la cac duoi file Excel va Access nhu xls, xlsx, xlsm,xlsb, mdb, accdb con lai la tra ve file SQL
Function CheckPath(s As String) As String
    Dim kq As String, GetName As String
    GetName = Split(s, ".")(UBound(Split(s, ".")))
    GetName = LCase(GetName)
    If (GetName = "xls" Or GetName = "xlsx" Or GetName = "xlsm" Or GetName = "xlsb") Then
        kq = "EXCEL"
    ElseIf (GetName = "mdb" Or GetName = "accdb") Then
        kq = "ACCESS"
    Else
        kq = "SQLSEVER"
    End If
    CheckPath = kq
End Function

---

 

Option Explicit
'Hien ket qua du lieu

Sub REPORTS_SQL()
'Khai bao bien - Ket noi LateBinding
    Dim Cnn As Object
    Dim lrs As Object
    Dim SQLQuery As String, kq As Boolean
    Dim dc As Long, icols As Long
    Dim path As String, Pasword As String
    
'Xoa du lieu trong bang Ket Qua tu dong A3 den AO dong cuoi
    
    dc = Sheets("REPORTS").Range("A" & Sheets("REPORTS").Rows.Count).End(xlUp).Row
    Sheets("REPORTS").Range("A3:AO" & dc).ClearContents
    
 ' Thiet lap ADODB  voi ket noi LateBinding
    
    Set Cnn = CreateObject("ADODB.Connection")
    Set lrs = CreateObject("ADODB.Recordset")
    
'kiem tra ket noi (xem them ham CheckPath)
' Neu duong dan la file .xls, .xlsm, .xlsb, .xlsx ==> CheckPath tra ve file Excel va ket noi theo phien ban OLEDB.12.0 hoac OLEDB.4.0
' Neu duong dan la file .mdb, .accdb ==> CheckPath tra ve file Access (bao gom mat khau neu co)
' Con lai CheckPath tra ve file SQL

' xac dinh ten duong dan
 path = ActiveWorkbook.FullName
' kiem tra ket noi tren 3 loai file Excel, Access, SQL
 If (CheckPath(path) = "EXCEL") Then
        If Val(Application.Version) > 12 Then
            Cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=0"";"
        Else
            Cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=0"";"
        End If
    ElseIf (CheckPath(path) = "ACCESS") Then
        If Val(Application.Version) > 12 Then
            Cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Persist Security Info=False; Jet OLEDB:Database Password=" & Pasword & ";"
        Else
            Cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Persist Security Info=False; Jet OLEDB:Database Password=" & Pasword & ";"
        End If
    Else
            Cnn.ConnectionString = path
    End If
    'Mo ket noi
    Cnn.Open
    'Cau lenh SQL trong sheet SQL tai o A1
    SQLQuery = Sheets("SQL").Range("A1").Value
     ' Mo ket noi cau lenh SQL
     lrs.Open SQLQuery, Cnn
 
'Tao tieu de cho tung cot muon lay du lieu tu Query
    For icols = 0 To lrs.Fields.Count - 1
        Sheets("REPORTS").Cells(3, icols + 1).Value = lrs.Fields(icols).Name
    Next

  'Ghi lai du lieu bat dau tu vung ô A4
    Sheets("REPORTS").Range("A4").CopyFromRecordset lrs
  'Ghi lai cac cau lenh SQL sang sheet "Record_SQL"
  Call Record_SQL_ThemLenh
  
  'Dong ket noi
    lrs.Close: Set lrs = Nothing
    Cnn.Close: Set Cnn = Nothing
    
    'Chuyen sang file ket qua
 Call sheet_select(Sheets("REPORTS"))
  
End Sub