select and edit a database table in a DBGrid control

[nandagopal]

The DBGrid control's DataSource property is set to the Data control at design time. The DBGrid's other properties are also set to allow the user to edit the data (AllowAddNew, AllowDelete, AllowUpdate). The rest is automatic.

Code Select Expand

Private Sub Form_Load()
Dim dbname As String
Dim db As Database
Dim qdef As QueryDef
Dim td As TableDef

    ' Open the database.
    dbname = App.Path
    If Right$(dbname, 1) <> "\" Then dbname = dbname & "\"
    dbname = dbname & "data.mdb"
    Set db = OpenDatabase(dbname)

    ' List the table names.
    For Each td In db.TableDefs
        ' Do not allow the system tables.
        If Left$(td.Name, 4) <> "MSys" Then _
            List1.AddItem td.Name
    Next td

    db.Close

    ' Attach the Data control to the database.
    Data1.DatabaseName = dbname
End Sub

' Open the selected table.
Private Sub List1_Click()
Dim table_name As String
Dim sql As String
   
    table_name = List1.List(List1.ListIndex)
    sql = "SELECT * FROM " & table_name

    Data1.Caption = table_name
    Data1.RecordSource = sql
    Data1.Refresh

    ' Make the Data and DBGrid controls visible.
    Data1.Visible = True
    DBGrid1.Visible = True
End Sub