Custom Search
 


How to use ADOX to create unique composite index - the VBA approach



In article how to create a composite unique index (not as a primary key), we have looked at how to manually create a composite unique index in Access. Here in this article, we'll see how to use ADOX objects (by VBA coding) to create the exact same composite unique index in Access.

The purpose is to give a practical view about some ADOX intrinsics, especially when manually building and modifying Access database schema is not possible or very inconvenient. Using ADOX can seamlessly achieve some complex tasks which otherwise would require the end-user to manually create or modify schema objects (tables, indexes, keys, etc.). So coding some ADOX will definitely give the end-user better experience in our MS Access applications.

ADOX is Microsoft's ActiveX Data Objects (ADO) Extensions for database schema creation, modification, as well as security management.

If you are not familiar with ADOX, here is Microsoft help document about "ADOX Fundamentals".

To use ADOX in Access VBA, you need to add reference to two Microsoft libraries. In VBA editor, click Tools -> References...

  1. Microsoft ActiveX Data Objects Library (any version from 2.0 to 2.8).
  2. Microsoft ADO Ext. for DDL and Security (version 2.5 or above).

There are two code examples showing below. The first one explains how to create the composite unique index in an existing table in Access. The second one explains how to create the table and then create the composite unique index in this new table.

Example 1: Creates unique composite index in an existing table.

This sub-routine creates the composite unique index in an existing table.

Private Sub cmdAdd_Click()
    On Error Goto Catch
    
    Dim cnn As ADODB.Connection
    Dim cat As ADOX.Catalog
    Dim idx As ADOX.Index    

    '' Create the composite unique index
    Set idx = New ADOX.Index
    With idx
        .Name = "Uidx_Products"
        .IndexNulls = adIndexNullsAllow
        .PrimaryKey = False
        .Unique = True

        .Columns.Append "Product_Name"
        .Columns.Append "Category_ID"
    End With

    '' Create the connection object
    Set cnn = CurrentProject.Connection
    
    '' Create the catalog object.
    Set cat = New ADOX.Catalog
    
    '' Assign connection to catalog object.
    Set cat.ActiveConnection = cnn
    
    '' Use catalog object to append the new index to the existing table.
    cat.Tables("Table1").Indexes.Append idx
    
    '' Insert some records for testing.
    cnn.Execute "INSERT INTO Table1(Product_Name, Category_ID) values ('Chocolade', 1)"
    cnn.Execute "INSERT INTO Table1(Product_Name, Category_ID) values ('Cheese', 2)"
    cnn.Execute "INSERT INTO Table1(Product_Name, Category_ID) values ('Chocolade', 2)"

    '' Close connection and release resources.
    cnn.Close
    Set cnn = Nothing
    Set cat = Nothing
    Set idx = Nothing
    
    MsgBox "Composite unique index has been successfully created in Table1."
    
    Exit Sub
    
Catch:
    MsgBox "cmdAdd_Click()" & vbCrLf & vbCrLf & " Error: " & Err.Description
End Sub

Example 2: Creates unique composite index in a new table.

This sub-routine creates a table and then create the composite unique index in this table.

Private Sub cmdCreate_Click()
    On Error Goto Catch
    
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim col As New ADOX.Column
    Dim cnn As ADODB.Connection
    Dim idx As ADOX.Index
    
    '' Connect to current database.
    Set cnn = CurrentProject.Connection
    
    '' Create the catalog object.
    Set cat = New ADOX.Catalog
    
    '' Assign connection to catalog object.
    Set cat.ActiveConnection = cnn
        
    '' Create a new table call Table2.
    Set tbl = New ADOX.Table
    tbl.Name = "Table2"        

    '' Add columns to this new table.
    tbl.Columns.Append "ID", adInteger
    tbl.Columns.Append "Product_Name", adVarWChar, 200
    tbl.Columns.Append "Category_ID", adInteger
    tbl.Keys.Append "PrimaryKey", adKeyPrimary, "ID"
    
    '' Specify the ID primary key column to be AutoNumber field.
    With tbl.Columns.Item("ID")
        Set .ParentCatalog = cat
        .Properties("Autoincrement") = True
    End With
    
    '' Append the table to catalog
    cat.Tables.Append tbl
    
    '' Create the composite unique index
    Set idx = New ADOX.Index
    idx.Name = "Uidx_Products"
    idx.IndexNulls = adIndexNullsAllow
    idx.PrimaryKey = False
    idx.Unique = True
    
    '' Append two columns to the index.
    idx.Columns.Append "Product_Name"
    idx.Columns.Append "Category_ID"

    '' Append the index to the table we just created.
    tbl.Indexes.Append idx
    
    '' Insert some records for testing.
    cnn.Execute "INSERT INTO Table2(Product_Name, Category_ID) values ('Chocolade', 1)"
    cnn.Execute "INSERT INTO Table2(Product_Name, Category_ID) values ('Cheese', 2)"
    cnn.Execute "INSERT INTO Table2(Product_Name, Category_ID) values ('Chocolade', 2)"

    '' Close connection and release resources.
    cnn.Close
    Set cnn = Nothing
    Set tbl = Nothing
    Set cat = Nothing
    Set col = Nothing
    Set idx = Nothing
    
    MsgBox "Table and composite unique index have been successfully created."
    
    Exit Sub
    
Catch:
    MsgBox "cmdCreate_Click()" & vbCrLf & vbCrLf & " Error: " & Err.Description
End Sub

Happy Coding!


Copyright© GeeksEngine.com



Other Recent Articles from the MS Access category:

1.Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
2.How to easily get a list of field names in MS Access
3.How to count distinct records in MS Access
4.How to do transaction based processing in MS Access
5.How to open a document (local/network file or web page) from MS Access
6.How to do cross-table update queries in MS Access - the right way
7.Three efficient ways to get the number of records by using VBA
8.How to create a composite unique index (not as a primary key) in MS Access
9.Use VBA to get the correct number of records in a Recordset object
10.Disable Access Prompt when a record is changed, table deleted, or action queries run
11.How to hide and unhide a MS Access object
12.How to return multiple values from a VBA function (Part 3)
13.How to return multiple values from a VBA function (Part 2)
14.How to return multiple values from a VBA function (Part 1)
15.Three ways to programmatically duplicate a table in MS Access by VBA
16.Create a DLL by CSharp or VB.Net for VBA
17.How to correctly reference and call a DLL
18.How to register a C# or VB.Net DLL
19.Email address validation by Regular Expressions using VBA
20.Fix MS Access error: Query must have at least one destination field
21.How to unselect radio buttons in MS Access after it has been selected
22.How to Change Query Timeout Value for MS Access SQL Queries
23.What is Northwind Traders database

Copyright © 2017 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy