asked on

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?

ellandrd

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.

fisherbrsnch00

ASKER

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?

Sethi

Are u using DAO or ADO?

Anthony Perkins

Sethi,

>>Are u using DAO or ADO?<<
What does it look like to you? Hint look up TableDefs in Google.

Sethi

:-)) Stupid question from me. Thanks for the check.

Anthony Perkins

Smart-ass response from me. I apologize.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fisherbrsnch00

ASKER

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?

Anthony Perkins

>>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.

fisherbrsnch00

ASKER

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.