A number of people have asked me how one can programmatically tell if a field is an autonumber field or not. The reason why this has been asked several times is because this information is not available via the ADO Schema (there are a number of tutorials available on the web on how to use the Schema). Instead you need to use ADOX and expose the Properties of the Column object. There is a Provider Defined Property called "autoincrement" which returns a True/False result depending on whether the field is an autonumber or not.

The basic code (for testing any given table) looks like this:

<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnect

Set objCatalog = Server.CreateObject("ADOX.Catalog")
objCatalog.ActiveConnection = objConn

' Set x equal to the table you want to test
x = 0

For each objColumn in objCatalog.Tables(x).Columns

   Response.Write("<br><b>" & objColumn.Name & "</b><br>" & vbCrLf)

   If objColumn.Properties("AutoIncrement") = True then
      Response.Write("Autonumber Column")
   Else
      Response.Write("Not AutoNumber Column")
   End If

Next

Set objCatalog = Nothing
objConn.Close
Set objConn = Nothing
%>

A more comprehensive piece of code that demonstrates encapsulation, error handling and the ability to loop through all the columns in all the tables within your Access file will be up soon in the Code section.

Back to FAQ Listing