Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleBuild an Access database and a table with records using ADOX
Keywordsbuild database, DB, ADO, ADOX, make table, Access
CategoriesDatabase
 
First, open the Project menu and select References. Find the "Microsoft ADO Ext. 2.6 for DLL and Security" entry (or whatever version you have) and check it.

To create the database, make a new ADOX.Catalog object and call its Create method.

To make a table, create a new ADOX.Table object. Use its Columns collection's Append method to define the table's fields. When you are finished, add the Table object to the Catalog's Tables collection.

Now you're back to normal ADO. Use the Catalog's ActiveConnection to get a reference to the database's connection. Use the Connection's Execute method to perform SQL INSERT statements to make records.

When you're finished, close the Connection and set the Connection, Table, and Catalog object references to Nothing.

 
Private Sub cmdCreate_Click()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim con As ADODB.Connection

    ' Delete the database if it already exists.
    On Error Resume Next
    Kill txtDatabaseName.Text
    On Error GoTo 0

    ' Create the new database.
    Set cat = New ADOX.Catalog
    cat.Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & txtDatabaseName.Text & ";"

    ' Create a new table.
    Set tbl = New ADOX.Table
    tbl.Name = "TestTable"
    tbl.Columns.Append "FirstName", adVarWChar, 40
    tbl.Columns.Append "LastName", adVarWChar, 40
    tbl.Columns.Append "Birthdate", adDate
    tbl.Columns.Append "Weight", adInteger
    cat.Tables.Append tbl

    ' Connect to the database.
    Set con = cat.ActiveConnection

    ' Insert records.
    con.Execute "INSERT INTO TestTable VALUES ('Andy', " & _
        "'Able', '1 Jan 1980', '150')"
    con.Execute "INSERT INTO TestTable VALUES ('Betty', " & _
        "'Baker', #2/22/1990#, 70)"

    ' Close the database connection.
    con.Close
    Set con = Nothing
    Set tbl = Nothing
    Set cat = Nothing

    MsgBox "Done"
End Sub
 
See my book Visual Basic .NET Database Programming for information on database programming in VB .NET.
 
 
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated