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

[Hai Lúa Miền Tây]

Thông thường ta 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 đó. Điều này làm giảm đi tốc độ khi chạy code.
Hôm nay mình xin chia sẻ với các bạn cách truy vấn dữ liệu mà không cần mở kết nối trên cùng file thực thi.

Sub LocDL_HLMT()
    Dim rst As Object, vArray As Variant, i As Long, thoigian As Long 'Khai bao bien
    Set rst = CreateObject("ADODB.Recordset") ' Khoi tao Recordset
    vArray = Sheet1.Range("A2:D10001").Value ' Du lieu nguon vao mang
    thoigian = Timer()
    With rst
        'Khai bao va mo Recorset
        .Fields.Append "ID", 3
        .Fields.Append "MatID", 200, 10
        .Fields.Append "Balance", 3
        .Open
        'Dua du lieu nguon tu mang da set vao Recordset
        For i = LBound(vArray) To UBound(vArray)
            .AddNew
            .Fields("ID").Value = vArray(i, LBound(vArray))
            .Fields("MatID").Value = vArray(i, LBound(vArray) + 1)
            .Fields("Balance").Value = vArray(i, LBound(vArray) + 3)
            .Update
        Next
        'Loc du lieu trong Recordset
        .Filter = "Balance " & Sheet2.Range("B1")
    End With
    Sheet2.Range("A2:D11000").ClearContents 'Xoa vung du lieu
    Sheet2.Range("A4").CopyFromRecordset rst 'Do du lieu tu Recordset da loc xuong sheet
    MsgBox Timer - thoigian
    rst.Close
    Set rst = Nothing
End Sub

 

[Hoàng Trọng Nghĩa]

Quá nhanh, quá nguy hiểm luôn! Rất tuyệt vời! Với một file Excel cần xuất dữ liệu từ Access chắc không làm được như vậy đâu em nhỉ?

.Fields.Append "MatID", 200, 10

Anh chưa hiểu số 200 và số 10 là cái gì, em vui lòng giải thích giúp anh nhé! Thank you!

 

[FatBear]

thực sự quá tốc độ )

 

[Hai Lúa Miền Tây]

Quá nhanh, quá nguy hiểm luôn! Rất tuyệt vời! Với một file Excel cần xuất dữ liệu từ Access chắc không làm được như vậy đâu em nhỉ?

.Fields.Append "MatID", 200, 10


Anh chưa hiểu số 200 và số 10 là cái gì, em vui lòng giải thích giúp anh nhé! Cảm ơn!

Đúng chính xác là tốc độ tuyệt vời đó anh
Là kiểu dữ liệu thôi anh, 200 là kiểu chuỗi, 10 là độ dài chuỗi đó anh.
Anh xem thêm bảng bên dưới nhé.

Constant	Value	Description
adArray	0x2000	Combine with another data type to indicate that the other data type is an array
adBigInt	20	8-byte signed integer
adBinary	128	Binary
adBoolean	11	True or false Boolean
adBSTR	8	Null-terminated character string
adChapter	136	4-byte chapter value for a child recordset
adChar	129	String
adCurrency	6	Currency format
adDate	7	Number of days since 12/30/1899
adDBDate	133	YYYYMMDD date format
adDBTime	134	HHMMSS time format
adDBTimeStamp	135	YYYYMMDDHHMMSS date/time format
adDecimal	14	Number with fixed precision and scale
adDouble	5	Double precision floating-point
adEmpty	0	no value
adError	10	32-bit error code
adFileTime	64	Number of 100-nanosecond intervals since 1/1/1601
adGUID	72	Globally unique identifier
adIDispatch	9	Currently not supported by ADO
adInteger	3	4-byte signed integer
adIUnknown	13	Currently not supported by ADO
adLongVarBinary	205	Long binary value
adLongVarChar	201	Long string value
adLongVarWChar	203	Long Null-terminates string value
adNumeric	131	Number with fixed precision and scale
adPropVariant	138	PROPVARIANT automation
adSingle	4	Single-precision floating-point value
adSmallInt	2	2-byte signed integer
adTinyInt	16	1-byte signed integer
adUnsignedBigInt	21	8-byte unsigned integer
adUnsignedInt	19	4-byte unsigned integer
adUnsignedSmallInt	18	2-byte unsigned integer
adUnsignedTinyInt	17	1-byte unsigned integer
adUserDefined	132	User-defined variable
adVarBinary	204	Binary value
adVarChar	200	String
adVariant	12	Automation variant
adVarNumeric	139	Variable width exact numeric with signed scale
adVarWChar	202	Null-terminated Unicode character string
adWChar	130	Null-terminated Unicode character string

 

[ongke0711]

Cách của bạn HLMT là tạo ADO recordset lưu trên bộ nhớ máy do đó việc truy xuất ngay trên memory sẽ nhanh hơn truy xuất trên ổ cứng. Cách này rất hay và nhanh để xử lý một tác vụ nào đó xong đóng và giải phóng bộ nhớ.
Cách này cũng thường dùng khi truy vấn dữ liệu từ SQL server lưu tạm để xử lý (sắp xếp, vấn tin, cập nhật...) rồi đồng bộ về lại sql server.

 

[Nguyễn Duy Tuân]

Cách của HLMT trình bày trên là đẩy mảng dữ liệu từ sheet vào recodset, từ đây dùng Filter - lọc. Nếu bài toán của người dùng là trích lọc dữ liệu trên sheet thì hoàn toàn ok. Tuy nhiên đây không phải SQL nên sẽ gặp phải các cấn đề sau:
- Mất thời gian lưu array vào recordset ban đầu. Mảng data có thể lấy từ sheet, còn lấy từ csdl khác như Access thì nên lấy bằng SQL luôn chứ không dùng Array vì sẽ mất hai lần làm không cần thiết (câu hỏi của a Hoàng Trọng Nghĩa)
- Chỉ áp dụng cho bài toán Filter trên data nguồn đưa vào dạng đơn lẻ không quan hệ. Vì không phải cách SQL nên không thực hiện được việc ORDER BY, GROUP BY, HAVING, các kết nối bảng với INNER JOIN, LEFT JOIN,...

Như vậy bài toán trên nên dùng trong phạm vi dữ liệu nguồn là Excel, một bảng nguồn không quan hệ. Bài toán gọi là lọc dữ liệu trong mảng.

 

[FatBear]

.Fields.Append "ID", 3
.Fields.Append "MatID", 200, 10
.Fields.Append "Balance", 3

Bác HLMT cho e hỏi chút, ở dòng 2 có thêm "10" ,vậy tại sao hai dòng ID và balance lại không có thêm giá trị đó ạ.Em cảm ơn

 

[Hai Lúa Miền Tây]

.Fields.Append "ID", 3
.Fields.Append "MatID", 200, 10
.Fields.Append "Balance", 3

Bác HLMT cho e hỏi chút, ở dòng 2 có thêm "10" ,vậy tại sao hai dòng ID và balance lại không có thêm giá trị đó ạ.Em cảm ơn

Bạn xem lại bài 4 tôi đã giải thích rồi nhé bạn.

 

[FatBear]

Bạn xem lại bài 4 tôi đã giải thích rồi nhé bạn.

bác chưa hiểu đúng ý e rồi. ý của e là tại sao ở trường MatID có thêm điều kiện 10 ( tra bảng 32-bit error code ), còn ở 2 trường ID và blance lại không có

 

[Hai Lúa Miền Tây]

bác chưa hiểu đúng ý e rồi. ý của e là tại sao ở trường MatID có thêm điều kiện 10 ( tra bảng 32-bit error code ), còn ở 2 trường ID và blance lại không có

Trường ID và Balance là kiểu Int, còn trường MatID là kiểu String, 10 là độ dài cho chuỗi đó bạn

 

[Kiều Mạnh]

1/ Mạnh nghỉ trên cùng 1 File xài mảng đi cho gọn và nhanh ... trừ khi lấy dữ liệu từ 1 file đóng

2/ Mà xài ADO trong trường hợp này dễ gì nhanh hơn cái Mảng trên Sheet ?????????????

 

[FatBear]

Trường ID và Balance là kiểu Int, còn trường MatID là kiểu String, 10 là độ dài cho chuỗi đó bạn

à. vâng. e cảm ơn

 

[Hai Lúa Miền Tây]

1/ Mạnh nghỉ trên cùng 1 File xài mảng đi cho gọn và nhanh ... trừ khi lấy dữ liệu từ 1 file đóng

2/ Mà xài ADO trong trường hợp này dễ gì nhanh hơn cái Mảng trên Sheet ?????????????

Nếu lọc và sắp xếp thì nó sẽ đơn giản hơn việc xử lý trong mảng đó anh.

 

[Kiều Mạnh]

Nếu lọc và sắp xếp thì nó sẽ đơn giản hơn việc xử lý trong mảng đó anh.

để mai mốt rảnh thử viết thành 1 cái hàm dựa trên code bài 1 xem sao ???? gõ [A1] = Ham(tham so1, tham so2)

không biết gõ cái cộp 1 cái nó có ra không nữa

 

[Hai Lúa Miền Tây]

để mai mốt rảnh thử viết thành 1 cái hàm dựa trên code bài 1 xem sao ???? gõ [A1] = Ham(tham so1, tham so2)

không biết gõ cái cộp 1 cái nó có ra không nữa

Vậy nếu anh viết và xử lý trong mảng cho trường hợp lọc dữ liệu theo điều kiện và sắp xếp thử xem coi cái nào đơn giản hơn nhé.

 

[ongke0711]

Nếu lọc và sắp xếp thì nó sẽ đơn giản hơn việc xử lý trong mảng đó anh.

Tôi thì chưa rành về mảng nhưng đối với "in- memory ADO recordset" này thì nó như một cái table ảo có đầy đủ tên trường (cột) nên bạn có thể dùng các câu lệnh SQL để lọc, sắp xếp. Rs.Sort ="ID"

 

[Hai Lúa Miền Tây]

Tôi thì chưa rành về mảng nhưng đối với "in- memory ADO recordset" này thì nó như một cái table ảo có đầy đủ tên trường (cột) nên bạn có thể dùng các câu lệnh SQL để lọc, sắp xếp. Rs.Sort ="ID"

Đó là lý do mà tôi đã nói ở trên rằng nó sẽ rất đơn giản

 

[Kiều Mạnh]

Đó là lý do mà tôi đã nói ở trên rằng nó sẽ rất đơn giản

nếu lấy dữ liệu từ 1 file đang đóng lên ta phải khai báo lại như thế nào nhỉ ?

 

[Hai Lúa Miền Tây]

nếu lấy dữ liệu từ 1 file đang đóng lên ta phải khai báo lại như thế nào nhỉ ?

Chỉ trên file hiện hành, còn file đóng thì mở kết nối rồi anh

 

[leonguyenz]

Chỉ trên file hiện hành, còn file đóng thì mở kết nối rồi anh

Anh giới thiệu thêm ứng dụng của truy vấn này được không?
Vì nếu để lọc dữ liệu thì AdvanceFilter có vẻ đơn giản, dễ áp dụng, dù nó có chậm 1 chút.
Em test AdvanceFilter 10k dòng thì thời gian cũng tương đương, còn 120k dòng thì đúng là không bằng cách truy vấn trên.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Tg As Long
Tg = Timer()
If Target.Address = "$F$2" Then
   [A1:D150000].AdvancedFilter 2, [F1:F2], [H1:J1]
End If
MsgBox Timer - Tg
End Sub