Excel based database : Excel ADO « Excel « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Excel
3. Excel ADO

Excel based database

 
Sub ExcelExample()
    Dim r As Integer, f As Integer
    Dim vrecs As Variant
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim fld As ADODB.Field
    Set cn = New ADODB.Connection
    cn.Provider = "Microsoft OLE DB Provider for ODBC Drivers"
    cn.ConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=C:\mydb.mdb;"
    cn.Open
    Debug.Print cn.ConnectionString
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open "SELECT * FROM Employees", cn, adOpenDynamic, adLockOptimistic
    For Each fld In rs.Fields
        Debug.Print fld.Name,
    Next
    Debug.Print
    vrecs = rs.GetRows(6)
    For r = 0 To UBound(vrecs, 1)
        For f = 0 To UBound(vrecs, 2)
            Debug.Print vrecs(f, r),
        Next
        Debug.Print
    Next
    Debug.Print "adAddNew: " & rs.Supports(adAddNew)
    Debug.Print "adBookmark: " & rs.Supports(adBookmark)
    Debug.Print "adDelete: " & rs.Supports(adDelete)
    Debug.Print "adFind: " & rs.Supports(adFind)
    Debug.Print "adUpdate: " & rs.Supports(adUpdate)
    Debug.Print "adMovePrevious: " & rs.Supports(adMovePrevious)
    
    rs.Close
    cn.Close
    
End Sub

 

Related examples in the same category