Use ADOX to Manipulate AutoNumber Fields

by Susan Sales Harkins

Application: Access 2000
Operating System: Windows

AutoNumber fields create a unique value for each record in a table. By default, Access begins this series with 1 and increments each value in the series by the value 1. Most of the time, this behavior is adequate. However, if you need to start with a value other than 1 or if you need to increment each value by any value other than 1, you may find yourself jumping through ADO hoops to get the job done. The fortunate developer knows how to use ADOX for this task. In this article, we'll use ADO and ADOX to modify a table's structure. Specifically, we'll add an AutoNumber field that starts with a value other than 1 and increments subsequent values by a value other
than 1.

The ADOX objects we'll use

When you need programmatic access to your data, you need ActiveX Data Objects (ADO). ADO also gives you access to a database's structure, but there's an easier route--most of the time. ADO Extensions for DDL and Security (ADOX), a second COM library, contains another set of objects that work with the ADO objects to more easily manipulate database objects.

Let's take a quick look at each of the ADOX objects we'll be using in our technique. The top of the ADOX hierarchy is the Catalog object, which contains all the collections that define the schema catalog of a data source. These collections are Tables, Views, Users, Groups and Procedures. To define the field we're appending, we'll use the Column object, which simply represents a column in a table, index or key. The Column object is equivalent to a traditional Access field. ADOX works with a number of database engines and the terminology is fairly generic in order to accommodate the majority.

Referencing the ADOX library

Before you can work with ADOX, you must set a reference to its library--Microsoft ADO Ext. 2.1 for DLL and Security (or the version appropriate for your system). To do so, open the Visual Basic Editor (VBE) by pressing [Alt][F11] or opening any module. After launching the VBE, choose References from the Tools menu. Finally, ensure that there's a check mark next to the library previously mentioned and then click OK.

More than likely the ADO library is already selected, and having both ADO and ADOX references active in the same module can create conflicts. We recommend you explicitly reference libraries when this happens. For instance, when declaring the Catalog or Column object, specify the ADOX library in the form

Dim cat As New ADOX.Catalog

instead of

Dim cat As New Catalog

In this particular case, neither Catalog nor Column should pose a problem, but explicitly referencing libraries is a good habit to acquire if you work with multiple libraries.

The procedure

If you need to begin an AutoNumber field with a value other than 1 or increment its value by a value other than 1, you'll need to do so programmatically. (Note that you can change the seed value with a query, but you'll need code to change the incremental value.) The procedure shown in Listing A is just one solution, and we think one of the simpler ones.

First, the procedure declares two object variables: cat as a Catalog object and col as a Column object. Remember, we're modifying a table and the Tables collection is one of the many collections that make up the ADOX Catalog object. Next, the procedure establishes a connection to the current project.

The With block then prepares to create a new column with the name ProductID. Don't worry about the adInteger data type's limits; it's the ADOX equivalent to the Access Long data type. The statement

Set .ParentCatalog = cat

may seem a bit confusing stuck in the middle of a With block, but by setting the ParentCatalog property to the current connection, we have access to the following provider-specific properties: AutoIncrement, Seed and Increment. These three properties define ProductID as an AutoNumber data type that starts with the value 10 and increments each subsequent value by 10. The CLng() function converts the value in question (we're using 10) to a Long data type, which both properties require. We then use the Append method to attach the new field. Finally, the procedure sets the two object variables, cat and col, to Nothing.

Function AddAutoNumberField()
Dim cat As New ADOX.Catalog
Dim col As New ADOX.Column
cat.ActiveConnection = CurrentProject.Connection
With col
    .Name = "ProductID"
    .Type = adInteger
    Set .ParentCatalog = cat
    .Properties("AutoIncrement") = True
    .Properties("Seed") = CLng(10)
    .Properties("Increment") = CLng(10)
End With
cat.Tables("tblProducts").Columns.Append col
Set cat = Nothing
Set col = Nothing
End Function

You can add this procedure to a database by opening a blank module and typing it in. To open a blank module, click Modules on the Object bar in the Database window and then click New. After entering the procedure, save the module by clicking the Save button on the editor's Standard menu and name it basAddAutoNumberField or something just as appropriate.

Using the procedure

This procedure works best on an empty table. You can run it against a table that contains data, but this particular procedure won't start using the Seed and Increment properties until you start entering new data. For any existing records, Access will assign values starting with 1 and will increment by 1. Keep in mind that this could lead to duplicate entries in the AutoNumber field. For instance, if you apply our sample procedure to a table that already has 11 records, the AutoNumber field will contain the values 1 through 11. The next new record you enter will begin incrementing with the seed value we specified, regardless of existing data--meaning you'll have two records containing the value 10.

To test our procedure, create tblProducts--the table our procedure modifies. For simplicity, create just two fields in the sample table: Product, which is a Text field, and Price, which is a Currency data type. Save and close the table, without creating a primary key.

Now we're ready to modify tblProducts by adding an enhanced AutoNumber field to its structure. To do so, open the Immediate window by pressing [Ctrl]G, type

AddAutoNumberField

and then press [Enter]. You can see the results by opening tblProducts, which we've shown in Figure A. As you can see, this table now contains a third field. Its name is ProductID and it's an AutoNumber field. After adding two records, as we've done in Figure B, you can see that the AutoNumber field does indeed start with the value 10 and increments each value by 10.

Figure A: Our procedure adds an AutoNumber field to our empty table.

Figure B: The AutoNumber field begins its series with the value 10 and increments each subsequent value by 10.

Where's the hard part?

If you're going to modify a table's structure, using ADOX objects is probably the easiest and most direct route. As we've shown, adding an AutoNumber field and then manipulating its Seed and Increment properties requires only two object variables and a few lines of code. If you need more flexibility, add a few more variables and pass the table's name, seed value and increment value each time you call the procedure. In addition, for the sake of simplicity we omitted error handling, but you'll want to plan for error number -2147217858 (cannot define field more than once), which occurs if you try to add a second ProductID field to a table.

More than an incremental improvement

AutoNumber fields provide a convenient way of generating incremental numbers in a table, but sequentially incrementing by 1 may not be appropriate for your needs. And using ADO to increment each value might be more trouble than it's worth. There is a better way. In this article, we've shown you how to use ADOX to manipulate an AutoNumber field to supply the results you want. You now have a relatively easy means of specifying the starting point and step value for your tables' AutoNumber fields.

Copyright © 2001 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.