Đố vui về ADO, DAO.

ndu96081631 · 29/5/13

Hai Lúa Miền Tây đã viết:
Chính xác là em muốn nói cái này, dùng DAO để lấy, vì ADO nó sẽ tự động sắp xếp theo thứ tự nên không thể lấy chính xác.

Mã:

Sub TestDAO() Dim Dbs As Object, db As Object Set Dbs = CreateObject("DAO.DBEngine.36") Set db = Dbs.OpenDatabase"E:\VBA\LuongTH.xls", False, True, "Excel 8.0;") MsgBox db.TableDefs(0).Name db.Close Set Dbs = Nothing: Set db = Nothing End Sub

Nhân tiện đây xin hỏi là làm sao ta lấy được tất cà tên sheet = DAO?

Ráp vào file bị báo lỗi tại dòng

Mã:

Set db = Dbs.OpenDatabase(FileName, False, True, "Excel 8.0;")

Tình nghi có liên quan đến version office (tôi dùng office 2010) nên sửa số 8.0 thành 12.0 vẫn lỗi!

Hai Lúa Miền Tây · 29/5/13

ndu96081631 đã viết:
Ráp vào file bị báo lỗi tại dòng

Mã:

Set db = Dbs.OpenDatabase(FileName, False, True, "Excel 8.0;")

Tình nghi có liên quan đến version office (tôi dùng office 2010) nên sửa số 8.0 thành 12.0 vẫn lỗi!

Em dùng Office 2013 test nó cho kết quả bình thường.

ndu96081631 · 29/5/13

Hai Lúa Miền Tây đã viết:
Em dùng Office 2013 test nó cho kết quả bình thường.

Vậy Hai Lúa vui lòng "ráp" code vào file ở bài #57 giúp tôi với

dhn46 · 29/5/13

Dựa vào trang này em đưa ra Code sau để lấy tên tất cả các sheet dựa theo DAO

Mã:

Public Sub GetSheetName()
Dim db As Database
Dim tdf As TableDef
   Set db = OpenDatabase("D:\test.xls", False, False, "Excel 8.0;HDR=NO;IMEX=2;")
    For Each tdf In db.TableDefs
      MsgBox tdf.Name
   Next
   Set db = Nothing
End Sub

VetMini · 29/5/13

2 cách dùng ADO mà tôi biết (có thể có các cách khác tôi không biết)

1. Dùng ADO Exlensions của DDLS (ADOX) để đọc Catalog

Mã:

Sub GetSheetNames1()

Dim cn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim t As ADOX.Table

Set cn = New ADODB.Connection
cn.Open "Provider=MSDASQL.1;Data Source=Excel Files;" _
& "Initial Catalog=C:\Junks\Test Book.xls"
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn
For Each t In cat.Tables
Debug.Print t.Name
Next t
Set cat = Nothing
cn.Close
Set cn = Nothing
End Sub

2. Dùng hàm OpenSchema và tham số adSchemaTables để load schema vào một string rồi parse string đó để lấy tên sheet

Mã:

Sub GetSheetNames2()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = GetExcelConnection("C:\Junks\Test Book.xls")
Set rs = cn.OpenSchema(adSchemaTables)

Do While Not rs.EOF
    strTable = rs.Fields("table_name").Value
    If Right$(strTable, 1) = "$" Then ' sheet name
        strWorksheetList = strWorksheetList & vbCrLf & strTable
    Else ' range name
        strRangeList = strRangeList & vbCrLf & strTable
    End If
    rs.MoveNext
Loop
Debug.Print "Worksheets:" & strWorksheetList & vbCrLf & vbCrLf & "Ranges:" & strRangeList
End Sub

Private Function GetExcelConnection(ByVal Path As String, _
    Optional ByVal Headers As Boolean = True) As Connection
    Dim strConn As String
    Dim objConn As ADODB.Connection
    Set objConn = New ADODB.Connection
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & Path & ";" & _
              "Extended Properties=""Excel 8.0;HDR=" & _
              IIf(Headers, "Yes", "No") & """"
    objConn.Open strConn
    Set GetExcelConnection = objConn
End Function

Cả 2 cách đều lấy tên sheet theo thứ tự abc. Làm thế nào để biết sheet đầu tiên thì tôi lười quá chưa nghĩ ra.

Theo tôi biết, Catalog và Schema đọc tên sheet từ ISAM table cho nên bắt buộc phải theo thứ tự index của ISAM.

Hai Lúa Miền Tây · 29/5/13

dhn46 đã viết:
Dựa vào trang này em đưa ra Code sau để lấy tên tất cả các sheet dựa theo DAO

Mã:

Public Sub GetSheetName() Dim db As Database Dim tdf As TableDef Set db = OpenDatabase("D:\test.xls", False, False, "Excel 8.0;HDR=NO;IMEX=2;") For Each tdf In db.TableDefs MsgBox tdf.Name Next Set db = Nothing End Sub

Còn phải xử lý bỏ qua name, vùng lọc, vùng in, tiêu đề cần lặp lại...

ndu96081631 · 29/5/13

Tôi biết vấn đề nằm ở đâu rồi
Code lấy Sheet đầu tiên dùng DAO phải đặt trước dòng sCon.Open szConnect mới xong

Hai Lúa Miền Tây · 29/5/13

VetMini đã viết:
2 cách dùng ADO mà tôi biết (có thể có các cách khác tôi không biết)

1. Dùng ADO Exlensions của DDLS (ADOX) để đọc Catalog

Mã:

Sub GetSheetNames1() Dim cn As ADODB.Connection Dim cat As ADOX.Catalog Dim t As ADOX.Table Set cn = New ADODB.Connection cn.Open "Provider=MSDASQL.1;Data Source=Excel Files;" _ & "Initial Catalog=C:\Junks\Test Book.xls" Set cat = New ADOX.Catalog Set cat.ActiveConnection = cn For Each t In cat.Tables Debug.Print t.Name Next t Set cat = Nothing cn.Close Set cn = Nothing End Sub

2. Dùng hàm OpenSchema và tham số adSchemaTables để load schema vào một string rồi parse string đó để lấy tên sheet

Mã:

Sub GetSheetNames2() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Set cn = GetExcelConnection("C:\Junks\Test Book.xls") Set rs = cn.OpenSchema(adSchemaTables) Do While Not rs.EOF strTable = rs.Fields("table_name").Value If Right$(strTable, 1) = "$" Then ' sheet name strWorksheetList = strWorksheetList & vbCrLf & strTable Else ' range name strRangeList = strRangeList & vbCrLf & strTable End If rs.MoveNext Loop Debug.Print "Worksheets:" & strWorksheetList & vbCrLf & vbCrLf & "Ranges:" & strRangeList End Sub Private Function GetExcelConnection(ByVal Path As String, _ Optional ByVal Headers As Boolean = True) As Connection Dim strConn As String Dim objConn As ADODB.Connection Set objConn = New ADODB.Connection strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Path & ";" & _ "Extended Properties=""Excel 8.0;HDR=" & _ IIf(Headers, "Yes", "No") & """" objConn.Open strConn Set GetExcelConnection = objConn End Function

Cả 2 cách đều lấy tên sheet theo thứ tự abc. Làm thế nào để biết sheet đầu tiên thì tôi lười quá chưa nghĩ ra.

Theo tôi biết, Catalog và Schema đọc tên sheet từ ISAM table cho nên bắt buộc phải theo thứ tự index của ISAM.

ADO sẽ không lấy tên sheet như DAO, đó là vấn đề mình muốn nói ở đây.

dhn46 · 29/5/13

Hai Lúa Miền Tây đã viết:
Còn phải xử lý bỏ qua name, vùng lọc, vùng in, tiêu đề cần lặp lại...

ADO phức tạp thậy đấy anh Hai lúa ah, e sửa lại 1 chút anh xem như thế nào nhé

Mã:

Public Sub GetSheetName()
Dim db As DAO.Database
Dim tdf As TableDef
   Set db = OpenDatabase("D:\test.xls", False, True, "Excel 8.0;")
    For Each tdf In db.TableDefs
      If Right(tdf.Name, 1) = "$" Or Right(tdf.Name, 2) = "$'" Then
        MsgBox tdf.Name
      End If
   Next
   Set db = Nothing
End Sub

Hai Lúa Miền Tây · 29/5/13

dhn46 đã viết:
ADO phức tạp thậy đấy anh Hai lúa ah, e sửa lại 1 chút anh xem như thế nào nhé

Mã:

Public Sub GetSheetName() Dim db As DAO.Database Dim tdf As TableDef Set db = OpenDatabase("D:\test.xls", False, True, "Excel 8.0;") For Each tdf In db.TableDefs If Right(tdf.Name, 1) = "$" Or Right(tdf.Name, 2) = "$'" Then MsgBox tdf.Name End If Next Set db = Nothing End Sub

Chính xác là vậy rồi, nhưng nên bỏ tham chiếu đến nó.

Mã:

Sub LayTenSheet()
    Dim Dbs  As Object, db As Object, tbl As Object
    Set Dbs = CreateObject("DAO.DBEngine.36")
    Set db = Dbs.OpenDatabase("D:\test.xls", False, True, "Excel 8.0;")
    For Each tbl In db.TableDefs
        If Right(tbl.Name, 1) = "$" Or Right(tbl.Name, 2) = "$'" Then
            MsgBox tbl.Name
        End If
    Next tbl
    db.Close
    Set Dbs = Nothing: Set db = Nothing: Set tbl = Nothing
    
End Sub

ndu96081631 · 29/5/13

dhn46 đã viết:
ADO phức tạp thậy đấy anh Hai lúa ah, e sửa lại 1 chút anh xem như thế nào nhé

Mã:

Public Sub GetSheetName() Dim db As DAO.Database Dim tdf As TableDef Set db = OpenDatabase("D:\test.xls", False, True, "Excel 8.0;") For Each tdf In db.TableDefs If Right(tdf.Name, 1) = "$" Or Right(tdf.Name, 2) = "$'" Then MsgBox tdf.Name End If Next Set db = Nothing End Sub

Bài 57 có xử lý rồi đấy

Mã:

If Right(SheetName, 1) = "'" Then SheetName = Mid(SheetName, 2, Len(SheetName) - 2)
  If Right(SheetName, 1) <> "$" Then SheetName = SheetName & "$"

------------------------
Bạn lưu ý đừng bao giờ khai báo biến theo kiểu db As DAO.Database, tdf As TableDef... vì chỉ máy bạn mới chạy đuọc còn máy người khác thì... thua (ai biết bạn đã check References nào chứ)
Khai báo kiểu vầy mới chắc ăn:

Mã:

Dim Dbs  As Object, db As Object
Set Dbs = [COLOR=#ff0000]CreateObject("DAO.DBEngine.36")[/COLOR]
Set db = Dbs.OpenDatabase(FileName, False, False, "Excel 8.0;")
SheetName = db.TableDefs(0).Name

Bảo đảm máy nào cũng chạy được

Hai Lúa Miền Tây · 29/5/13

ndu96081631 đã viết:
Bài 57 có xử lý rồi đấy

Vậy Thầy nên trả lời bài #58 ở đề tài bên đó và đưa file đính kèm cho mọi người học hỏi thêm.

VetMini · 29/5/13

Hai Lúa Miền Tây đã viết:
ADO sẽ không lấy tên sheet như DAO, đó là vấn đề mình muốn nói ở đây.

Đồng ý.
Theo lý thuyết CSDL Liên Hệ thì thứ tự của dòng không quan trọng. Khi cần theo thứ tự nào thì người ta ORDER BY the thứ tự nấy. Nếu không có ORDER thì hầu hết các CSDL LH mặc định thứ tự theo khoá chính.
ADO là đối tượng bậc cao (high level) nên nó tuỳ thuộc vào engine vòng trong (low level)
DAO là đối thượng bậc thấp (low level) nên nó theo sát với dữ liệu thô hơn.

Vì vậy, có lẽ DAO là cách duy nhất để biệt thực sự sheet nào được đặt ở đầu.

sealand · 29/5/13

Mã:
Bạn lưu ý đừng bao giờ khai báo biến theo kiểu db As DAO.Database, tdf As TableDef... vì chỉ máy bạn mới chạy đuọc còn máy người khác thì... thua (ai biết bạn đã check References nào chứ)
Khai báo kiểu vầy mới chắc ăn:
Code:

Mã:

Dim Dbs As Object, db As Object Set Dbs = [COLOR=#ff0000]CreateObject("DAO.DBEngine.36")[/COLOR] Set db = Dbs.OpenDatabase(FileName, False, False, "Excel 8.0;") SheetName = db.TableDefs(0).Name

Bảo đảm máy nào cũng chạy được

Mình thấy rằng việc khai báo đến thư viện của DAO là cần thiết, nhất là đối với những người đang học tập hay tìm tòi như chúng ta. Chính Topic này cũng theo mục đích đó. Như vậy, nó sẽ hỗ trợ chúng ta trong viết Code chính xác, tham vấn trực tiếp được đến các thuộc tính, phương pháp hay cú pháp chuẩn của nó.
Để khắc phục tình trạng sang máy khác không hiểu ta thêm 1 đoạn Code tự xác định tham chiếu đến thư viện nếu nhỡ trong file nào đó không tham chiếu như sau:

Mã:

Public Sub AutRef()
On Error Resume Next
Application.VBE.ActiveVBProject.References.AddFromFile _
"C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"
End Sub

Còn đương nhiên cách viết của Ndu là gọn nhưng nó phù hợp cho ai nắm chắc cú pháp viết Code hoặc chép Code , còn người đang học mất hẳn tính năng "nha nhá' gợi ý.
Hoặc ta cứ tham chiếu để viết Code, bao giờ xong ta chuyển sang dạng CreateObject như Ndu.

VetMini · 30/5/13

sealand đã viết:
Mình thấy rằng việc khai báo đến thư viện của DAO là cần thiết, nhất là đối với những người đang học tập hay tìm tòi như chúng ta. Chính Topic này cũng theo mục đích đó. Như vậy, nó sẽ hỗ trợ chúng ta trong viết Code chính xác, tham vấn trực tiếp được đến các thuộc tính, phương pháp hay cú pháp chuẩn của nó.
Để khắc phục tình trạng sang máy khác không hiểu ta thêm 1 đoạn Code tự xác định tham chiếu đến thư viện nếu nhỡ trong file nào đó không tham chiếu như sau:

Mã:

Public Sub AutRef() On Error Resume Next Application.VBE.ActiveVBProject.References.AddFromFile _ "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll" End Sub

Còn đương nhiên cách viết của Ndu là gọn nhưng nó phù hợp cho ai nắm chắc cú pháp viết Code hoặc chép Code , còn người đang học mất hẳn tính năng "nha nhá' gợi ý.
Hoặc ta cứ tham chiếu để viết Code, bao giờ xong ta chuyển sang dạng CreateObject như Ndu.

Đề mục này thuộc về dữ liệu, ADO và DAO đều là công cụ kết nối dữ liệu (data linking), và nếu mở rộng hơn thì sẽ bao gồm chuyển đổi dữ liệu (data migration).

Trên tinh thần đó, chuyện sử dụng dữ liệu trong dataset/recordset mới là chính. Chuyện đoạn code thực hiện lệnh kết nối là chuyện phụ, nó chỉ dùng để test code, cú pháp hầu như không quan trọng. Người sử dụng chỉ cần biết điều kiện kết nối để có thể thực hiện truy vấn hiệu quả.

Thực tế trong môi trường làm việc, người ta đặt những đoạn code hạ tầng cơ sở này vào một wrapper class/module (xem chú thích 1). Gặp môi trường khác nhau thì chỉ việc sửa đổi code trong các class/module này. Code làm việc với dữ liệu đưa tham số nguồn (vd tên file) và gọi hàm kết nối với nguồn, đưa tham số truy vấn (ví dụ câu lệnh SQL) và gọi hàm đọc dữ liệu.

Chính vì có điểm lợi tách rời code sử dụng dũ liệu riêng với code hạ tầng cơ sở này mà ADO mới ra đời. Trên nguyên tắc, DAO (xem chú thích 2) làm việc với Access và Jet engines hữu hiệu hơn. Mặt khác, ADO có khả năng kết nối rộng hơn với các loại nguồn khác.

Chú thích:
(1) theo ngôn ngữ tổng quát thì các code modules đều có thể gọi là class - module chúng ta hay dùng là class tĩnh, sử dụng các hàm và phương thức không cần phải qua đối tuợng.
(2) hình như khi nói đến DAO, chúng ta dùng phiên bản 3.6; code của các bạn có nêu như vậy.

Hai Lúa Miền Tây · 30/5/13

Ví dụ tôi có 2 file có đường dẫn là chung 1 folder, file A.xls là file chứa dữ liệu, file còn lại là B.xls.
Xin hỏi: Làm thế nào không mở file B.xls mà ta tạo ra 14 sheet và chuyển dữ liệu từ sheet Data của file A.xls với điều kiện là số liệu của sheet 1 đến 14 được lọc tương ứng ở cột STT vào file B.xls (Tức là sheet 1 thì chỉ chứa STT là 1, sheet 2 thì chỉ chứa STT là 2... sheet 14 thì chỉ chứa STT là 14)

dhn46 · 30/5/13

Hai Lúa Miền Tây đã viết:
Ví dụ tôi có 2 file có đường dẫn là chung 1 folder, file A.xls là file chứa dữ liệu, file còn lại là B.xls.
Xin hỏi: Làm thế nào không mở file B.xls mà ta tạo ra 14 sheet và chuyển dữ liệu từ sheet Data của file A.xls với điều kiện là số liệu của sheet 1 đến 14 được lọc tương ứng ở cột STT vào file B.xls (Tức là sheet 1 thì chỉ chứa STT là 1, sheet 2 thì chỉ chứa STT là 2... sheet 14 thì chỉ chứa STT là 14)

Em cũng muốn tìm hiểu ADO nên mạnh dạn đưa lời giải đầu tiên, có gì sai sót anh góp ý nhé.

Mã:

Option Explicit
Sub CopyDL()
Dim i As Long
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 & "\A.XLS" & _
                        ";Extended Properties=""Excel 8.0;HDR=YES;"";"
    .Open
End With
    
For i = 1 To 14
    lsSQL = "select [Data$A1:D30].* INTO Data" & i & " IN '" & ThisWorkbook.Path & _
                 "\B.xls ' 'Excel 8.0;'  FROM [Data$A1:D30] where Stt=" & i
                 
    lrs.Open lsSQL, cnn, 3, 1
Next
    
Set lrs = Nothing
cnn.Close: Set cnn = Nothing
End Sub

Hai Lúa Miền Tây · 30/5/13

dhn46 đã viết:

Em cũng muốn tìm hiểu ADO nên mạnh dạn đưa lời giải đầu tiên, có gì sai sót anh góp ý nhé.

Mã:

Option Explicit
Sub CopyDL()
Dim i As Long
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 & "\A.XLS" & _
                        ";Extended Properties=""Excel 8.0;HDR=YES;"";"
    .Open
End With
    
For i = 1 To 14
    lsSQL = "select [Data$A1:D30].* INTO Data" & i & " IN '" & ThisWorkbook.Path & _
                 "\B.xls ' 'Excel 8.0;'  FROM [Data$A1:D30] where Stt=" & i
                 
    lrs.Open lsSQL, cnn, 3, 1
Next
    
Set lrs = Nothing
cnn.Close: Set cnn = Nothing
End Sub

Đúng là như vậy, không làm khó được bạn. Thật ra nó nằm ở #9

hungpecc1 · 30/5/13

Hai Lúa Miền Tây đã viết:
Đúng là như vậy, không làm khó được bạn. Thật ra nó nằm ở #9

Nếu bây giờ muốn xác định được giá trị 14 ( trong câu lệnh for next )thì phải dùng câu lệnh SQL như thế nào anh nhỉ ?
Không lẽ lại viết:

PHP:

Lrs.Open "SELECT DISTINCT STT FROM [DATA$],cnn,3,1
While i < = Lrs.RecordCount
.......................
Wend

Trong SQL có hàm Count mà em không biết đưa câu lệnh : "SELECT DISTINCT COUNT(STT) FROM [DATA$] vào VBA như thế nào để lấy giá trị 14?<------------ +-+-+-+

Hai Lúa Miền Tây · 30/5/13

hungpecc1 đã viết:
Nếu bây giờ muốn xác định được giá trị 14 ( trong câu lệnh for next )thì phải dùng câu lệnh SQL như thế nào anh nhỉ ?
Không lẽ lại viết:

PHP:

Lrs.Open "SELECT DISTINCT STT FROM [DATA$],cnn,3,1 While i < = Lrs.RecordCount ....................... Wend

Trong SQL có hàm Count mà em không biết đưa câu lệnh : "SELECT DISTINCT COUNT(STT) FROM [DATA$] vào VBA như thế nào để lấy giá trị 14?<------------

Trong trường hợp muốn lấy giá trị cao nhất của cột STT thì bạn có thể dùng

"select top 1 stt From [Data$] order by stt desc"

Đố vui về ADO, DAO.

Huyền thoại GPE

❆❆❆❆❆❆❆❆

Huyền thoại GPE

Hướng tới tương lai

Ăn cùng góc phố

❆❆❆❆❆❆❆❆

Huyền thoại GPE

❆❆❆❆❆❆❆❆

Hướng tới tương lai

❆❆❆❆❆❆❆❆

Huyền thoại GPE

❆❆❆❆❆❆❆❆

Ăn cùng góc phố

Thành viên gạo cội

Ăn cùng góc phố

❆❆❆❆❆❆❆❆

File đính kèm

Hướng tới tương lai

❆❆❆❆❆❆❆❆

Thành viên gắn bó

❆❆❆❆❆❆❆❆