Create a Database

using dbText (bound) and
dbDate (unbound) Field Types
(with MaskedEditBox)

by Rick Meyer

Note: This demo also shows how to use the date field type adDate and how to create a nullable field (to allow a zero length string) adColNullable.
1. Start a new standard exe.
2. In Project->Components add the ADO Control
3. In Project->References add Microsoft ADO Ext 2.0 for DLL & Security
4. In Project->References add Microsoft ActiveX Data Objects 2.1 Library
5. In Project->References add Microsoft ActiveX Data Objects Recordset 2.1 Library
6. On Form1 put an ADO Data Control.
7. On Form1 put a Commandbutton named Command1.
8. On Form1 put a Textbox named Text1.
        (Set the Text1 DataSource property to Data1)
9. On Form1 put 2 MaskEdBoxes named MaskEdBox1-2.
        (Set the MaskEdBox2 DataSource property to Data1)

      Form1 should look like the picture above.

Now you are ready for the code. Select all of the following code (by clicking on the word Option three times) and copy it to the clipboard with [Ctrl][Insert]. Then paste it into the code window of Form1 with [Shift][Insert].
When you run the program, you will enter a name, a date, and a phone number. Then click the commandbutton to enter a new record. Then to see all the records you have entered, click the left arrow on the Data Control. 
Option Explicit

Private Sub Form_Load()
    Dim dbName$, tbName$
    Dim isNew As Boolean

    dbName = "c:\Txt1DB.mdb"
    tbName = "test"
    
    'Delete statement used for testing create
    'If Dir(dbName) <> "" Then Kill dbName
    
    If Dir(dbName) = "" Then
        Dim cn$
        Dim cat As New ADOX.Catalog
        Dim tbl As New ADOX.Table
        
        isNew = True
        
        cn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source = " & dbName & ";"
        cat.Create cn
        
        With tbl
            .Name = tbName
            'This field is a Text type
            .Columns.Append "Name", adVarWChar
            .Columns!Name.Attributes = adColNullable
            'This field is a Date type
            .Columns.Append "Date", adDate
            .Columns!Date.Attributes = adColNullable
            'This field is a Text type
            .Columns.Append "Phone", adVarWChar
            .Columns!Phone.Attributes = adColNullable
        End With
        
        cat.Tables.Append tbl
        Set tbl = Nothing
        Set cat = Nothing
    End If
        
    With ISGData1
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .DataSource = dbName & ";"
        .SQL = "SELECT * FROM " & tbName
        .Refresh
        If isNew Then .AddNew
    End With
    
    Text1.DataField = "Name"     'Set Text1 bound field
    
    With MaskEdBox1
        .Format = "mm/dd/yy"
        .Mask = "##/##/##"
        .MaxLength = 8
    End With
    With MaskEdBox2
        .DataField = "Phone"     'Set bound field
        .Mask = "(###) ###-####"
        .MaxLength = 14
    End With
    Command1.Caption = "AddNew"
End Sub


Private Sub Form_Unload(Cancel As Integer)
    ISGData1.First
    ISGData1.UpdateRecord
End Sub

Private Sub ISGData1_Reposition()
    If IsDate(ISGData1.Recordset!Date) Then
        MaskSet MaskEdBox1, Format(ISGData1.Recordset!Date, "mm/dd/yy")
    Else
        MaskSet MaskEdBox1, ""
    End If
End Sub


'MaskEdBox1 is not bound, so we must write the field
' if Data1 was clicked while on the MaskEdBox1 field
' since MaskEdBox1_LostFocus is not fired
Private Sub ISGData1_ValidateAction(Action As Integer, Save As Integer)
    If Action < 5 Then
        If IsDate(MaskEdBox1.Text) Then
            ISGData1.Recordset!Date = _
                CDate(MaskEdBox1.Text)
        End If
    End If
End Sub


'MaskEdBox1 is not databound so we must provide for
' updating the database separately for this control
' Note this is not a text field
Private Sub MaskEdBox1_LostFocus()
    If IsDate(MaskEdBox1.Text) Then
        ISGData1.Recordset!Date = _
            CDate(MaskEdBox1.Text)
    Else
        MaskSet MaskEdBox1, ""
    End If
End Sub

'Note to blank a MaskEditBox we must first blank
' the mask and then restore it
Private Sub MaskSet(b As MaskEdBox, d As String)
    Dim m$
    m = b.Mask
    b.Mask = ""
    b.Text = d
    b.Mask = m
End Sub

Private Sub Command1_Click()
    ISGData1.Last
    ISGData1.AddNew
    MaskSet MaskEdBox2, ""
    Text1.SetFocus
End Sub
Examine the Form_Load sub to see how creating the database is actually accomplished.

The If Dir(dbName) statement determines if the database file already exists so it isn't recreated. Then it goes through the basic three step process of creating the database, creating a table, and finally creating fields in the table.

Note that the second data field is not a text field. It is a date field. All the bound data entry controls use text fields, so this demo requires special code to ensure that the unbound MaskedEditBox entries will be added to the database.