Cách truy vấn dữ liệu trên cùng file không mở kết nối (ADODB.Connection)

HeSanbi · 17/1/20

Huyyeu99999 đã viết:
Chìa khóa XlRangeValueDataType
xlRangeValueXMLS Lansheet

Bài đã được tự động gộp: 16/1/20

Mã:

Sub Copy() Dim xml As string xml = Application.ActiveSheet.Range("A1:D20").Value(XlRangeValueDataType.xlRangeValueXMLSpreadsheet) Application.ActiveSheet.Range("G1:j20").Value(XlRangeValueDataType.xlRangeValueXMLSpreadsheet) = xml End Sub

Có xml này anh đưa vào record

----------------------
Bạn chớ nói Vui mà không thực hành.

Phương thức chuyển Giá trị Range thành XML (xlRangeValueXMLSpreadsheet) là cả một vấn đề. Thì làm sao nói đến được đưa vào Recordset ADODB để xử lý.
Và làm sao cải thiện được tốc độ xử lý dữ liệu.
Xử lý sang XML với 1 triệu dòng là đã "toi Excel" rồi.

Bài viết này đang đề cập đến truy vấn dữ liệu. Chính xác hơn là tận dụng tối đa thư viện ADODB.

Với cách bạn nói là Chuyển đổi vùng lưu trữ dữ liệu thành dạng lưu trữ mới phù hợp với Recordset ADODB có thể hiểu được và xử lý được.

Khác nào bạn đang truy vấn một file dữ liệu dạng XML, nhưng ở đây phải bỏ thêm bước chuyển đổi.

Huyyeu99999 · 17/1/20

HeSanbi đã viết:
----------------------
Bạn chớ nói Vui mà không thực hành.

Phương thức chuyển Giá trị Range thành XML (xlRangeValueXMLSpreadsheet) là cả một vấn đề. Thì làm sao nói đến được đưa vào Recordset ADODB để xử lý.
Và làm sao cải thiện được tốc độ xử lý dữ liệu.
Xử lý sang XML với 1 triệu dòng là đã "toi Excel" rồi.

Bài viết này đang đề cập đến truy vấn dữ liệu. Chính xác hơn là tận dụng tối đa thư viện ADODB.

Với cách bạn nói là Chuyển đổi vùng lưu trữ dữ liệu thành dạng lưu trữ mới phù hợp với Recordset ADODB có thể hiểu được và xử lý được.

mình chưa nói tới so sánh tốc độ, bài này là nói không mở kết nối mà, đúng như yêu cầu của bài chứ đâu có lệch đâu. mình chưa thử 1.000.000 nên không biết mà chắc chắn là nó chậm hơn lấy vào Arr rồi. Máy chạy hông nổi :unknw:

Bài đã được tự động gộp: 17/1/20

HeSanbi đã viết:
----------------------
Bạn chớ nói Vui mà không thực hành.

Phương thức chuyển Giá trị Range thành XML (xlRangeValueXMLSpreadsheet) là cả một vấn đề. Thì làm sao nói đến được đưa vào Recordset ADODB để xử lý.
Và làm sao cải thiện được tốc độ xử lý dữ liệu.
Xử lý sang XML với 1 triệu dòng là đã "toi Excel" rồi.

Bài viết này đang đề cập đến truy vấn dữ liệu. Chính xác hơn là tận dụng tối đa thư viện ADODB.

Với cách bạn nói là Chuyển đổi vùng lưu trữ dữ liệu thành dạng lưu trữ mới phù hợp với Recordset ADODB có thể hiểu được và xử lý được.

Khác nào bạn đang truy vấn một file dữ liệu dạng XML, nhưng ở đây phải bỏ thêm bước chuyển đổi.

theo như bạn có cách nào hay không vẫn giữ định dạng của sheet

HeSanbi · 17/1/20

Huyyeu99999 đã viết:
mình chưa nói tới so sánh tốc độ, bài này là nói không mở kết nối mà, đúng như yêu cầu của bài chứ đâu có lệch đâu. mình chưa thử 1.000.000 nên không biết

Chắc bạn hiểu không hết đoạn "truy vấn dữ liệu", nghĩa của nó đã bao hàm tốc độ rồi bạn.
"ADODB...." Thư viện này cũng nói lên tốc độ xử lý dữ liệu.

Có thể hiểu như thế này:
Excel đã sử dụng ngôn ngữ đánh dấu XML hiển thị thành bảng -> ADODB có thể truy vấn ngay lập tức.

Với cách làm của bạn:
Excel sử dụng ngôn ngữ đánh dấu XML -> Chuyển mảng sang XML (Phí bước này) -> ADODB Recordset

Bài đã được tự động gộp: 17/1/20

Huyyeu99999 đã viết:
theo như bạn có cách nào hay không vẫn giữ định dạng của sheet

Đọc lại bài trước #60 của tôi bạn sẽ thấy cách vận dụng.

Huyyeu99999 · 17/1/20

HeSanbi đã viết:
Chắc bạn hiểu không hết đoạn "truy vấn dữ liệu", nghĩa của nó đã bao hàm tốc độ rồi bạn.
"ADODB...." Thư viện này cũng nói lên tốc độ xử lý dữ liệu.

Có thể hiểu như thế này:
Excel đã sử dụng ngôn ngữ đánh dấu XML hiển thị thành bảng -> ADODB có thể truy vấn ngay lập tức.

Với cách làm của bạn:
Excel sử dụng ngôn ngữ đánh dấu XML -> Chuyển sang XML (Phí bước này) -> ADODB Recordset

ý của mình là muốn giữ nguyên định dạng của sheet kìa chứ không phải là chỉ đưa giá trị vào Recordset. vậy từ excel có cách nào đọc trực tiếp xml không, phai nguyên định dạng format, mau chu, công thức.....?

Hai Lúa Miền Tây · 17/1/20

HeSanbi đã viết:
Bài viết của anh @Hai Lúa Miền Tây hoàn toàn có vấn đề. Anh đọc qua bài viết dưới đây để hiểu vì sao như vậy.

----------------------
Không biết mắt có đọc nhằm không. Đọc 2-3 lần
Đọc bài viết và đọc code là "MatID", khi vào đọc trong Data thì là "MaID"

Đó là ý đồ của tôi, cái cột tiêu đề trong sheet 1 không dính gì đến code, vùng dữ liệu tôi đưa vào cũng không lấy dòng tiêu đề đó. Bạn có thể tạo cột tiêu đề theo ý trong bảng, tên cột không nhất thiết phải giống tên cột dữ liệu nguồn (MatID và MaID) và ta có thể bỏ bớt cột dữ liệu không cần thiết (Remark), cũng có thể chọn thứ tự cột theo ý. Sau khi tạo xong bảng rỗng dữ liệu sau đó ta đưa dữ liệu vào.

HeSanbi đã viết:
Sao anh @Hai Lúa Miền Tây lại để LBound của Hàng sang Cột được, hơi khó hiểu.

Nhưng kết quả bạn thấy như thế nào?

Hai Lúa Miền Tây · 17/1/20

Huyyeu99999 đã viết:
ý của mình là muốn giữ nguyên định dạng của sheet kìa chứ không phải là chỉ đưa giá trị vào Recordset. vậy từ excel có cách nào đọc trực tiếp xml không, phai nguyên định dạng format, mau chu, công thức.....?

Xử lý cách này chỉ với dữ liệu nhỏ, riêng lẻ thôi bạn. Còn xử lý theo cách của tôi có thể dùng với dữ liệu lớn, kết quả cho ra không giữ lại định dạng hay công thức.

Huyyeu99999 · 17/1/20

HeSanbi · 17/1/20

Huyyeu99999 đã viết:
ý của mình là muốn giữ nguyên định dạng của sheet kìa chứ không phải là chỉ đưa giá trị vào Recordset. vậy từ excel có cách nào đọc trực tiếp xml không, phai nguyên định dạng format, mau chu, công thức.....?

Copy file sang file mới bung file thành một folder bằng Zip7z, sau đó truy cập file XML trong thư mục worksheets, File XML chứa toàn bộ định dạng + công thức + giá trị, tệp sẽ rất nặng (gấp 10 đến vài chục lần) khiến máy tính xử lý rất nhọc nhằn. Nếu bạn biết thư viện nào hỗ trợ XML tốt thì có thể Vận dụng. Với VBA thì không thể xử lý dữ liệu lớn được.

--------------------------------------
@Hai Lúa Miền Tây
Anh có vẻ vui tính đây. LBound(Arr) và LBound(Arr, 2) của mảng khai báo kia luôn luôn là 1, chắc chắn là không sai rồi, nhưng ai lại viết như vậy.

Và vẫn chưa hiểu nổi vì sao anh lại cho rằng sử dụng vòng lặp sẽ nhanh hơn truy vấn trực tiếp. Quá đổi rườm rà và chậm.

Cách dùng vòng lặp Trừ khi ta cần phải xử lý dữ liệu mảng trước khi đưa vào Recordset.
Hoặc là Mảng thuần trong VBA nên cần sử dụng vòng lặp.

Câu nói: "truy vấn dữ liệu bằng ADO trên cùng file thì ta vẫn phải mở kết nối đến chính file thực thi đó"
Sai hoàn toàn. (Đoạn này đã được nói ở #60)

Dễ gây hiểu nhầm cho người học và vận dụng ADODB.

Code dưới đây phản biện cho câu trên và câu "Điều này làm giảm đi tốc độ khi chạy code".

Driver Microsoft....OLEDB... được sinh ra để tối ưu kết nối cơ sở dữ liệu sử dụng cho Office. Ở trường hợp này đã dùng Vòng lặp thì Tốc độ chắc chắn sẽ chậm hơn truy vấn trực tiếp.

-----

PHP:

Sub testRecordset()
  Dim T#: T = Timer
  Static RCT As Object
  If RCT Is Nothing Then Set RCT = VBA.CreateObject("ADODB.Recordset")
  RCT.Open "SELECT ID,MaID,Balance FROM [" & ThisWorkbook.Sheets(1).Name & "$] WHERE Balance>0", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;"""
  With ThisWorkbook.Sheets(2)
    .Range("F4:D100000").ClearContents
    .Range("F4").CopyFromRecordset RCT
    RCT.Close
  End With
  Debug.Print Round(Timer - T, 5)
End Sub

------

Và trong code của anh có ăn gian một lượng thời gian ở đoạn này:

------

PHP:

vArray = Sheet1.Range("A2:D10430").Value
thoigian = Timer()

------
Phải như thế này mới phải:
------

PHP:

thoigian = Timer()
vArray = Sheet1.Range("A2:D10430").Value

------
Đoạn này thay cho mở kết nối như anh đã nói, sao lại không tính thời gian, nó cũng được tính là truy vấn dữ liệu.

Huyyeu99999 · 17/1/20

HeSanbi đã viết:
Copy file sang file mới bung file thành một folder bằng Zip7z, sau đó truy cập file XML trong thư mục worksheets, File XML chứa toàn bộ định dạng + công thức + giá trị, tệp sẽ rất nặng (gấp 10 đến vài chục lần) khiến máy tính xử lý rất nhọc nhằn. Nếu bạn biết thư viện nào hỗ trợ XML tốt thì có thể Vận dụng. Với VBA thì không thể xử lý dữ liệu lớn được.

Cảm ơn ban góp ý.
Mình tính lấy xml chuyển qua client để đưa vào sheet ben client.
Khi cell nào ben client thay đổi mình sẽ chuyển ngược về lại sever. Không biết có khả thi không

Hai Lúa Miền Tây · 17/1/20

HeSanbi đã viết:
Anh có vẻ vui tính đây. LBound(Arr) và LBound(Arr, 2) của mảng khai báo kia luôn luôn là 1, chắc chắn là không sai rồi, nhưng ai lại viết như vậy.

Tôi viết rõ ràng là LBound(vArray) To UBound(vArray) chứ không phải như bạn nói ở trên.
LBound(vArray) sẽ là 1 và UBound(vArray) là mấy bạn test lại nhé. Còn việc tôi đưa mảng vào Recordset chính là 1 cách để mọi người có thể tùy biến mà dùng.
Cách của bạn, bạn nghĩ là không kết nối đến file nguồn?

HeSanbi · 17/1/20

Hai Lúa Miền Tây đã viết:
Tôi viết rõ ràng là LBound(vArray) To UBound(vArray) chứ không phải như bạn nói ở trên.
LBound(vArray) sẽ là 1 và UBound(vArray) là mấy bạn test lại nhé. Còn việc tôi đưa mảng vào Recordset chính là 1 cách để mọi người có thể tùy biến mà dùng.
Cách của bạn, bạn nghĩ là không kết nối đến file nguồn?

-------
Rõ là anh không đọc bài viết thì phải bắt đầu từ #60:

Trong code của anh: vArray(i, LBound(vArray)) Và vArray(i, LBound(vArray, 2)) cách viết đúng?

Có ai nói đến đoạn này sai bao giờ: LBound(vArray) To UBound(vArray)

"Cách của bạn, bạn nghĩ là không kết nối đến file nguồn?"

Hài chết đi được, em đã đăng hai bài rồi, có lẽ anh chưa đọc qua.
File Excel đang mở là file thực hiện truy cập chính nó, vì Driver MS OLEDB được mở từ ADODB trên cùng một Process nên nó không cần truy cập hay mở ở đâu nữa, Mà vào ngay dữ liệu đang mở là File chính truy vấn trực tiếp.

Nó đã cùng một tiến trình rồi, thì anh nghĩ nó sẽ mở File ở đâu nữa đây.

"Cùng một Process" có nói ở #60.

Chắc là tham gia nhằm bài viết.

Bài đã được tự động gộp: 17/1/20

Đoạn này trích từ Trang Microsoft:

"Performance. Microsoft Excel is an out-of-process ActiveX server. ADO runs in-process, and saves the overhead of costly out-of-process calls. "
"Hiệu suất . Microsoft Excel là một máy chủ ActiveX out-of-process. ADO chạy trong tiến trình và tiết kiệm chi phí cho các cuộc gọi ngoài quy trình tốn kém. "

https://support.microsoft.com/en-us...-to-use-ado-to-read-and-write-data-in-excel-w

ngovantruong07061994@gmai · 8/4/20

Các tiền bối cho em hỏi chút là nếu tổng hợp dữ liệu từ nhiều file sang 1 file ( coppy dữ liệu thôi không phải chuyển hẳn sheet ) thì sử dụng câu lệnh nào ạ

Ví dụ file 1 có 3 sheet a b c
File 2 có 3 sheet a b1 c1
Giờ em muốn tổng hợp file 1 và file 2 và chỉ tổng hợp 1 sheet a thôi thì làm thế nào ạ, Mong nhận sự chỉ giáo ạ

shnhatha221108 · 11/10/20

ongke0711 đã viết:

Cũng xin góp vui với đề tài này.

Tôi demo một cách dùng khác của Recordset nhưng có tạo Connection.
Theo cách của anh HLMT là tạo một table ảo (ADO recordset) trên bộ nhớ và xử lý Filter, Sort. Cách này theo tôi có một điểm yếu đó là nó sẽ tải toàn bộ dữ liệu lên Recordset (10.000 dòng trong ví dụ này). Điều này sẽ ảnh hưởng đến bộ nhớ rõ rệt nếu số lượng record lên hàng triệu.
Cách của tôi đang làm thì do không rành về mảng nên chỉ thuần tuý dùng Recordset và name range của Excel để gán dữ liệu vào recordset. Dữ liệu được chọn lọc trước khi ghi lên Recordset nên về mặt bộ nhớ sẽ giảm áp lực hơn.
Các bạn tham khảo góp ý nhé.

- Cách làm này phải tốn thêm một bước là tạo cái Name range và cập nhật nó mỗi khi có thay đổi thêm số dòng vào dữ liệu.
- Tôi có viết cái hàm để cập nhật lại name range.

Mã:

Option Explicit

Function createDynamicNamedRange() As Boolean
'--------------------------------------------------------------------------------------
'# Áp dung khi da xac dinh so cot cua range, hàm chi cap nhat dong cho so dòng cua range
'--------------------------------------------------------------------------------------

    Dim sht                     As Worksheet
    Dim lngFirstRowRng          As Long
    Dim lngLastRowRng           As Long
    Dim lngFirstColRng          As Long
    Dim lngLastColRng           As Long
    Dim myDynamicNamedRange     As Range
    Dim sRngName                As String

    Set sht = ThisWorkbook.Worksheets("Sheet1")

    'Khai bao dòng/côt dau tiên cua Range
    lngFirstRowRng = 1
    lngFirstColRng = 1

    'dat ten Name range tuy ý
    sRngName = "TonKhoVT"

    With sht.Cells
        lngLastRowRng = sht.Cells(Rows.Count, "D").End(xlUp).Row
        lngLastColRng = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Set myDynamicNamedRange = .Range(.Cells(lngFirstRowRng, lngFirstColRng), .Cells(lngLastRowRng, lngLastColRng))
    End With

    ThisWorkbook.Names.Add Name:=sRngName, RefersTo:=myDynamicNamedRange

End Function

- Code cho Sub lọc dữ liệu:

Mã:

Sub LocRst()
On Error GoTo LocRst_Err
    Dim oCnn As Object
    Dim oRst As Object
    Dim sRngName As String, fileExcel As String
    Dim s, s1, s2, s3 As String     'Chuoi dieu kien loc
    Dim thoigian As Long

    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1

    Set oCnn = CreateObject("ADODB.Connection")
    Set oRst = CreateObject("ADODB.Recordset")

    thoigian = Timer()

    fileExcel = ThisWorkbook.FullName
    With oCnn
        Select Case CLng(Application.Version)
        Case 11   'Excel 2003
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .ConnectionString = "Data Source=" & fileExcel & ";" & _
                                "Extended Properties=""Excel 8.0;HDR=Yes;"";"
        Case Is >= 12    'Excel 2007 tro lên"
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = "Data Source=" & fileExcel & ";" & _
                                "Extended Properties=""Excel 12.0 Xml;HDR=Yes;"";"
        End Select
        .Open
    End With

    sRngName = "TonKhoVT"

    'Tong hop dieu kien loc
    s = "SELECT * FROM [" & sRngName & "] WHERE 1=1"
    s1 = " AND [" & Sheet3.Range("A1") & "]" & Sheet3.Range("B1")                   'Balance
    s2 = " AND [" & Sheet3.Range("A2") & "] =" & Sheet3.Range("B2")                 'ID
    s3 = " AND [" & Sheet3.Range("A3") & "] ='" & Sheet3.Range("B3") & "'"    'Remark
    If Not IsEmpty(Sheet3.Range("B1")) Then
        s = s & s1
    End If
    If Not IsEmpty(Sheet3.Range("B2")) Then
        s = s & s2
    End If
    If Not IsEmpty(Sheet3.Range("B3")) Then
        s = s & s3
    End If
    s = s & " ORDER BY [ID]"
    'Debug.Print s

    oRst.Open s, oCnn, adOpenStatic, adLockOptimistic, adCmdText

    Sheet3.Range("A7:D11000").ClearContents    'Xoa vung du lieu
    Sheet3.Range("A7").CopyFromRecordset oRst   'Do du lieu tu Recordset da loc xuong sheet
    Sheet3.Range("A5") = "Tong so record tim thay: " & oRst.RecordCount
    MsgBox Timer - thoigian

LocRst_Exit:
    oRst.Close
    Set oRst = Nothing
    If Not oCnn Is Nothing Then
        oCnn.Close
        Set oCnn = Nothing
    End If
    Exit Sub

LocRst_Err:
    Select Case Err.Number
    Case -2147217900
        MsgBox "Sai dieu kien loc du lieu!" & vbNewLine & vbNewLine & Err.Description, vbCritical, "Thông báo"
        Exit Sub
    Case Else
        MsgBox "Loi: " & Err.Number & vbNewLine & "Noi dung loi: " & Err.Description, vbExclamation, "Thông báo"
        Exit Sub
End Select

End Sub

Chào anh..!Nhờ anh chỉ bảo giúp với đoạn code trên để đáp dụng cho một file dữ liệu khác thì tại 3 ô giá trị lọc cần thay đổi điều kiện câu lệnh ra sao.?
Vi dụ em muốn tai Sheet3.Range("B1") = một giá trị cụ thể .

ongke0711 · 11/10/20

shnhatha221108 đã viết:
Chào anh..!Nhờ anh chỉ bảo giúp với đoạn code trên để đáp dụng cho một file dữ liệu khác thì tại 3 ô giá trị lọc cần thay đổi điều kiện câu lệnh ra sao.?
Vi dụ em muốn tai Sheet3.Range("B1") = một giá trị cụ thể .

Bạn chỉ cần thay dấu ">" thành dấu "=".
Ở ô B1 này mục đích là lọc giá trị bao gồm luôn các toán tử <, >, = nên tôi phải thiết kế dạng Text và khi nhập liệu phải thêm toán tử vô và nhập kiểu text.
Vd: bạn muốn tìm chính xác giá trị 150 thì phải gõ "'=150"
Nếu trường hợp của bạn không cần lọc các trường hợp <, > thì tìm câu lệnh SQL và sửa như bên dưới, sau đó nhập số không cần dấu "="

Mã:

s1 = " AND [" & Sheet3.Range("A1") & "]=" & Sheet3.Range("B1")

shnhatha221108 · 12/10/20

ongke0711 đã viết:
Bạn chỉ cần thay dấu ">" thành dấu "=".
Ở ô B1 này mục đích là lọc giá trị bao gồm luôn các toán tử <, >, = nên tôi phải thiết kế dạng Text và khi nhập liệu phải thêm toán tử vô và nhập kiểu text.
Vd: bạn muốn tìm chính xác giá trị 150 thì phải gõ "'=150"
Nếu trường hợp của bạn không cần lọc các trường hợp <, > thì tìm câu lệnh SQL và sửa như bên dưới, sau đó nhập số không cần dấu "="

Gửi anh..Nếu trong đoạn code " AND [" & Sheet3.Range("A1") & "]" & Sheet3.Range("B1") để nguyên bản như của anh thì có thể sử dụng được toán tử so sánh < hoặc > còn = thì báo lỗi(như file anh em đính kèm).Ngược lại khi em sửa " AND [" & Sheet3.Range("A1") & "]=" & Sheet3.Range("B1") thì chỉ sử dụng được giá trị sác đinh đó ...
Nhân đây em muốn nhờ anh giúp hộ.Nếu ở sheet nguồn em không đặt tên bảng muốn sử dụng tên sheet và vùng dữ liệu và trong câu truy vấn kết nôi (HDR=No) để không phải sử dụng tiêu đề mà sử dung theo kiểu (SELECT f1,f2,f3 FROM ............. where f1 =...).Anh kiểm tra file đính kèm giúp em với..

ongke0711 · 12/10/20

shnhatha221108 đã viết:
Gửi anh..Nếu trong đoạn code " AND [" & Sheet3.Range("A1") & "]" & Sheet3.Range("B1") để nguyên bản như của anh thì có thể sử dụng được toán tử so sánh < hoặc > còn = thì báo lỗi(như file anh em đính kèm).Ngược lại khi em sửa " AND [" & Sheet3.Range("A1") & "]=" & Sheet3.Range("B1") thì chỉ sử dụng được giá trị sác đinh đó ...
Nhân đây em muốn nhờ anh giúp hộ.Nếu ở sheet nguồn em không đặt tên bảng muốn sử dụng tên sheet và vùng dữ liệu và trong câu truy vấn kết nôi (HDR=No) để không phải sử dụng tiêu đề mà sử dung theo kiểu (SELECT f1,f2,f3 FROM ............. where f1 =...).Anh kiểm tra file đính kèm giúp em với..

- Bạn phải học lại cách viết câu lệnh SQL trong VBA. Bạn viết sai cú pháp nên nó lỗi thôi. Phải chính xác từng dấu nháy đơn, nháy kép, "&", khoảng trắng...
- Bạn tham chiếu sai các Cell lấy dữ liệu.

>> phải tương ứng địa chỉ Cell bạn đã thiết kế:

Về việc dùng "HDR=No", tôi khuyên nếu bảng dữ liệu của bạn có "tiêu đề" cột là tiếng Anh hoặc tiếng Việt không dấu thì nên dùng luôn tiêu đề cột cho nó tường minh. Sau này nếu bảng dữ liệu của bạn có bị thay đổi vị trí các cột cũng không cần sửa lại code F1, F2... vì nó tham chiếu thẳng tiêu đề cột, bất kể nó nằm vị trí 1, 2, 3 v.v..
Theo câu lệnh SQL bạn đang viết theo kiểu " Select f1,f2, f3...Where..." thì nếu một trong các tham số điều kiện mà để trống thì code sẽ báo lỗi ngay.

thnghiachau · 12/10/20

ongke0711 đã viết:
- Bạn phải học lại cách viết câu lệnh SQL trong VBA. Bạn viết sai cú pháp nên nó lỗi thôi. Phải chính xác từng dấu nháy đơn, nháy kép, "&", khoảng trắng...
- Bạn tham chiếu sai các Cell lấy dữ liệu.

View attachment 247216

>> phải tương ứng địa chỉ Cell bạn đã thiết kế:

View attachment 247217

Về việc dùng "HDR=No", tôi khuyên nếu bảng dữ liệu của bạn có "tiêu đề" cột là tiếng Anh hoặc tiếng Việt không dấu thì nên dùng luôn tiêu đề cột cho nó tường minh. Sau này nếu bảng dữ liệu của bạn có bị thay đổi vị trí các cột cũng không cần sửa lại code F1, F2... vì nó tham chiếu thẳng tiêu đề cột, bất kể nó nằm vị trí 1, 2, 3 v.v..
Theo câu lệnh SQL bạn đang viết theo kiểu " Select f1,f2, f3...Where..." thì nếu một trong các tham số điều kiện mà để trống thì code sẽ báo lỗi ngay.

Hic cái SQL mình cũng toàn hay sai.... học cách viết rõ như bạn mới được

shnhatha221108 · 13/10/20

ongke0711 đã viết:
- Bạn phải học lại cách viết câu lệnh SQL trong VBA. Bạn viết sai cú pháp nên nó lỗi thôi. Phải chính xác từng dấu nháy đơn, nháy kép, "&", khoảng trắng...
- Bạn tham chiếu sai các Cell lấy dữ liệu.

View attachment 247216

>> phải tương ứng địa chỉ Cell bạn đã thiết kế:

View attachment 247217

Về việc dùng "HDR=No", tôi khuyên nếu bảng dữ liệu của bạn có "tiêu đề" cột là tiếng Anh hoặc tiếng Việt không dấu thì nên dùng luôn tiêu đề cột cho nó tường minh. Sau này nếu bảng dữ liệu của bạn có bị thay đổi vị trí các cột cũng không cần sửa lại code F1, F2... vì nó tham chiếu thẳng tiêu đề cột, bất kể nó nằm vị trí 1, 2, 3 v.v..
Theo câu lệnh SQL bạn đang viết theo kiểu " Select f1,f2, f3...Where..." thì nếu một trong các tham số điều kiện mà để trống thì code sẽ báo lỗi ngay.

Vâng .!cảm ơn anh do em chưa đi chuyên sâu về lĩnh vực này nên chỉ dám học mót mỗi vấn đề một chút thôi ạ nên có nhiều sai sót..
Anh cho em hỏi phần khai báo dưới có phải theo kiểu dữ liệu ,nhờ anh giải thích giúp...Còn điều kiện lọc Where 1=1' là sao ạ..?Mong được anh và anh chị em trên diễn đàn chỉ bảo..
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

ongke0711 · 13/10/20

shnhatha221108 đã viết:
Anh cho em hỏi phần khai báo dưới có phải theo kiểu dữ liệu ,nhờ anh giải thích giúp...Còn điều kiện lọc Where 1=1' là sao ạ..?Mong được anh và anh chị em trên diễn đàn chỉ bảo..
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

- Các hằng số (adOpenStatic,...) là tham số cho đối tượng Connection, Recordset của thư viện ADODB. Do trong file viết code kiểu khai báo muộn (Late Binding) thư viện ADODB nên buộc phải khai báo các hằng như trên để VBA nó hiểu, không bắt lỗi chưa khai báo biến. Nếu dùng Early Binding sẽ không cần khai báo các dòng trên. Bạn tự tìm hiểu 2 kiểu khai báo này theo từ khoá đã nêu trên.
- Về câu lệnh SQL tôi thêm "Where 1 = 1" là thủ thuật để tôi nối thêm các điều kiện sau nữa và các điều kiện sau này có thể tuỳ chọn, có cái có, có cái không. Nếu không có thêm các điều kiện phía sau nữa thì câu lệnh SQL vẫn luôn chạy vì 1=1 --> True. Nếu không có "Where 1 =1" thì việc nối thêm chuỗi điều kiện phía sau sẽ dài dòng, phức tạp hơn.
Ví dụ:
SELECT * FROM tblABC

Nếu thêm 2 điều kiện lọc dữ liệu nữa: [NgaySX]=#01/01/2010# và [MaHang] = 'HH001'
2 điều kiện này có thể tìm cả 2, 1 trong 2 hoặc không lọc theo điều kiện nào cả nếu gười dùng không nhập dữ kiện cho nó.
Khi đó câu lênh SQL sẽ là:

"... WHERE [NgaySX] = #" & IIF(Cell 'B2' = Null, "*", Cell 'B2')  & "# AND [MaHang] ='" & IIF(Cell 'B3' = Null, "*", Cell 'B3') & "'"

Thường cái Form tìm kiếm nó sẽ bao gồm cả chục điều kiện tìm kiếm, nếu viết kiểu trên thì nó sẽ rất rối, khó sửa nên tôi chọn cách thêm 1=1 cho đơn giản câu lệnh.

shnhatha221108 · 13/10/20

ongke0711 đã viết:
- Các hằng số (adOpenStatic,...) là tham số cho đối tượng Connection, Recordset của thư viện ADODB. Do trong file viết code kiểu khai báo muộn (Late Binding) thư viện ADODB nên buộc phải khai báo các hằng như trên để VBA nó hiểu, không bắt lỗi chưa khai báo biến. Nếu dùng Early Binding sẽ không cần khai báo các dòng trên. Bạn tự tìm hiểu 2 kiểu khai báo này theo từ khoá đã nêu trên.
- Về câu lệnh SQL tôi thêm "Where 1 = 1" là thủ thuật để tôi nối thêm các điều kiện sau nữa và các điều kiện sau này có thể tuỳ chọn, có cái có, có cái không. Nếu không có thêm các điều kiện phía sau nữa thì câu lệnh SQL vẫn luôn chạy vì 1=1 --> True. Nếu không có "Where 1 =1" thì việc nối thêm chuỗi điều kiện phía sau sẽ dài dòng, phức tạp hơn.
Ví dụ:
SELECT * FROM tblABC

Nếu thêm 2 điều kiện lọc dữ liệu nữa: [NgaySX]=#01/01/2010# và [MaHang] = 'HH001'
2 điều kiện này có thể tìm cả 2, 1 trong 2 hoặc không lọc theo điều kiện nào cả nếu gười dùng không nhập dữ kiện cho nó.
Khi đó câu lênh SQL sẽ là:

"... WHERE [NgaySX] = #" & IIF(Cell 'B2' = Null, "*", Cell 'B2') & "# AND [MaHang] ='" & IIF(Cell 'B3' = Null, "*", Cell 'B3') & "'"

Thường cái Form tìm kiếm nó sẽ bao gồm cả chục điều kiện tìm kiếm, nếu viết kiểu trên thì nó sẽ rất rối, khó sửa nên tôi chọn cách thêm 1=1 cho đơn giản câu lệnh.

View attachment 247303

Cảm ơn anh đã giải thích rất rõ ràng nhưng với em những điều trên thật đúng là vượt ngoài khả năng hiểu biết.Em sẽ cố tìm hiểu dần để biết thêm phần nào tốt phần đó.Một lần nữa xin cảm ơn anh đã nhiệt tình chỉ bảo..!

Cách truy vấn dữ liệu trên cùng file không mở kết nối (ADODB.Connection)

Nam Nhân✨Hiếu Lễ Nghĩa Trí Tín✨

Thành viên chính thức

Nam Nhân✨Hiếu Lễ Nghĩa Trí Tín✨

Thành viên chính thức

❆❆❆❆❆❆❆❆

❆❆❆❆❆❆❆❆

Thành viên chính thức

Nam Nhân✨Hiếu Lễ Nghĩa Trí Tín✨

Thành viên chính thức

❆❆❆❆❆❆❆❆

Nam Nhân✨Hiếu Lễ Nghĩa Trí Tín✨

Thành viên mới

Thành viên chính thức

Thành viên gắn bó

Thành viên chính thức

File đính kèm

Thành viên gắn bó

Chỉ biết ngồi BÈ và PHÁN chuyện!!!

Thành viên chính thức

Thành viên gắn bó

Thành viên chính thức

Đếm ngược thời gian