Create a Microsoft Access Database Using ADOX and Visual Basic .NET

HOW TO: Create a Microsoft Access Database Using ADOX and Visual Basic .NET

Summary

Programmers may have to create databases programmatically, but neither ActiveX Data Objects (ADO) nor ADO.NET provide the means to create Microsoft Access databases. However, you can create Access databases by using the Microsoft Jet OLE DB Provider and Microsoft ADO Ext. 2.7 for DDL and Security (ADOX) with the COM Interop layer.

Requirements

• Microsoft Visual Basic .NET
• ADO Ext. 2.7 for DDL and Security (ADOX)

This step-by-step example describes how to use ADOX and Visual Basic .NET to create an Access database on the fly.

Steps to Build Example

1. Open a new Visual Basic .NET Console application.
2. In Solution Explorer, right-click the References node, and then click Add Reference.
3. In the Add Reference dialog box, click the COM tab, click Microsoft ADO Ext. 2.7 for DDL and Security, click Select to add it to the Selected Components section, and then click OK.
4. Delete all of the code from the code window for Module1.vb.
5. Copy the following code and paste it in the code window:

Imports ADOX

Module Module1

  Sub Main()

      Dim cat As Catalog = New Catalog()

      cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=D:\AccessDB\NewMDB.mdb;" & _
                 "Jet OLEDB:Engine Type=5")

      Console.WriteLine("Database Created Successfully")

      cat = Nothing

  End Sub

End Module

6. Change the path to the new .mdb file as appropriate. Make sure the folder provided in the path exists. Press F5 to build and run the project.

The new .mdb file is created in Access 2000 (Jet 4.0) format. For a different Jet format, read the following section.

HOWTO: Create a Table with Primary Key Through ADOX

Summary

ADOX is an extension to ActiveX Data Objects that allows the manipulation of the database schema. This section illustrates how to use ADOX to create a table and add a Primary Key.

More Information

Note: Not all OLE DB providers support the interfaces required to support ADOX methods. With those providers, you have to use Data Definition Queries or another object model to manipulate the database schema.

The first procedure in the followingexample creates a new table in an existing Microsoft Access database, creates a new field in that table, then creates a primary key index. When adding a single-field primary key, you do not need to use the ADOX Key object.

The second procedure utilizes the ADOX Key object to add a multiple field key to a table.

Steps to Create the Sample Application

In Microsoft Visual Basic 5.0 or 6.0, create a new Standard EXE project. Form1 is created by default. On the Project menu, select References to add the following type libraries:

• Microsoft ActiveX Data Objects 2.1 Library
• Microsoft ADO Ext. 2.1 for DDL and Security

Add two Command buttons (Command1 and Command2) and the following code to the Form1: Option Explicit:

Private Sub Command1_Click()
'
' This code adds a single-field Primary key
'
Dim Cn As ADODB.Connection, Cat As ADOX.Catalog, _
                            objTable As ADOX.Table

  Set Cn       = New ADODB.Connection
  Set Cat      = New ADOX.Catalog
  Set objTable = New ADOX.Table

  'Open the connection
  Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"

  'Open the Catalog
  Set Cat.ActiveConnection = Cn

  'Create the table
  objTable.Name = "Test_Table"

  'Create and Append a new field to the "Test_Table"
  'Columns Collection
  objTable.Columns.Append "PrimaryKey_Field", adInteger

  'Create and Append a new key. Note that we are merely passing
  'the "PimaryKey_Field" column as the source of the primary key.
  'Thi snew Key will be Appended to the Keys Collection of
  '"Test_Table"
  objTable.Keys.Append "PrimaryKey", adKeyPrimary, _
                       "PrimaryKey_Field"

  'Append the newly created table to the Tables Collection
  Cat.Tables.Append objTable

' clean up objects
  Set objKey = Nothing
  Set objTable = Nothing
  Set Cat = Nothing
  Cn.Close
  Set Cn = Nothing
End Sub

Private Sub Command2_Click()
'
' This code adds a multi-field Primary Key
'
Dim Cn As ADODB.Connection, Cat As ADOX.Catalog
Dim objTable As ADOX.Table, objKey As ADOX.Key

  Set Cn       = New ADODB.Connection
  Set Cat      = New ADOX.Catalog
  Set objTable = New ADOX.Table
  Set objKey   = New ADOX.Key

  Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
  Set Cat.ActiveConnection = Cn
  objTable.Name = "Test_Table2"

  objTable.Columns.Append "PrimaryKey_Field1", adInteger
  objTable.Columns.Append "PrimaryKey_Field2", adInteger

  objKey.Name = "PrimaryKey"
  objKey.Type = adKeyPrimary
  objKey.Columns.Append "PrimaryKey_Field1"
  objKey.Columns.Append "PrimaryKey_Field2"

  objTable.Keys.Append objKey

  Cat.Tables.Append objTable

' clean up objects
  Set objKey   = Nothing
  Set objTable = Nothing
  Set Cat      = Nothing
  Cn.Close
  Set Cn       = Nothing
End Sub

Note: You might have to adjust the connect string to point to a valid Jet database.

Run the application and click the Command buttons. You can check the table definitions for Test_Table and TestTable2 in Microsoft Access 97, Microsoft Access 2000, or the Visual Basic Visual Data Manager add-in.