Open Any .MDB Database to find Tables and Fields

This project uses only the controls that appear on the default toolbox, with the addition of references to the ADO and ADOX libraries, and is designed to be as simple as possible. There is a DriveList, DirList and FileList box, connected by code to allow you to select a database, then the tables, fields and field contents are shown in threee list boxes. It can be downloaded from here

When the project starts the Form_Load event sets the drive/dir/file listboxes up and specifies the provider for the ADO connection:

Private Sub Form_Load()
    'set provider as jet 4. This will also open Access 97 databases
    dbsCon.Provider = "Microsoft.jet.oledb.4.0"

    Drive1.Drive = "C:"
    Dir1.Path = "\My Documents"
    File1.Pattern = "*.mdb"
End Sub

The link between the Drive / Dir listboxes is:

Private Sub Dir1_Change()
    File1.Path = Dir1.Path
End Sub

Private Sub Drive1_Change()
    Dir1.Path = Drive1.Drive
End Sub

When a file is selected the database is opened to discover its structure. If a password is required an input box is displayed to allow it to be entered:

Private Sub File1_Click()
    Dim strPassword As String
    
    On Error Resume Next
    'close connection if already open
    dbsCon.Close
    
    On Error GoTo PasswordErr
    'try to open database
    dbsCon.ConnectionString = "data source=" & Dir1.Path & "\" & File1.FileName
    dbsCon.Open
    
    'set catalog to view structure
    Set cat.ActiveConnection = dbsCon
    
    'clear listboxes
    List1.Clear
    List2.Clear
    List3.Clear
    For Each tbl In cat.Tables
        'ensure Non-System tables only
        If tbl.Type = "TABLE" And InStr(1, tbl.Name, "MSys", vbTextCompare) = 0 Then
            'Add Table Name
            List1.AddItem tbl.Name
        End If
    Next tbl
    
    Exit Sub
    
PasswordErr:
    If Err.Number = -2147217843 Then 'wrong password
        strPassword = InputBox("Enter password for this database. Leave blank to Cancel")
        If strPassword <> "" Then
            dbsCon.Properties("Jet OLEDB:database Password").Value = strPassword
            Resume
        End If
    End If
End Sub

If you click on a table name in the tables listbox, the following code will display the fields present in the selected table

Private Sub List1_Click()
    List2.Clear
    List3.Clear
    For Each col In cat.Tables(List1.Text).Columns
        'Add Field names,Type and size
        List2.AddItem col.Name
    Next col
End Sub

Similarly, clicking in the fields listbox uses the following code to display the data in the selected fields

Private Sub List2_Click()
    'open a recordset (Table-type)
    rst.Open cat.Tables(List1.Text).Name, dbsCon, adOpenDynamic, adLockReadOnly, adCmdTableDirect
    List3.Clear
    If Not rst.BOF Then
        rst.MoveFirst
        Do While Not rst.EOF
            List3.AddItem rst.Fields(cat.Tables(List1.Text).Columns(List2.Text).Name).Value & ""
            rst.MoveNext
        Loop
    Else
        List3.AddItem "No Records"
    End If
    rst.Close
End Sub

Homepage