Connect to the database. Then use the Connection object's Execute method to execute an SQL CREATE TABLE statement.

Private Sub Command1_Click()
Dim db_file As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim num_records As Integer

    ' Get the database name.
    db_file = App.Path
    If Right$(db_file, 1) "\" Then db_file = db_file & _
        "\"
    db_file = db_file & "People.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

    ' Drop the Employees table if it already exists.
    On Error Resume Next
    conn.Execute "DROP TABLE Employees"
    On Error GoTo 0

    ' Create the Employees table.
    conn.Execute _
        "CREATE TABLE Employees(" & _
            "EmployeeId INTEGER      NOT NULL," & _
            "LastName   VARCHAR(40) NOT NULL," & _
            "FirstName VARCHAR(40) NOT NULL)"

    ' Populate the table.
    conn.Execute "INSERT INTO Employees VALUES (1, " & _
        "'Anderson', 'Amy')"
    conn.Execute "INSERT INTO Employees VALUES (1, 'Baker', " & _
        "   'Betty')"
    conn.Execute "INSERT INTO Employees VALUES (1, 'Cover', " & _
        "   'Chauncey')"
    ' Add more records ...

    ' See how many records the table contains.
    Set rs = conn.Execute("SELECT COUNT (*) FROM Employees")
    num_records = rs.Fields(0)

    conn.Close

    MsgBox "Created " & num_records & " records", _
        vbInformation, "Done"
End Sub

Comments

Add New