How to use VBA to remove attribute dbAutoIncrField?

[TheSmileyCoder]

Im trying to convert an existing autonumber field into a numeric long field.

How can I do this in VBA?

What I have tried:

Dim DB As dao.Database
    Set DB = CurrentDb()
    Dim tblDef As dao.TableDef
    Set tblDef = DB.TableDefs("hist_Tbl_Obs")
    Dim f As dao.Field
    Dim p As Property
    For Each f In tblDef.Fields
        If f.Attributes And dbAutoIncrField Then
            Debug.Print f.Name
            'f.Attributes = dbFixedField Or dbUpdatableField
            'f.Attributes = dbNumeric
            'f.Attributes=0
            'f.Type = dbNumeric
            'f.Properties(dbAutoIncrField) = False
        End If
    Next
 
'Cleanup
    Set f = Nothing
    Set tblDef = Nothing
    Set DB = Nothing
 

The commented lines all fail with error 3219: Invalid Operation, with the exeption of the last one, which has no effect.

[NeoPa]

You are looking to change the value of Attributes from an AutoNumber field which has the following two flags set - dbAutoIncrField (&H10) & dbFixedField (&H01), into a standard Long field which has only one flag set - dbFixedField (&H01). Essentially to turn of the dbAutoIncrField flag. This seems to be disallowed from my tests, but if it were allowed would look like :

f.Attributes = (f.Attributes And Not dbAutoIncrField)

Unfortunately, using the Watch Pane of the IDE I checked all properties of an AutoNumber field as well as those of a Long and compared them. Only the Attributes property was different (apart from obviously unhelpful ones such as the Name). I couldn't find a way of changing a field from AutoNumber to Long using VBA.

[TheSmileyCoder]

Well that is also what my own tests revealed, using the approach described.

Doing it manually through the standard access table design GUI, however seems so easy, that I thought it would be available somehow.

I guess I could write a VBA routine to add an extra field of type number,Long, and then update its values to the key column values, delete the key field, and rename the extra field added. I just prefer to avoid complicating matters if possible. :)

Thank you for your time.

[ADezii]

@TheSmileyCoder:
Simply Pass to this Function a Table Name and it will:

1. Determine the Name of an AutoNumber Field, if one exists.
2. Convert that AutoNumber Field to a LONG INTEGER Data Type.
3. Function Definition:
   

   Public Function fModifyFieldType(strTableName As String)
   Dim strSql As String
   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Dim fld As DAO.Field
    
   Set db = CurrentDb()
   Set tdf = db.TableDefs(strTableName)
    
   For Each fld In tdf.Fields
     If (fld.Attributes And dbAutoIncrField) <> 0 Then     'Yep, an AutoNumber Field
       strSql = "ALTER TABLE " & strTableName & " ALTER COLUMN " & fld.Name & " LONG;"
         DBEngine(0)(0).Execute strSql, dbFailOnError
           Exit For
     End If
   Next
    
   Set fld = Nothing
   Set tdf = Nothing
   Set db = Nothing
   End Function

4. Sample Function Call:
   

   fModifyFieldType("Table1")

5. Critical Points to remember:
   1. The Code WILL work if the AutoNumber Field is also the Primary Key, and NOT involved in any Relationships.
   2. The Code WILL NOT work if the AutoNumber Field is also the Primary Key, and IS involved in any Relationship.
6. The Code has been tested, and is fully functional. Any other questions, feel free to ask.

[NeoPa]

Smiley:
I just prefer to avoid complicating matters if possible. :)

Makes perfect sense to me. There is an alternative, which is to use SQL, but it seems ADezii has beaten me to that and, as is quite unsurprising, has already knocked up some good code to illustrate the approach.

It's not strictly a VBA approach (although it is all managed within a VBA procedure), but I think we know by now that the strict VBA approach is not supported so this may well be an acceptable solution.

[TheSmileyCoder]

Thank you for your code Adezii.

If possible I would liked to do it entirely by VBA, but in reality its merely because I am mostly used to working in VBA, and not so much in SQL, and also because I wanted to make sure that it was not simply my lack of understanding or lack of correct method that was preventing me from using a pure VBA solution.

That said, the solution does cover my, shall we say REAL need, which is to be able to drop the autonumber attribute without having to do it manually.