How To Create a Table with Primary Key Through ADOX

Article translations Article translations
Article ID: 252908 - View products that this article applies to.
This article was previously published under Q252908
Expand all | Collapse all

On This Page

SUMMARY

ADOX is an extension to ActiveX Data Objects that allows the manipulation of the database schema. This article 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 example below 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

  1. In Microsoft Visual Basic 5.0 or 6.0, create a new Standard EXE project. Form1 is created by default.
  2. 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
  3. 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. This
      'new 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.
  4. 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.

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
317867 HOW TO: Create a Microsoft Access Database Using ADOX and Visual Basic .NET
305271 How To Perform Paging with the DataGrid Windows Control by Using Visual Basic .NET

Properties

Article ID: 252908 - Last Review: July 1, 2004 - Revision: 3.4
APPLIES TO
  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft ActiveX Data Objects 2.6
  • Microsoft ActiveX Data Objects 2.7
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
Keywords: 
kbhowto kbjet KB252908

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com