Deleting an Autonumber Field

Hello Experts.

I am trying to delete and add a field to a table.  The code below to add the field work fine.

What I need to do is Delete the field and then add the field back again so that the Counter starts at Number 1.

The name of the Table is "tbl_Desktops".
Then name of the Field is "ID".
The type of field is a Primary Key/Autonumber.

I got the Adding a Field from Help.  I tried to get the Delete a Field from Help also.

When I tried to use what was there, I got an error message saying that I had to Delete the Index before I could Delete the Field.

This is what I have so far in the Delete Code part.

Dim dbs As Database, tdf As TableDef
    Dim fldInitial As Field
   
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs!tbl_Desktops
    Set fldInitial = tdf("ID")
   
    tdf.Fields.Delete fldInitial.Name

I get the Error Message when I get to:

   tdf.Fields.Delete fldInitial.Name


****************************************




 Dim dbs As Database
    Dim tblDesktop As TableDef, fld_Desktop As Field, fld2 As Field
    Dim idx As Index, fldIndex As Field
 
    Set dbs = CurrentDb
    Set tblDesktop = dbs("tbl_Desktops")
    Set fld_Desktop = tblDesktop.CreateField("ID", dbLong)
    fld_Desktop.Attributes = fld_Desktop.Attributes + dbAutoIncrField
    tblDesktop.Fields.Append fld_Desktop
    Set idx = tblDesktop.CreateIndex("PrimaryKey")
    Set fldIndex = idx.CreateField("ID", dbLong)
    idx.Fields.Append fldIndex
    idx.Primary = True
    tblDesktop.Indexes.Append idx
    dbs.TableDefs.Refresh
    Set dbs = Nothing


Thanks
LVL 2
WonHopAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

phil2freeCommented:
WonHop,

The following will delete the index for you.

tdf.Indexes.Delete "PrimaryKey"

Place this before the statement that is giving you the error.

Hope this helps!

Phil
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrianWrenCommented:
WonHop,

What if you used an append query to add all of these records to a new table whose AutoNumber was starting at 1, then deleted the source table, and then renamed the new table?

If you explicitly declare the fields to but appended, and don't use the asterisk, the new autoNumber field gets automatically filled in.

You're going to want your table to have a primary key, (I'm presuming), and this way you can have that.  Especially if you copy the source table, structure only...

Just a thought.  Of course, you won't need to do that if the answer form Phil gets you on your way.
0
hnasrCommented:
I agree with phil2free's comment.

I tried it like this:

    Set fldInitial = tdf("ID")
    tdf.Indexes.Delete "PrimaryKey"
    tdf.Fields.Delete fldInitial.Name

Credit goes to phil2free
0
WonHopAuthor Commented:
Hello guys…thanks very much for you comment.  phil2free's comment worked for me.  When I first tried it, it did not work. I got an Error message saying that I could not delete it because it was part of an index group or something like that.   I am not sure if this really made a difference or not, but I went in the Design view of the table and clicked on the Index button to see what was in there.  There were 3 other things listed. I deleted those and now the code works fine.  I will find out soon it that was it because I hae 4 other tables to do that for.

BrianWren:  
Your comment was suggested by someone here in the office.  And yes, that could be done for 1 of the 2 projects that I am using this for.  I am not sure if you are familiar with the application "Real Secure" or not (I really know nothing about it), but it will send information to the Access Database whenever it reaches a certain number of records.  There will 9 machines sending information at any giving time of the day or night.  I did not want to take the chance of the table being deleted at the same time data was coming in and missing some of the data.  This is another part of the project that you helped me with recently.  
Basically what I am doing every hour is gathering the information there, putting it in query.
Doing a Make Table
Putting that data in a CSV, Creating and exporting to another database and appending to another database that is created at midnight to hold the information gathered for that 24 hour period.  I need for the each 24 hour period to start with record number 1.  They are guessing by one of our other sites numbers that we might have about 100 - 150,000 records on some days.  I know Access Autonumber only goes up to a certain number and I did not want that number to ever come up.


Thanks to all of you for your help.
:o)
WonHop
0
WonHopAuthor Commented:
Hello guys.  This is the final working version of the code.  I am putting it out for for anyone who pays the price of admission when it is in the PAQ.

Thanks again.

Function Delete_Add_Field()

    Dim dbs As Database
    Dim tblDesktop As TableDef, fld_Desktop As Field
    Dim idx As Index, fldIndex As Field
    Dim fldID As Field
   
    'Table Name: tbl_Desktop
    'Field Name: ID
   
    'To Delete the Primary Key Field
    Set dbs = CurrentDb
    Set tblDesktop = dbs.TableDefs!tbl_Desktops
    Set fldID = tblDesktop("ID")
    tblDesktop.Indexes.Delete "PrimaryKey"
    tblDesktop.Fields.Delete fldID.Name
    tblDesktop.Fields.Refresh
 
    'To Add the Primary Key Field back in starting with number 1
    Set fld_Desktop = tblDesktop.CreateField("ID", dbLong)
    fld_Desktop.Attributes = fld_Desktop.Attributes + dbAutoIncrField
    tblDesktop.Fields.Append fld_Desktop
    Set idx = tblDesktop.CreateIndex("PrimaryKey")
    Set fldIndex = idx.CreateField("ID", dbLong)
    idx.Fields.Append fldIndex
    idx.Primary = True
    tblDesktop.Indexes.Append idx
    dbs.TableDefs.Refresh
    Set dbs = Nothing

End Function
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.