By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 1,942 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

How to use VBA to remove attribute dbAutoIncrField?

TheSmileyCoder
Expert Mod 100+
P: 2,321
Im trying to convert an existing autonumber field into a numeric long field.

How can I do this in VBA?

What I have tried:
Expand|Select|Wrap|Line Numbers
  1. Dim DB As dao.Database
  2.     Set DB = CurrentDb()
  3.     Dim tblDef As dao.TableDef
  4.     Set tblDef = DB.TableDefs("hist_Tbl_Obs")
  5.     Dim f As dao.Field
  6.     Dim p As Property
  7.     For Each f In tblDef.Fields
  8.         If f.Attributes And dbAutoIncrField Then
  9.             Debug.Print f.Name
  10.             'f.Attributes = dbFixedField Or dbUpdatableField
  11.             'f.Attributes = dbNumeric
  12.             'f.Attributes=0
  13.             'f.Type = dbNumeric
  14.             'f.Properties(dbAutoIncrField) = False
  15.         End If
  16.     Next
  17.  
  18. 'Cleanup
  19.     Set f = Nothing
  20.     Set tblDef = Nothing
  21.     Set DB = Nothing
  22.  
The commented lines all fail with error 3219: Invalid Operation, with the exeption of the last one, which has no effect.
Jan 4 '12 #1

✓ answered by 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:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fModifyFieldType(strTableName As String)
    2. Dim strSql As String
    3. Dim db As DAO.Database
    4. Dim tdf As DAO.TableDef
    5. Dim fld As DAO.Field
    6.  
    7. Set db = CurrentDb()
    8. Set tdf = db.TableDefs(strTableName)
    9.  
    10. For Each fld In tdf.Fields
    11.   If (fld.Attributes And dbAutoIncrField) <> 0 Then     'Yep, an AutoNumber Field
    12.     strSql = "ALTER TABLE " & strTableName & " ALTER COLUMN " & fld.Name & " LONG;"
    13.       DBEngine(0)(0).Execute strSql, dbFailOnError
    14.         Exit For
    15.   End If
    16. Next
    17.  
    18. Set fld = Nothing
    19. Set tdf = Nothing
    20. Set db = Nothing
    21. End Function
  4. Sample Function Call:
    Expand|Select|Wrap|Line Numbers
    1. 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.

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,491
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 5 '12 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Jan 5 '12 #3

ADezii
Expert 5K+
P: 8,638
@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:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fModifyFieldType(strTableName As String)
    2. Dim strSql As String
    3. Dim db As DAO.Database
    4. Dim tdf As DAO.TableDef
    5. Dim fld As DAO.Field
    6.  
    7. Set db = CurrentDb()
    8. Set tdf = db.TableDefs(strTableName)
    9.  
    10. For Each fld In tdf.Fields
    11.   If (fld.Attributes And dbAutoIncrField) <> 0 Then     'Yep, an AutoNumber Field
    12.     strSql = "ALTER TABLE " & strTableName & " ALTER COLUMN " & fld.Name & " LONG;"
    13.       DBEngine(0)(0).Execute strSql, dbFailOnError
    14.         Exit For
    15.   End If
    16. Next
    17.  
    18. Set fld = Nothing
    19. Set tdf = Nothing
    20. Set db = Nothing
    21. End Function
  4. Sample Function Call:
    Expand|Select|Wrap|Line Numbers
    1. 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.
Jan 5 '12 #4

NeoPa
Expert Mod 15k+
P: 31,491
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.
Jan 6 '12 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Jan 6 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.