Retrieve number of fields and rows from Access Database

Private Sub Command1_Click()
    Dim strConnection As String
    Dim cn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim rs As New ADODB.Recordset
    Dim r As Long
    Dim strDatabasePath As String
    strDatabasePath = "C:\MyFolder\MyDatabase.mdb"
    Const TableName = "MyTable"
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabasePath & ";Persist Security Info=False"
    cat.ActiveConnection = strConnection
    MsgBox "Field Count = " & cat.Tables(TableName).Columns.Count
    cn.Open strConnection
    rs.Open TableName, cn, adOpenDynamic, adLockPessimistic, adCmdTable
    Do Until rs.EOF
        r = r + 1
        rs.MoveNext
    Loop
    MsgBox "Record count =" & r
    rs.Close
    cn.Close
End Sub

Select all Open in new window