AutoNumber and identity fields have some benefits—and some costs. In this article, Russell Sinclair covers just about everything you can do to manage AutoNumbers in ADO, ADOX, and DAO. Since most AutoNumber fields are used as primary keys, this is the information you need to manage your tables.

Before reading this article, see this post

When designing tables in Access, you often have to make a choice about how to define the primary key for your tables. You can eitherbase your primary key on a field (or fields) that hold real meaningful data, or you can add an AutoNumber field and base your primary key on that field. AutoNumber fields offer a number of advantages over using real data as a primary key. Relationships based on AutoNumber fields can be easier to create. Indexes based on AutoNumber fields can be updated faster by Access; as a result, inserts and updates to the tables are performed faster. In cases where the uniqueness of a table is defined by multiple columns, AutoNumbers provide an efficient way to identify and search for a particular record.

The term “AutoNumber” is unique to Access. A more generic name for these types of fields is “identity” fields. An identity field can be sequential or random. That is, the value that’s created for a new record can be generated by adding a number to the last new value, or a random number can be generated for each new record. To support sequential identities, the identity field must have two other characteristics: the seed and increment values. The seed value is the next number that will be used for this field when a record is added. The increment value is the number by which this value will be increased between each record.

Access makes it easy to add an identity field to a table. However, there are a few problems with the way you can work with them. The first and most obvious problem is that Access gives you no control over the seed or increment values. Access automatically assigns an initial seed value of 1 and an increment value of 1 to all identity fields. Another problem is that if you find that you need to change an AutoNumber field to a standard Long data type, Access requires that you delete all foreign relationships associated with the field before the change can be made. This means that you must ensure that you delete and rebuild all relationships for the field—an errorprone process. The final problem occurs when you insert a record with an identity into a table using code or an append query: You can’t determine what value will be assigned to the identity field in the new record. Although you could search the table for a record matching the entry you just made, this type of search can be slow. It would be much easier if there were some way to determine what the last identity created was.

Fortunately, Access 2000 and ADO provide the ability to overcome all of these problems.

Handling seed and increment values

One of the features I’ve always loved about SQL Server is that I can change the seed and increment values of an identity field simply by opening a table in design view and changing two properties of the field. In SQL Server, I can also change the seed and increment values using a SQL Data Definition Language (DDL) statement. The syntax for this statement is:

ALTER TABLE tablename   
ALTER COLUMN fieldname   
IDENTITY (seed, increment)

In this statement, tablename is the name of the table to which I want to add the new field fieldname, and seed and increment are any number within the range of the data type of the field (SQL Server allows you to use a variety of integer data types for identities). Now, with Access 2000, I can use this SQL statement against an Access table.

There are three catches to using this SQL statement with Access:

• There’s no way to tell what the current seed and increment values are, either through DAO or ADO.
• The SQL statement will return an error if the field is the primary field in any foreign relationships— Access prevents an ALTER COLUMN statement being run on any field that’s a primary field in an enforced relationship.
• The statement will only work if it’s run through ADO. It won’t work if it’s run in the Access IDE or through DAO. To execute this properly, you’ll need to use code like this:

Dim conn As ADODB.Connection 

Set conn = CurrentProject.Connection 
conn.Execute("Alter Table….")

The final catch isn’t really a problem. In order to run the statement in DAO, just replace the word IDENTITY with the word COUNTER, and the statement will execute correctly. In fact, the “COUNTER” version will run in DAO also.

Fortunately for us, Microsoft added ADOX to Microsoft Data Access Components in version 2.5. ADOX stands for the ActiveX Data Objects Extensions for Data Definition Language and Security. This is a library that can be used to create, modify, manipulate, and delete database objects like tables and queries. The advantage to using ADOX is that it’s supposed to be database-neutral— the same code should work with all databases that have implemented an OLE DB provider. In ADOX, a Catalog object represents each database or data source. Each Catalog object can contain Tables, Views, Procedures, Groups, and Users. These objects, in turn, contain some collections of their own and expose some properties that might not be available through the Access IDE or DAO.

When I first started toying with ADOX against Access databases, I noticed that there were a number of interesting properties that were exposed for each of these objects in their respective Properties collections. The ADOX Column objects that represent fields in a Table have three properties that interested me in particular: Autoincrement, Seed, and Increment. The Autoincrement property is a Boolean value that specifies whether or not a field is an identity field. This property is read-only, but, if it’s set to True, I know that I’m dealing with an identity field. The Seed and Increment properties expose the identity field’s properties. These properties are read/ write, so you can change them simply by modifying the values with ADOX code. You can see the code that does this in my ChangeIdentity function in the basIdentity module that’s available in the Source Code file at www.smartaccessnewsletter.com. My ChangeIdentity function takes a table name, a field name, a seed, and an increment value as parameters and resets the values of the specified field in the table.

Changing the data type

The second challenge that I’ve faced when working with identity fields is changing them to standard Long fields. This type of situation doesn’t come up all the time, but it’s something that I’ve had to do often enough that I wanted to automate the process. The main reason that this change is difficult in Access is that the field can’t be part of the primary key in any enforced relationships. What I needed to do in order to accomplish this task was to save the relationship information somehow, delete the relationships, change the field to a Long, and then re-create the relationships from the saved information.

Actually changing the field data type is quite easy and can be done through ADO or DAO by using a SQL DDL statement:

ALTER TABLE tablename   ALTER COLUMN fieldname INTEGER

This command tells Access to change the field specified in the tablename and fieldname parameters to a 32-bit integer data type (INTEGER), which is the SQL DDL equivalent to an Access Long data type.

Saving the relationships

Once I’d determined what fields I wanted to change the data types for, I needed some way to save the information about the relationships defined against the field. Although I could have done this using DAO, I felt that it was best to continue to use only a single data-access library. I decided that I’d use the information provided by ADOX to store the relationship information for the fields I wanted to modify.

This information isn’t as easy to find in ADOX as it is in DAO. In DAO, you can use the Relations collection in a Database object to analyze the relationships in an Access database. In ADOX, relationships are stored in a Table’s Keys collection. However, the information about a relationship is stored in the table with the foreign key, not in the table whose primary key you want to change. This means that I needed to know what tables were related to the field being changed before I could get any information about the relationship itself. Scanning all of the Keys in all of the tables in the database in order to obtain this information for each AutoNumber field didn’t really seem like a very efficient way to retrieve this data. Fortunately, I didn’t have to do this.

ADO connections have an OpenSchema method that’s used to return a recordset containing information about the design—or schema—of the database. This method takes three parameters, two of which are required: the schema ID to be returned (a value defined in the SchemaEnum data type) and an array of filter arguments for that schema. The one optional argument is only used if the value for the schema ID argument is adSchemaProviderSpecific (meaning that you’re asking for a provider-specific schema). One of the values that the optional argument takes is adSchemaForeignKeys. This returns a recordset of all of the primary/foreign key relationships in the database and their main properties. I used the OpenSchema function to return all of the relationships for the table that was about to change. I then filtered this recordset locally so that it would only list those relationships defined against the primary key field I needed to change. While I could have used the OpenSchema function to filter the results before returning them, I found that local filtering was more reliable.

In this schema recordset, each row includes the key name and foreign table name. This information allowed me to access the key information for the related fields so that I could store the relationships in working tables. However, there’s one problem. Key objects not only define relationships in a database, but also define unique indexes in a table. The names of these objects can, and often will, overlap. Any table in Access that’s the foreign table in a one-to-one relationship with another table will have a relationship and a unique index with the same name. This isn’t a problem in Access, but if you’re accessing these items through named keys in ADOX, there are actually two items in the Keys collection with the same name. You can never be sure which one you’ll actually get when you try to refer to a Key by name.

Fortunately, each Key object has a Type property. The Type property contains a value of type KeyTypeEnum. If the key is a foreign key, the value of the Type property is adKeyForeign. By checking this property, I could ensure that I was actually dealing with the key I needed to log. Otherwise, I needed to loop through each Key in the Table object, checking the Name and Type properties to determine whether it was the Key I needed to log, and then save the Key information. You can see the code that does this in SaveKeyInfo in basIdentity in the sample database.

With each key that I needed to save, there were two pieces of information that needed to be recorded. The first piece that needed to be documented was the key information itself, including its Type, Name, PrimaryTable, PrimaryColumn, ForeignTable, UpdateRule, and DeleteRule. The second piece of information that needed to be documented was the fields that were referenced in the relationship. This is especially helpful in relationships that are defined on more than one field. Although it’s extremely uncommon to do this with AutoNumber fields (there’s usually only one field in the relationship), the possibility still needs to be accounted for. In order to document the field information, I needed to log the characteristics of each Column object in the Key’s Columns collection.

When dealing with Column objects defined against a Key, there are five pieces of information that are pertinent. The ForeignTable, ColumnName, and RelatedColumn properties help to define the relationship between the tables. However, in order to be able to re-create the relationship, I also needed to preserve the order in which the Columns were set in the relationship. Because ADOX doesn’t provide this as a property of the Column object, I needed to create a simple custom counter that was incremented with each Column and used to order the data when it would be retrieved later on. The final piece of information I needed to save for the Key’s Columns was the name of the relationship. This was done simply to ensure that I could relate the column information back to the key information later on.

All of this information is stored in tables called tblKey (for the Key objects) and tblKeyColumns (for the Key’s Column objects). You can see the results in the SaveKeyInfo function in the sample database.Deleting the relationships

Now that I had the information I needed to document the state of the Keys before I made any changes, I was ready to delete the relationships that were preventing the data type of my AutoNumber field from being modified. In order to do this, all I had to do was loop through the saved Key information and delete each Key using the saved name in the table. This can be done by calling the Delete method of the Keys collection in a Table object, passing in the name of the Key to delete, like this:

tbl.Keys.Delete "MyKeyName"

You might be wondering why the delete code doesn’t need to loop through the keys to ensure that it’s deleting the right key, as I had to do in logging one-to-one relationships. The reason behind this is that Access defines the relationship in such a way that the unique index and the relationship are completely dependent on each other and actually refer to a single DAO object. Although they’re interpreted as separate objects by ADOX, deleting either one of the keys in the table will delete both the index and the relationship in the Access database.

I could now run the ALTER TABLE command to change the identity fields to standard Long fields. Once this was done, I needed to rebuild the relationships from the saved data.

Rebuilding the relationships

Rebuilding the relationships was probably one of the easiest things to do. All I needed to do was use the data I’d saved about the relationships to rebuild the key and column information originally stored in the database. You can see the code to do this in the RebuildKeyInfo procedure in basIdentity. This code loops through each record I’ve saved to tblKey and then uses the information stored in tblKeyColumn to append each of the original fields to the Columns collection in the Key. The newly created Key is then appended to the Keys collection of the table using the Append method of the Keys collection.

Retrieving the last identity

The final challenge I wanted to overcome with identity fields was to be able to determine through code the last identity value added to a table. Knowing the value of the last record that you added is essential when that value must be put in the foreign key field of the next record that you’ll add. You could turn around and retrieve the record that you just added to find the value. There are occasions when even this workaround isn’t possible. The most common times are when I have a DAO recordset open in Append Only mode, or when I insert a record into a table by calling an append query from my code.

Access 2000 has added support for a SQL statement that was previously only available in SQL Server. The statement is:

SELECT @@IDENTITY

This statement will retrieve the last identity value added to any table using the current connection. This means that it will only work against the same DAO Database object or ADO Connection object that was used to insert the record in the first place. It always returns 0 in a standard Access query run through the IDE, so you should only use it from code.

For example, if I added a new record to tblEmployee and I wanted to retrieve the new EmployeeID identity value created, I’d have to use code like this to do it:

Dim cnn As New ADODB.Connection 
Dim rst As New ADODB.Recordset 
Dim lngNewID As Long 

Set cnn = CurrentProject.Connection 
Set rst.ActiveConnection = cnn 
rst.CursorLocation = adUseServer 
rst.CursorType = adOpenDynamic 
rst.LockType = adLockOptimistic 
rst.Open Source:="tblEmployee",Options:=adCmdTable 
rst!EmpFirstName = "George" 
rst!EmpLastName = "Orwell" 
rst.Update 

Set rst = New ADODB.Recordset 
rst.CursorLocation = adUseServer 
rst.CursorType = adOpenDynamic 
rst.LockType = adLockOptimistic 
rst.Open Source:="SELECT @@IDENTITY", Options:=adCmdText 
lngNewID = rst.Fields(0).Value

The value of the new ID would now be stored in the variable lngNewID. This same SQL statement could be used from DAO, and it could be used after calling an append query that added a record to a table that contained identity values.

Putting it to use

If you take a look at the sample database in the Source Code file, you might notice that it’s actually an Add-In database. You can install this Add-In using the Add-In Manager in Access, which gives you a menu item in the Tools | Add-Ins menu called Modify Identities. The Add-In allows you to quickly change the seed and increment values of all of your identity fields, and even switch the identities to Long fields. There’s even a bonus function called FillIdentities. This function uses ADOX to scan all Tables and Columns to find any Column objects with the Autoincrement property set to True, effectively building a list of all of the AutoNumber fields in your database. The details of each identity field are presented to you so that you can modify them.

If you’re still wondering why you might want this much control over your AutoNumber fields, consider a bug that exists in Jet 4.0 prior to service pack 5. If, when you inserted a record in a table, you provided a value for an identity column that was less than the current seed in the table, the seed would be reset to one more than the record you inserted. So, if you had a table with identities from 1 to 10, excluding 5, and then you inserted a record with an ID of 5, the table would attempt to start numbering the records at 6. This could violate your primary key, or even corrupt your data. This utility allows you to see and fix the problem before any damage is done.

If you’re using AutoNumber fields in your database, odds are you’re using them as primary keys in your database. The foundation of the relational database theory begins with assigning primary keys to tables in order to uniquely identify records. Managing those AutoNumber fields is critical to maintaining your data integrity. The tools that I’ve described in this article and supplied in my Add-In will give you all of the control you should ever need.

Garry’s 2009 Autonumber Story

I was asked to debug a database where a new record kept “seemingly” overwriting an existing record in a complex DAO centric application written back in the 1990’s. So in I went, form after form and then into popup forms and still I couldn’t trace why the numbers were being overwritten. Then my first clue, the table had an autonumber field and the field was not a Primary or even unique key.  So on fixing this, still the problem kept occurring. I thought that the old VBA software was generating the unusual records because Autonumber never overwrites in own exisiting autonumber (Does it). Well in the end I found out that it did, not sure why

So I set about find code to reset the AutoNumber

Open the backend, make sure that the AutoNumber field has a Unique Index. In a new module, add a reference to Microsoft ADO Ext. for DDL and Security and then add the following

Public Sub resetSeed(tableName As String, fieldName As String, seedValue As Integer)

Dim cat As New ADOX.Catalog

Set cat.ActiveConnection = CurrentProject.Connection

cat.Tables(tableName).Columns(fieldName).Properties(“Seed”) = seedValue

End Sub

call using the following (I did this in the Immediate Window)

call resetSeed (“equation”, “equationid”, dmax(“equationid”,”equation” ) + 1)

Possibly remove the module and the ADOX reference

See Switching Primary Indexes and Field Index Gotchas