CodeItBetter Programming Another VB Programming Blog

'Database - How to Use ADO to make a database table with default column values
'When the program creates new records, it can omit the field to get the default value.

Private Sub Command1_Click()
    Dim db_file As String
    Dim statement As String
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
 
    ' Get the data.
    db_file = App.Path
    If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
    db_file = db_file & "books.mdb"
 
    ' Open a connection.
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & db_file & ";" _
        & "Persist Security Info=False"
    conn.Open
 
    ' Create the new table.
    statement = "CREATE TABLE NewTable (" & "Comment        TEXT, " & _
        "NumericField   LONG        DEFAULT -1, " & _
        "TextField      TEXT(10)    DEFAULT '<unknown>'" & ")"
    conn.Execute statement
 
    ' Create some records.
    conn.Execute "INSERT INTO NewTable (Comment, " & _
        "NumericField, TextField) VALUES ('Entered Both', " & "12345, 'Hello')"
    conn.Execute "INSERT INTO NewTable (Comment, TextField) " & _
        "VALUES ('Numeric Missing', 'Hello')"
    conn.Execute "INSERT INTO NewTable (Comment, " & "NumericField) VALUES ('Text Missing', 12345)"
    conn.Execute "INSERT INTO NewTable (Comment) VALUES " & "('Both Missing')"
 
    ' Get the records.
    Set rs = conn.Execute("SELECT * FROM NewTable", , adCmdText)
    List1.Clear
    Do While Not rs.EOF
        List1.AddItem rs!Comment & ", " & rs!NumericField & ", " & rs!TextField
        rs.MoveNext
    Loop
    rs.Close
 
    ' Drop the table.
    conn.Execute "DROP TABLE NewTable"
 
    conn.Close
End Sub