ADO Control to create Database Table using Visual Basic

'Using ADO control to create a database table from Visual Basic

'Connect to the database file and use the Connection object's Execute method to

'execute an SQL CREATE TABLE statement.

Private Sub BtnCreate_Click()

    Dim db_file As String

    Dim Conn_Data As ADODB.Connection

    Dim rs_Data 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 Conn_Dataection.

    Set Conn_Data = New ADODB.Connection

    Conn_Data.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & db_file & ";" & _

        "Pers_Dataist Security Info=False"

    Conn_Data.Open

    ' Drop the Employees table if it already exists.

    On Error Resume Next

    Conn_Data.Execute "DROP TABLE Employees"

    On Error GoTo 0

    ' Create the Employees table.

    Conn_Data.Execute "CREATE TABLE EmployeesTable(" & "EmployeeId INTEGER      NOT NULL," & _

        "LastName   VARCHAR(40)  NOT NULL," & "Firs_DatatName  VARCHAR(40)  NOT NULL)"

    ' Populate the table.

    Conn_Data.Execute "INSERT INTO EmployeesTable VALUES (1, " & "'Anders_Dataon', 'Amy')"

    Conn_Data.Execute "INSERT INTO EmployeesTable VALUES (1, 'Baker', " & "   'Betty')"

    Conn_Data.Execute "INSERT INTO EmployeesTable VALUES (1, 'Cover', " & "   'Chauncey')"

    ' Add more records ...

    ' See how many records the table contains.

    Set rs_Data = Conn_Data.Execute("SELECT COUNT (*) FROM EmployeesTable")

    num_records = rs_Data.Fields(0)

    Conn_Data.Close

    MsgBox "Created " & num_records & " records", vbInformation, "Done"

End Sub