CodeItBetter Programming Another VB Programming Blog

'Database - How to use ADO to create a database table
'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
 
'Using this method, you can perform all database creation tasks. You can drop,
'create, and modify table. The one thing you cannot do is create the database
'itself. You can, however, copy an existing database, remove all of its tables,
'and then create the tables you need.