Setting field attributes to autoincrement

I am altering a database through code and everything is working fine except that I can't set a field to autoincrement    

Here is what I've tried
- source_db.TableDefs(0).Fields(0).Attributes = 17
and
- source_db.TableDefs(0).Fields(0).Attributes = dbAutoIncrField

And both times I get the error:

"Run-time error '3219':"
"Invalid Operation"

MSDN says it should work, any idea?
fisherbrsnch00Asked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Pagiarized from MSDN and tested successfully:

Sub CreateAutonumber()
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim f As DAO.Field

Set db = OpenDatabase("c:\temp\db1")
Set td = db.CreateTableDef("Table1")
Set f = td.CreateField("MyAutoNumber")
f.Type = dbLong
f.Attributes = dbAutoIncrField
td.Fields.Append f
Set f = td.CreateField("MyTextField")
f.Type = dbText
td.Fields.Append f
db.TableDefs.Append td
db.Close

End Sub
0
 
ellandrdCommented:
You can't directly set it but there is a workaround.  Create a new version of the table which is empty and ensure all fields can hold null values then use this code...


    Public Sub ForceIdentifierToSetValue(sTableName As String, lTargetNumber As Long)
   
        Dim rsData As DAO.Recordset
        Dim lCounter As Long
       
        lCounter = 1
        Set rsData = CurrentDb.OpenRecordset(sTableName)
       
        Do While lCounter < lTargetNumber
            rsData.AddNew
            rsData.Update
           
            rsData.MoveFirst
            rsData.Delete
                   
            lCounter = lCounter + 1
        Loop
       
    End Sub
 

This must be called from within access, or modified to work from VB.  
 
0
 
fisherbrsnch00Author Commented:
I'm not clear how that function would resolve the problem I'm having. Can you explain, or possibly show how this would work in vb?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
SethiCommented:
Are u using DAO or ADO?
0
 
Anthony PerkinsCommented:
Sethi,

>>Are u using DAO or ADO?<<
What does it look like to you? Hint look up TableDefs in Google.
0
 
SethiCommented:
:-)) Stupid question from me. Thanks for the check.
0
 
Anthony PerkinsCommented:
Smart-ass response from me.  I apologize.
0
 
fisherbrsnch00Author Commented:
That sounds good acperkins, but I'm wondering how I can use this method to alter an existing field? I suppose I could duplicate the field, make it autoincremented, copy the data over, kill the original and then rename the new field to the name of the original field... but that would seems like more work than is neccessary.

I simply want to take an existing field and make it autoincremented. Is this possible?
0
 
Anthony PerkinsCommented:
>>I suppose I could duplicate the field, make it autoincremented, copy the data over, kill the original and then rename the new field to the name of the original field... but that would seems like more work than is neccessary.<<
Yes, that is what you have to do and is exactly what MS Access (and MS SQL Server do, for that matter)

>>I simply want to take an existing field and make it autoincremented. Is this possible?<<
Nope. Which incidently was what ellandrd told you originally.
0
 
fisherbrsnch00Author Commented:
I understand that I can not change it directly with the method I was using, but I was hoping there was an easy way around it... like using openschema, I just can't figure it out.

Anyway, if you can change all other properties for an existing table and field but not this one then so be it. I'll have to work around it.

Thanks.
0
All Courses

From novice to tech pro — start learning today.