Bài tập về ADO căn bản.

[asturias]

[TIP]Bài tập 5: Chèn 1 mẫu tin vào cơ sở dữ liệu[/TIP]

1./ Cú pháp:

[GPECODE=sql]INSERT INTO [Tên Bảng] ([Cột 1], [Cột 2], ...,[Cột n])
VALUES (GiaTri_1, GiaTri_2, ...,GiaTri_n)[/GPECODE]

Lưu ý phần GiaTri:

• Nhập ngày thì phải thêm # bao quanh ngày (VD:#15/06/1977#)
• Nhập số thì chỉ ghi số, không cần thêm gì cả
• Nhập dạng chuổi thì phải thêm dấu nháy bao quanh chuổi đó(VD: 'HLMT')

2./ Bài Tập:

[INFO1]Anh, chị hãy nhập liệu 1 mẫu tin sau vào cơ sở dữ liệu

• ID=415
• W_HDATE=10/01/2013
• PONO=DW12WQ009
• MATERIAL NAME=POLY ZIPPER #5
• COLOR NAME=BEIGE
• UNIT=M
• SUPPLIER=HHH VIETNAM

[/INFO1]

Cám ơn Anh vì Chủ đề rất hữu ích.
Em có 02 file khác nhau (1 file Main, 1 file DataBase) cùng 1 thư mục, e đã thực hiện được việc truy suất dữ liệu từ file DataBase sang file Main, nhưng còn Insert, Update (theo như chỉ dẫn), Delete e không thực hiện được từ file Main để tác động tới file DataBase. Hệ thống thường báo lỗi "Sytax error..." hoặc "Cannot Update, database/object is Read-only".
Em rất mong Anh giải đáp lỗi phát sinh này. (nếu em thực hiện lệnh trên 1 file hiện hành thì lại thực hiện được)

 

[Hoàng Trọng Nghĩa]

Cám ơn Anh vì Chủ đề rất hữu ích.
Em có 02 file khác nhau (1 file Main, 1 file DataBase) cùng 1 thư mục, e đã thực hiện được việc truy suất dữ liệu từ file DataBase sang file Main, nhưng còn Insert, Update (theo như chỉ dẫn), Delete e không thực hiện được từ file Main để tác động tới file DataBase. Hệ thống thường báo lỗi "Sytax error..." hoặc "Cannot Update, database/object is Read-only".
Em rất mong Anh giải đáp lỗi phát sinh này. (nếu em thực hiện lệnh trên 1 file hiện hành thì lại thực hiện được)

Khi sử dụng ADO trong EXCEL, UPDATE thì được, nhưng DELETE thì không nha bạn! Nếu xóa bằng cách update các khoảng trắng thì CSDL của bạn trở thành các dòng trắng xen kẻ trông rất xấu. Cái này đã nói trong các bài trước nha bạn.

 

[asturias]

Khi sử dụng ADO trong EXCEL, UPDATE thì được, nhưng DELETE thì không nha bạn! Nếu xóa bằng cách update các khoảng trắng thì CSDL của bạn trở thành các dòng trắng xen kẻ trông rất xấu. Cái này đã nói trong các bài trước nha bạn.

Cám ơn Anh, hôm nay đến cơ quan thì code INSERT, UPDATE lại chạy được, hic. Vậy Anh cho em hỏi, nếu em muốn xóa 1 dòng ở file excel B bằng cách chạy Code từ file A (file B có thể mở hoặc không) thì có cách nào không ạ. Rất mong được các Anh, Chị giải đáp. Em cám ơn.

 

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

Cám ơn Anh, hôm nay đến cơ quan thì code INSERT, UPDATE lại chạy được, hic. Vậy Anh cho em hỏi, nếu em muốn xóa 1 dòng ở file excel B bằng cách chạy Code từ file A (file B có thể mở hoặc không) thì có cách nào không ạ. Rất mong được các Anh, Chị giải đáp. Em cám ơn.

Việc xóa = ado thì sẽ không hổ trợ, có thể dùng cách khác = vba là mở file đó lên tìm dòng thỏa điều kiện rồi xóa nó đi, hoặc nếu dùng ado thì dùng cau lệnh update để clear trống dòng đó đi, tuy nhiên theo cách này thì dữ liệu của bạn sẽ không được liên tục.

 

[asturias]

Việc xóa = ado thì sẽ không hổ trợ, có thể dùng cách khác = vba là mở file đó lên tìm dòng thỏa điều kiện rồi xóa nó đi, hoặc nếu dùng ado thì dùng cau lệnh update để clear trống dòng đó đi, tuy nhiên theo cách này thì dữ liệu của bạn sẽ không được liên tục.

Em cám ơn Anh.
Em có 2 file dữ liệu mẫu theo đính kèm.
Nếu có thể Anh và các Anh, Chị trên diễn đàn dùng VBA để em tham khảo xóa đi những dòng dữ liệu ở file B với điều kiện field Stxt có chứa chuỗi "tgt" được không ạ?
Em cám ơn các Anh, Chị!

 

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

Em cám ơn Anh.
Em có 2 file dữ liệu mẫu theo đính kèm.
Nếu có thể Anh và các Anh, Chị trên diễn đàn dùng VBA để em tham khảo xóa đi những dòng dữ liệu ở file B với điều kiện field Stxt có chứa chuỗi "tgt" được không ạ?
Em cám ơn các Anh, Chị!

Đây là bài thảo luận về ado nên tôi trả lời về ado nhé.

[GPECODE=sql]Sub XoaDong()
On Error GoTo Handle
Dim cnn As Object, lsSQL As String, lrs As Object
Set cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & _
"\B.xls;Extended Properties=""Excel 8.0;HDR=Yes;"";"
.Open
End With
lsSQL = "UPDATE [General$] " & _
"set [po]=null, [material]=null,[stxt]=null,[order]=null," & _
"[quant]=null,[date]=null where ucase(stxt) like '%TGT%'"
lrs.Open lsSQL, cnn, 3, 1
Set lrs = Nothing
cnn.Close: Set cnn = Nothing
Exit Sub
Handle:
MsgBox Err.Description


End Sub

[/GPECODE]

 

[VetMini]

Vì tựa tô bít này có từ "căn bản", và bài mở đầu có câu "cho người mới học" nên tôi mạn phép góp ý về việc "xoá" dòng dữ liệu trong Excel:

Có lẽ cần nhắc nhở mọi người rằng chính Microsoft khi cung cấp ADO cho sử dụng với Excel có câu giới thiệu như sau:

The rows and columns of a Microsoft Excel spreadsheet closely resemble the rows and columns of a database table. As long as users keep in mind that Microsoft Excel is not a relational database management system, and recognize the limitations that this fact imposes, it often makes sense to take advantage of Excel and its tools to store and analyze data.
​

Chỗ xanh lá: cách thức tình bày dòng và cột của Excel gần giống với hình thức bảng trong CSDL.
Chỗ bôi đỏ: nên ghi nhớ Excel không phải là công cụ quản lý CSDL liên hệ
Chỗ bôi xanh: (dịch thoáng) vì vậy phải chấp nhận rằng có những điều không thể làm được như CSDL thông thường.

Công cụ tiện ích ADO này được đưa vào VBA với mục đich giứp cho việc truy cập dữ liệu theo dạng bảng được thuận lợi. Một khi có thể truy cập dữ liệu theo dạng bảng, nguời ta cũng có thể lợi dụng tính năng của CSDL Liên Hệ và phân tích dữ liệu qua ngôn ngữ SQL.

Sức mạnh của CSDL LH (Relational Database) là phân tích dữ liệu qua sự liên hệ giữa các bảng, các cột... SQL (Structured Query Language) là ngôn ngữ chính thức được đặt ra để hổ trợ việc truy vấn và phân tích dữ liệu. Ngoài công việc đó, chuyện sửa đổi dữ liệu là việc phụ.

Khi dùng ADO như một công cụ quản lý và sửa đổi dữ liệu trong Excel thì ngưòi sử dụng đã cố tính chơi tò "không có chó bắt mèo c..". Điều đó cũng chẳng sao, chỉ hiềm nổi, tục ngữ ta cũng có câu "được đằng chân, lân đằng đâu". Sau một thời gian, người ta quên mất là nhà mình nuôi con mèo chứ không phải con chó.

Trong CSDL LH, các dòng được chứa theo dạng rời rạc, khái niệm dòng đi trước và dòng đi sau hầu như không có (nếu có thì 99% là thiết kế dỏm). Trong Excel, các dòng rõ ràng được ghi theo thứ tự. Nói theo ngôn ngữ CSDL thì là dạng dữ liệu liên tục (sequential) Để thực hiện việc truy vấn theo bảng của SQL, ADO phải miễn cưỡng sử dụng kỹ thuật ISAM (Indexed Sequential Access Method). Nhưng đến đó là giới hạn rồi. Xoá bỏ (delete) một dòng ở lưng chừng là xâm phạm tính chất liên tục. Và hiện tại, ADO không cho phép làm vậy - có thể một phiên bản nào đó trong tương lai sẽ được - nhưng hiện tại thì không.

Ngay cả trong CSDL LH thực tiễn, người ta cũng rất ít khi dùng lệnh DELETE. Thông thường thì người ta đặt một cái cờ (flag) cho mỗi dòng. Nếu cờ ở một trị số quy ước nào đó thì dòng (record) ấy coi như đã bị xoá.

 

[leonguyenz]

Em xin code bằng ADO về tổng hợp dữ liệu từ nhiều sheet, được phép lựa chọn cột để tổng hợp. Trong file ví dụ em làm 2 sheet, dữ liệu 2 cột khác nhau, yêu cầu tổng hợp 4 cột (tùy ý chọn cột) với cột đầu tiên (Mscn) duy nhất, 3 cột sau là tổng lại.
Chân thành cảm ơn !

 

[Lê Duy Thương]

Em xin code bằng ADO về tổng hợp dữ liệu từ nhiều sheet, được phép lựa chọn cột để tổng hợp. Trong file ví dụ em làm 2 sheet, dữ liệu 2 cột khác nhau, yêu cầu tổng hợp 4 cột (tùy ý chọn cột) với cột đầu tiên (Mscn) duy nhất, 3 cột sau là tổng lại.
Chân thành cảm ơn !

không hiểu là chọn cột như thế nào .chứ dữ liệu giống dữ liệu mẫu thì consolidate là xong mà Thảo

 

[leonguyenz]

không hiểu là chọn cột như thế nào .chứ dữ liệu giống dữ liệu mẫu thì consolidate là xong mà Thảo

Em muốn viết code mà anh. Consolidate phải cùng số cột dữ liệu, ở đây mỗi sheet có số cột khác nhau, dữ liệu thật có trên 2 sheet.

 

[Lê Duy Thương]

em muốn viết code mà anh. Consolidate phải cùng số cột dữ liệu, ở đây mỗi sheet có số cột khác nhau, dữ liệu thật có trên 2 sheet.

không biết tôi có nhầm không ?.tôi vẫn thường dùng consolidate đâu cần cùng cột dữ liệu. Chỉ cần tiêu đề và cột mã giống nhau là được mà. Ah mà quang hải đã viết code consolidate ngon lành rồi đấy . Tìm đi

 

[VetMini]

Lập một câu truy vấn tổng quát:

qString = "SELECT Mscn, SUM(<cot1>) AS <cot1>, SUM(<cot2>) AS <cot2>, SUM(<cot3>) AS <cot3> FROM (" _
& " SELECT Mscn, <cotA1> AS <cot1>, <cotA2> AS <cot2>, <cotA3> AS <cot3> FROM BangA WHERE Mscn > 0 " _
& " UNION ALL " _
& " SELECT Mscn, <cotB1> AS <cot1>, <cotB2> AS <cot2>, <cotB3> AS <cot3> FROM BangB WHERE Mscn > 0 " _
& " ) GROUP BY Mscn"

Dùng hàm REPLACE hoặc SUBSTITUTE để đổi các chuỗi <cotN> theo đúng tên cột trước khi truy vấn.

 

[Quang_Hải]

Em xin code bằng ADO về tổng hợp dữ liệu từ nhiều sheet, được phép lựa chọn cột để tổng hợp. Trong file ví dụ em làm 2 sheet, dữ liệu 2 cột khác nhau, yêu cầu tổng hợp 4 cột (tùy ý chọn cột) với cột đầu tiên (Mscn) duy nhất, 3 cột sau là tổng lại.
Chân thành cảm ơn !

ADO thì mình thua rồi. Bài này chắc không dùng consolidate được nên thử làm vầy coi đúng không.

Sub tong_hop()
Dim dl(), kq(1 To 10000, 1 To 4), d As Object, tieude()
Dim sh As Worksheet, i As Long, k As Long, cot As Byte, n
Set d = CreateObject("scripting.dictionary")
tieude = Sheets("Tong Hop").[A4:D4].Value
For Each sh In ThisWorkbook.Worksheets
   If sh.Name <> "Tong Hop" Then
      cot = sh.[IV3].End(1).Column - 5
      dl = sh.Range(sh.[F3], sh.[F65536].End(3)).Resize(, cot)
      For i = 2 To UBound(dl)
         If dl(i, 1) <> 0 Then
            dl(i, cot) = dl(i, cot) * -1
            If Not d.exists(dl(i, 1)) Then
               k = k + 1
               d.Add dl(i, 1), k
               kq(k, 1) = dl(i, 1)
               For n = 2 To cot
                  For m = 2 To 4
                     If dl(1, n) = tieude(1, m) Then
                        kq(k, m) = dl(i, n)
                     End If
                  Next
               Next
            Else
               For n = 2 To cot
                  For m = 2 To 4
                     If dl(1, n) = tieude(1, m) Then
                        kq(d.Item(dl(i, 1)), m) = kq(d.Item(dl(i, 1)), m) + dl(i, n)
                     End If
                  Next
               Next
            End If
         End If
      Next i
   End If
Next sh
Sheets("Tong hop").[A5:D65536].ClearContents
Sheets("Tong hop").[A5].Resize(k, 4) = kq
End Sub

 

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

Em xin code bằng ADO về tổng hợp dữ liệu từ nhiều sheet, được phép lựa chọn cột để tổng hợp. Trong file ví dụ em làm 2 sheet, dữ liệu 2 cột khác nhau, yêu cầu tổng hợp 4 cột (tùy ý chọn cột) với cột đầu tiên (Mscn) duy nhất, 3 cột sau là tổng lại.
Chân thành cảm ơn !

Lâu quá mới về thăm nhà, cột để ra kết quả ở sheet3 có điều kiện như thế nào vậy Thảo? theo anh cộng thì nó sẽ là 30.008 thay vì 23.980 của em

 

[leonguyenz]

Lâu quá mới về thăm nhà, cột để ra kết quả ở sheet3 có điều kiện như thế nào vậy Thảo? theo anh cộng thì nó sẽ là 30.008 thay vì 23.980 của em

Dạ, đúng rồi anh, 30,008. Lúc đầu em dùng Consolidate để tổng số liệu, sau đó lại gõ giá trị 0 ở 2 sheet kia để ví dụ mà quên tính tổng lại 1 lần nữa.

 

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

Dạ, đúng rồi anh, 30,008. Lúc đầu em dùng Consolidate để tổng số liệu, sau đó lại gõ giá trị 0 ở 2 sheet kia để ví dụ mà quên tính tổng lại 1 lần nữa.

VẬy em dùng code sau nhé.

[GPECODE=sql]Sub Tong_HLMT()
Dim lsSQL As String, cnn As Object, lrs As Object
Set cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=No;"";"
.Open
End With
lsSQL = "select F1 as Mscn, sum(f10) as Gbloi,sum(f11) as Other,-sum(f12) as [Total] from " & _
"(SELECT F1, F10, F11, F12 FROM [Sheet1$F4:Q65536] " & _
"union all " & _
"SELECT F1, F4, F5, F6 FROM [Sheet2$F4:K65536]) " & _
"group by f1 having f1>0"
lrs.Open lsSQL, cnn, 3, 1
With Sheet3
.[a:d].Clear
For i = 1 To lrs.Fields.Count
.Cells(4, i).Value = lrs.Fields(i - 1).Name
Next
.[A5].CopyFromRecordset lrs
End With
lrs.Close: Set lrs = Nothing
cnn.Close: Set cnn = Nothing

End Sub
[/GPECODE]

 

[leonguyenz]

Em đã test, code chạy rất tuyệt!
Lúc trước em dùng ADO tổng, nhưng tổng được 1 cột thôi, giờ học thêm được cái mới. Cảm ơn anh nhiều!

 

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

Dùng hàm REPLACE hoặc SUBSTITUTE để đổi các chuỗi <cotN> theo đúng tên cột trước khi truy vấn.

Cho mình hỏi là mục đích chuyển đổi để làm gì vậy bạn?

 

[Hoàng Trọng Nghĩa]

Xin cho hỏi, nếu dùng ADO thì mình truy xuất các cột, chẳng hạn 10 cột của một bảng, mình lấy cột 1, 3, 5 và 7 đến 10 (tức là không lấy cột 2, 4, 6) thì mình phải làm sao? Hay phải liệt kê Select theo tên cột ngoại trừ tên của 3 cột kia?

 

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

Xin cho hỏi, nếu dùng ADO thì mình truy xuất các cột, chẳng hạn 10 cột của một bảng, mình lấy cột 1, 3, 5 và 7 đến 10 (tức là không lấy cột 2, 4, 6) thì mình phải làm sao? Hay phải liệt kê Select theo tên cột ngoại trừ tên của 3 cột kia?

Chắc chắn là như thế, anh chọn cột nào thì phải select cột đó theo thứ tự anh đặt ra, còn muốn chọn hết là dấu sao (*)