• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

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
0
WonHop
Asked:
WonHop
1 Solution
 
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
 
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

Featured Post

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now