Đây là một ví dụ ứng dụng ADODB với câu lệnh SQL lồng (dùng Union All). Các anh chị và các bạn tham khảo code bên dưới và xem file đính kèm. Đã test thử với cấu trúc DATA như file đính kèm, khoản 10.000 record lọc chưa tới 15 giây.
[highlight=vb]
Sub GetDetail()
'---------------------------------------------------------------------------
'FName: Database Name including Path (ex: "D:\MyDatabase\MyFile.xls")
'cnnEx: Connection string into external database
'recEx: Recordset
'mySQL: SQL statement
'---------------------------------------------------------------------------
Dim FName As String, mySQL_Dr As String, mySQL_Cr As String, mySQLDetail As String
Dim mPeriod As Long, mAcctID As String
Dim cnnEx As New ADODB.Connection
Dim RecEx As New ADODB.Recordset
'---------------------------------------------------------------------------
FName = ThisWorkbook.FullName
mPeriod = Sheets("Detail").[B6].Value 'Ky bao cao can loc
mAcctID = "'" & Sheets("Detail").[B5].Text & "%'" 'Tai khoan can loc, co the loc theo do dai ky tu tai khoan
'Tao Ket noi voi file du lieu nguon:
cnnEx.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FName & _
";Persist Security Info=False; Extended Properties=Excel 8.0;"
'---------------------------------------------------------------------------
mySQL_Dr = "Select a.TKNo as TaiKhoan, a.SoCtu, a.NgayCtu, a.DienGiai, a.TKCo as TKDoiUng, a.Thanhtien as GhiNo, 0 as GhiCo, 0 as REF FROM [DATA$] AS a" & Chr(13)
mySQL_Dr = mySQL_Dr & "WHERE a.Period= " & mPeriod & " AND a.TKNo LIKE " & mAcctID
mySQL_Cr = "Select a.TKCo as TaiKhoan, a.SoCtu, a.NgayCtu, a.DienGiai, a.TKNo as TKDoiUng, 0 as GhiNo, a.ThanhTien as GhiCo, 1 as REF FROM [DATA$] AS a" & Chr(13)
mySQL_Cr = mySQL_Cr & "WHERE a.Period= " & mPeriod & " AND a.TKCo LIKE " & mAcctID
'---------------------------------------------------------------------------
mySQLDetail = mySQL_Dr & Chr(13) & "Union all" & Chr(13) & mySQL_Cr
a = MsgBox("You will get the data by runing this SQL statement: " & Chr(13) & "----------" & Chr(13) & mySQLDetail & Chr(13) & "----------", vbInformation + vbYesNo, "Info")
If a = vbYes Then
RecEx.Open mySQLDetail, cnnEx, adOpenKeyset, adLockOptimistic
'---------------------------------------------------------------------------
'Neu query khong tim thay du lieu thi thoat ra, khong xoa du lieu cu:
If RecEx.EOF Then
a = MsgBox("Hic!No record found!", vbCritical + vbOKOnly, "Info")
Exit Sub
Else
Sheets("Detail").[9:65536].Delete
Sheets("DETAIL").[A9].CopyFromRecordset RecEx
'Refresh lai hai bien cnEx va RecEx:
RecEx.Close: Set RecEx = Nothing
cnnEx.Close: Set cnEx = Nothing
b = MsgBox("Data has already been exported to sheet(Detail)!", vbInformation + vbOKOnly, "Info")
Sheets("DETAIL").Activate
End If
Else
c = MsgBox("Cancel query by user!Hehe...!See you later!
--> ca_dafi", vbCritical + vbOKOnly, "Info")
Exit Sub
End If
End Sub
[/highlight]
File PDF đính kèm hướng dẫn sơ lược cách sử dụng ADODB một cách đơn giản nhất! Mời mọi người tham khảo!
(
