Thắc mắc về ADO và CSDL Excel (5 người xem)

[KVP]

Chào các anh, chị

Sau khi đọc và tìm hiểu một số đề tài, ví dụ của các Bác Pmt412, Sealand về ADO và sử dụng ADO trong truy vấn CSDL excel, phần lớn trong các ví dụ đều sử dụng chủ yếu câu lệnh SELECT để trích lọc và Join dữ liệu từ các bảng.
Tôi thử có gắng sử dụng câu lệnh DELETE, UPDATE dữ liệu trực tiếp vào dữ liệu trên Excel nhưng không thể nào thực hiện được.
Các Bác có thể cho xin một ví dụ về cách thực hiện câu lệnh này thao tác trực tiếp trên dữ liệu excel được không ạh.

Xin chân thành cám ơn

 

[levanduyet]

Tôi thử có gắng sử dụng câu lệnh DELETE, UPDATE dữ liệu trực tiếp vào dữ liệu trên Excel nhưng không thể nào thực hiện được

Bạn chỉ nói chung chung thì rất khó để trả lời cho bạn. Vậy khi thực hiện các câu lệnh trên, thông báo lỗi là gì? Hiện tượng gì xãy ra khi thực hiện?...

Lê Văn Duyệt

 

[ptm0412]

Anh cho xin 1 file thí dụ mẫu cần update và delete. Tôi nghĩ là được nhưng những file dùng trong topic kia sau khi làm xong nó nhiều code quá.

 

[KVP]

Cám ơn các Bác

Em xin phép mượn File của Bác Pmt412 rút gọn lại để upload.
Phần ý tưởng và thao tác em đọc qua tài liệu của Anh Duyệt (sử dụng Command) nhưng nó hơi khác ADO.net nên vẫn loay hoay.

Các bác vui lòng sửa dùm em

Xin cám ơn.

 

[ptm0412]

Câu SQL đúng là:

DELETE DSDV.*, DSBH.SoBH
FROM DSBH INNER JOIN DSDV ON DSBH.SoBH=DSDV.SoDV
WHERE (DSBH.SoBH=DSDV.SoDV);

Đoạn code của Else - End If:
Phải có dòng: Recex.Open mySql, Cnex, adOpenKeyset, adLockOptimistic

Ngoài ra, cần 1 câu lệnh để gán Recex xuống sheet, thí dụ gán xuống D1:

Sheet2.[D1].CopyFromRecordset Recex

Tuy vậy, vẫn bị lỗi: Deleting Data in a linked table is not supported by this ISAM.

Không biết Duyệt có cao kiến gì không?

 

[KVP]

Cái này em muốn xóa trực tiếp trên sheets("DONVI").

Phải gán xuống sheets ? Cái này em nghĩ là phải có một phương thức khác thay thế.

 

[ThuNghi]

Cái này em muốn xóa trực tiếp trên sheets("DONVI").

Phải gán xuống sheets ? Cái này em nghĩ là phải có một phương thức khác thay thế.

Không dùng ADO được không, hay chỉ dùng ADO lấy BHXH và dùng Dictionary để xoá.
Nếu OK thì viết vài dòng code thôi.

 

[KVP]

Tuy vậy, vẫn bị lỗi: Deleting Data in a linked table is not supported by this ISAM.

Không biết Duyệt có cao kiến gì không?

Em đọc được cái này

Delete

You are more restricted in deleting Excel data than data from a relational data source. In a relational database, "row" has no meaning or existence apart from "record"; in an Excel worksheet, this is not true. You can delete values in fields (cells). However, you cannot:

1. Delete an entire record at once or you receive the following error message: Deleting data in a linked table is not supported by this ISAM.
   
   
   You can only delete a record by blanking out the contents of each individual field.
2. Delete the value in a cell containing an Excel formula or you receive the following error message: Operation is not allowed in this context.
   
   
3. You cannot delete the empty spreadsheet row(s) in which the deleted data was located, and your recordset will continue to display empty records corresponding to these empty rows.

A caution about editing Excel data with ADO: When you insert text data into Excel with ADO, the text value is preceded with a single quote. This may cause problems later in working with the new data.

Có lẽ anh Thunghi nói đúng

 

[ptm0412]

Tạm thời đi đường vòng: dùng select lấy những records đạt yêu cầu là DSDV có, mà DSBH không có, tương đương sau khi dùng Delete query.

SELECT DSDV.*, DSBH.SoBH
FROM DSDV LEFT JOIN DSBH ON DSDV.SoDV = DSBH.SoBH
WHERE (((DSBH.SoBH) Is Null));

Nếu để xử lý tiếp thì không cần gán xuống sheet, mà xử lý chính Recordset hoặc mảng. Còn nếu như ý nghĩa là xoá dòng thừa "trực tiếp trên sheets("DONVI")" (sic), thì rõ ràng là phải xoá Data cũ trên sheet rồi gán data mới, ít dòng hơn, vào chỗ đó.

Code sau đây gán 321 dòng xuống vị trí mới là E2 (có thể xoá data cũ rồi chép vào A2)

Private Sub CommandButton1_Click()
With Application
  .ScreenUpdating = False: .Calculation = xlCalculationManual
End With
KetNoi

mySql = "SELECT DSDV.*, DSBH.SoBH " & Chr(10) & _
"FROM DSDV LEFT JOIN DSBH ON DSDV.SoDV = DSBH.SoBH" & Chr(10) & _
"WHERE (((DSBH.SoBH) Is Null));"

Recex.Open mySql, Cnex, adOpenKeyset, adLockOptimistic
Sheet2.[e2].CopyFromRecordset Recex

BoKetNoi
With Application
  .ScreenUpdating = True: .Calculation = xlCalculationAutomatic
End With

End Sub

http://www.mediafire.com/file/09gdd44ubsrxr0f/ADO_GPEPtm.rar

 

[ThuNghi]

Tạm thời đi đường vòng: dùng select lấy những records đạt yêu cầu là DSDV có, mà DSBH không có, tương đương sau khi dùng Delete query.

SELECT DSDV.*, DSBH.SoBH
FROM DSDV LEFT JOIN DSBH ON DSDV.SoDV = DSBH.SoBH
WHERE (((DSBH.SoBH) Is Null));

Nếu để xử lý tiếp thì không cần gán xuống sheet, mà xử lý chính Recordset hoặc mảng. Còn nếu như ý nghĩa là xoá dòng thừa "trực tiếp trên sheets("DONVI")" (sic), thì rõ ràng là phải xoá Data cũ trên sheet rồi gán data mới, ít dòng hơn, vào chỗ đó.

Code sau đây gán 321 dòng xuống vị trí mới là E2 (có thể xoá data cũ rồi chép vào A2)

Private Sub CommandButton1_Click()
With Application
  .ScreenUpdating = False: .Calculation = xlCalculationManual
End With
KetNoi

mySql = "SELECT DSDV.*, DSBH.SoBH " & Chr(10) & _
"FROM DSDV LEFT JOIN DSBH ON DSDV.SoDV = DSBH.SoBH" & Chr(10) & _
"WHERE (((DSBH.SoBH) Is Null));"

Recex.Open mySql, Cnex, adOpenKeyset, adLockOptimistic
Sheet2.[e2].CopyFromRecordset Recex

BoKetNoi
With Application
  .ScreenUpdating = True: .Calculation = xlCalculationAutomatic
End With

End Sub

http://www.mediafire.com/file/09gdd44ubsrxr0f/ADO_GPEPtm.rar

Với dữ liệu 5.000 dòng thì code trên nhanh hơn Dic. Nhưng với yêu cầu trên mà dùng Dic sẽ đơn giản và nhanh hơn ADO nhiều. Test với 50.000 dòng sẽ thấy. Trường hợp nếu lấy sh BHXH ở file khác thì chỉ dùng ADO để lấy Data. Sau đó dùng Dic.

Dim endR As Long
Sub XoaTrung()
Dim T
T = Timer
Dim Dic As Object, WF As WorksheetFunction
Dim ArrSoBH(), ArrDV(), ArrKq()
Dim i As Long, s As Long
Set WF = WorksheetFunction
Set Dic = CreateObject("Scripting.Dictionary")
With Sheets("BHXH")
  endR = .Cells(65000, 1).End(xlUp).Row
  ArrSoBH = WF.Transpose(.Range("A2:A" & endR))
End With
For i = 1 To UBound(ArrSoBH)
  If Not Dic.Exists(ArrSoBH(i)) Then
    Dic.Add ArrSoBH(i), i
  End If
Next i
With Sheets("DonVi")
  endR = .Cells(65000, 1).End(xlUp).Row
  ArrDV = .Range("A2:C" & endR).Value
End With
ReDim ArrKq(1 To endR - 1, 1 To 3)
s = 0
For i = 1 To UBound(ArrDV)
  If Not Dic.Exists(ArrDV(i, 1)) Then
    s = s + 1
    For k = 1 To 3
      ArrKq(s, k) = ArrDV(i, k)
    Next k
  End If
Next i
With Sheets("DonVi")
  .[e2].Resize(s, 3) = ArrKq
End With
Erase ArrSoBH(), ArrDV(), ArrKq()
Set Dic = Nothing: Set WF = Nothing
With Sheets("DonVi")
  .[e1] = Timer - T
End With
End Sub

 

[levanduyet]

Không biết Duyệt có cao kiến gì không?

Theo http://support.microsoft.com/kb/257819 thì:

Việc xoá không được theo em đã được giải thích ở đây.

Lê Văn Duyệt