Đố vui về ADO, DAO - T3/15

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

Lấy danh sách tên cột của 1 bảng.

Để tiếp tục tôi xin đưa ra câu đố tiếp theo: Làm thế nào để lấy danh sách tên cột của 1 bảng mà không dùng vòng lặp?

 

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

Để tiếp tục tôi xin đưa ra câu đố tiếp theo: Làm thế nào để lấy danh sách tên cột của 1 bảng mà không dùng vòng lặp?

Xin gợi ý 1 chút là nó tương tự như cách lấy tên bảng ở câu đố bài 1

 

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

Cũng có thể mọi người chưa quan tâm lắm, hoặc có thể chưa nhìn thấy đề tài này. Xin phép được làm mới lại.
Mong các bạn tham gia thảo luận để còn tiếp những đề tài mới thú vị hơn.

 

[dhn46]

Để tiếp tục tôi xin đưa ra câu đố tiếp theo: Làm thế nào để lấy danh sách tên cột của 1 bảng mà không dùng vòng lặp?

Em xin đưa đáp án đầu tiên, anh góp ý nhé

Sub FieldName()
    Dim Cn As Object, Rst As Object, LsSql As String, Arr() As Variant
    Set Cn = CreateObject("ADODB.Connection")
    Set Rst = CreateObject("ADODB.recordset")
    Cn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & ThisWorkbook.FullName & _
             ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";")
    LsSql = "Select * from [Table]"
    Rst.Open LsSql, Cn, 3, 1
    Arr = Rst.getrows
    ActiveCell.Resize(UBound(Arr, 1) + 1, 1) = Arr
End Sub

 

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

Em xin đưa đáp án đầu tiên, anh góp ý nhé

Sub FieldName()
    Dim Cn As Object, Rst As Object, LsSql As String, Arr() As Variant
    Set Cn = CreateObject("ADODB.Connection")
    Set Rst = CreateObject("ADODB.recordset")
    Cn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & ThisWorkbook.FullName & _
             ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";")
    LsSql = "Select * from [Table]"
    Rst.Open LsSql, Cn, 3, 1
    Arr = Rst.getrows
    ActiveCell.Resize(UBound(Arr, 1) + 1, 1) = Arr
End Sub

Cách lấy kiểu "ăn gian" như trên có thể lấy như sau:

Sub ColumName()
    Dim Cn As Object, Rst As Object
    Set Cn = CreateObject("ADODB.Connection")
    Cn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & ThisWorkbook.FullName & _
             ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";")
    Set Rst = Cn.Execute("Select top 1 * from [Table]")
    ActiveCell.CopyFromRecordset Rst
    
End Sub

Tuy nhiên tên cột thì bắt buộc phải có, không trùng... cách trên nếu dòng trên cùng là trống thì vẫn là trống, không có tên cột...

 

[Hau151978]

Mình bắt chước làm thử xem sao, chỉ để cổ vũ là chính vì không hiểu về mấy object này lắm. Sub chỉ chạy chính xác khi table nằm ở sheet có name đầu tiên trong các name xếp theo vần abc (do các ADO tự sắp xếp các sheet theo abc, sheet không có table chỉ có 1 phần tử là F1). Tốt nhất là table ở sheet1.

Sub Test()
    Dim cnn As ADODB.Connection, rst As ADODB.Recordset, s$, arr
    s = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
        ";Extended Properties=""Excel 12.0;HDR=YES"";"
    
    Set cnn = New ADODB.Connection
    cnn.Open s
      
    Set rst = cnn.OpenSchema(4)
    arr = rst.GetRows(, , 3)
    ActiveCell.Resize(UBound(arr, 2) + 2 - Sheets.Count, 1) = Application.Transpose(arr)
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub

 

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

Mình bắt chước làm thử xem sao, chỉ để cổ vũ là chính vì không hiểu về mấy object này lắm. Sub chỉ chạy chính xác khi table nằm ở sheet có name đầu tiên trong các name xếp theo vần abc (do các ADO tự sắp xếp các sheet theo abc, sheet không có table chỉ có 1 phần tử là F1). Tốt nhất là table ở sheet1.

Sub Test()
    Dim cnn As ADODB.Connection, rst As ADODB.Recordset, s$, arr
    s = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
        ";Extended Properties=""Excel 12.0;HDR=YES"";"
    
    Set cnn = New ADODB.Connection
    cnn.Open s
      
    Set rst = cnn.OpenSchema(4)
    arr = rst.GetRows(, , 3)
    ActiveCell.Resize(UBound(arr, 2) + 2 - Sheets.Count, 1) = Application.Transpose(arr)
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub

Gần được rồi đó, ráng chút nữa đi. Quy định lấy bảng nào trong file chứ không mặc định là bảng đầu tiên.

 

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

Thôi mình ghi đáp án luôn.

Sub ColumnName()
  Dim cn As Object, ColumnsSchema As Object, arr
  Set cn = CreateObject("ADODB.Connection")
  Set ColumnsSchema = CreateObject("ADODB.Recordset")
  cn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & ThisWorkbook.FullName & _
             ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")
  Set ColumnsSchema = cn.OpenSchema(4, Array(Null, Null, "'TenSheet$'"))
  arr = ColumnsSchema.GetRows(, , "COLUMN_NAME")
  ActiveCell.Resize(UBound(arr, 2) - LBound(arr, 2) + 1, _
        1).Value = Application.Transpose(arr)

End Sub

 

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

Tạo số TT

Xin tiếp tục câu đố dể hơn.

Làm thế nào mình tạo cột số STT trong đoạn truy vấn.

 

[Hau151978]

Xin tiếp tục câu đố dể hơn.

Làm thế nào mình tạo cột số STT trong đoạn truy vấn.

Nếu như ví dụ trong đề bài, cột mã là duy nhất và đã sort thì có thể truy vấn:

Sub STT()
  Dim cn As Connection, rst As Recordset, s$
  Set cn = New Connection
  s = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & ThisWorkbook.FullName & _
             ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
  cn.Open s
  
  Set rst = New Recordset
  s = "select (select count(b.ma) from data b where b.ma<=a.ma) as STT,a.* from data a"
  rst.Open s, cn
  
  Range("G2").CopyFromRecordset rst
  rst.Close
  Set rst = Nothing
  cn.Close
  Set cn = Nothing
End Sub

 

[loinguyen212]

Nếu như ví dụ trong đề bài, cột mã là duy nhất và đã sort thì có thể truy vấn:

Vậy trường hợp cột mã không còn là duy nhất (có các mã giống nhau) HOẶC chưa sort thì kết quả không còn được gọi là đánh số thứ tự nữa.
Em chú ý thì trong yêu cầu #29 của anh Hai Lúa có để hình kết quả với cột Ma đang bị đảo từ dưới lên trên (sort ngược) ==> câu lệnh trên vẫn chưa phải là lời giải.
Có cách nào sửa câu lệnh SQL để chỉ cần nó lấy được bản ghi nào thì nó sẽ đánh số tự động tăng dần từ 1 đến n. Kể cả khi mình lấy ORDER BY của các cột khác (tùy biến) ??

 

[Hau151978]

Nếu sort ngược lại thì thay dấu >= thành <=.
Nếu duy nhất và chưa sort thì dùng thêm order by nhưng thứ tự bảng không còn như lúc đầu.
Nếu mã không duy nhất thì sql mình bó tay.

 

[VetMini]

...
Nếu mã không duy nhất thì sql mình bó tay.

Theo lý thuyết CSDL Liên Hệ, vị trí của dòng nằm trong bảng là việc không liên hệ. Vì vậy SQL tiêu chuẩn không có lệnh để định vị trí dòng.
Tuy nhiên, vì nhu cầu khách hàng cho nên hầu hết các phiên bản SQL trên thị truonwgf đều có hàm để đánh số dòng.

Riêng MS Access thì lại không có hàm đánh số thứ tự (ít nhất là phiên bản hiện nay). ADO dùng cổ mày Access để thực hiện truy vấn SQL trên bảng tính Excel cho nên phải chịu vậy.

Nếu kết nối với CSDL khác (mySSQL, MS SQL Server,...) thì có thể dùng hàm hoặc lệnh để làm việc này.

 

[loinguyen212]

Nghĩa là việc đánh số thứ tự khi mã không là duy nhất bằng câu lệnh SQL là không thể.
Vậy chắc nếu làm thì phải viết một đoạn code khác để đánh số thứ tự khi đã truy vấn được dữ liệu. Cũng phức tạp quá

 

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

Lọc dữ liệu duy nhất.

Xin chào các bạn, câu hỏi lần này là "Tôi viết câu query như thế nào để lọc duy nhất dữ liệu mà không dùng Group By và select distinct?"

 

[tan.ngohoangquoc]

Xin chào các bạn, câu hỏi lần này là "Tôi viết câu query như thế nào để lọc duy nhất dữ liệu mà không dùng Group By và select distinct?"

Em không biết chơi kiểu gì làm đại kiểu UNION

Sub TestExcel()
 
    Dim cn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    
    cn.Open ("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};dbq=" & ThisWorkbook.FullName & ";")
    rst.Open ("select SUPPLIERS from [Data$] union select SUPPLIERS from [Data$]"), cn
    
    Sheet2.[C2].CopyFromRecordset rst


End Sub