?
Solved

Setting field attributes to autoincrement

Posted on 2005-02-25
10
Medium Priority
?
374 Views
Last Modified: 2013-12-25
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?
0
Comment
Question by:fisherbrsnch00
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 16

Expert Comment

by:ellandrd
ID: 13407885
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
 

Author Comment

by:fisherbrsnch00
ID: 13408399
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
 
LVL 18

Expert Comment

by:Sethi
ID: 13408506
Are u using DAO or ADO?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13408762
Sethi,

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

Expert Comment

by:Sethi
ID: 13408771
:-)) Stupid question from me. Thanks for the check.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13408780
Smart-ass response from me.  I apologize.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1500 total points
ID: 13408795
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
 

Author Comment

by:fisherbrsnch00
ID: 13408930
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13408943
>>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
 

Author Comment

by:fisherbrsnch00
ID: 13409132
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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question