Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Resetting Autonumber Seed (Access 2K)

    For some time now, I've been confronting a problem that I could not find an answer to in the docs. Under certain special circumstances, I need to clear a table of all rows and reload the data -- programmatically with VBA. Since one of the columns was a generated AutoNumber value (ClientID), I was at a loss as to how to reset the AutoNumber back to 1 (or, for that matter, to ANY value other than the current seed).

    Then, one day, while scanning threads here, I saw a post from Hans that directed me to a MSDN Knowledge Base article, herein quoted:
    You can use the following function in a Microsoft Access database (.mdb) to programmatically reset the seed value of your AutoNumber field. You can add the function to a module, and then run it in the Debug window. Or, you can call the function from a command button or a macro.

    Note For this code to run properly, you must reference both the Microsoft ActiveX Data Objects 2.x and the Microsoft ADO Ext 2.x for DDL and Security Libraries (where 2.x is 2.1 or later.) To do so, click References on the Tools menu in the Visual Basic Editor. Make sure that the Microsoft ActiveX Data Objects 2.x and the Microsoft ADO Ext 2.x for DDL and Security Libraries check boxes are selected.

    Function ChangeSeed(strTbl As String, strCol As String, lngSeed As Long) As Boolean
    'You must pass the following variables to this function.
    'strTbl = Table containing autonumber field
    'strCol = Name of the autonumber field
    'lngSeed = Long integer value you want to use for next AutoNumber.

    Dim cnn As ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim col As ADOX.Column

    'Set connection and catalog to current database.
    Set cnn = CurrentProject.Connection
    cat.ActiveConnection = cnn

    Set col = cat.Tables(strTbl).Columns(strCol)

    col.Properties("Seed") = lngSeed
    cat.Tables(strTbl).Columns.Refresh
    If col.Properties("seed") = lngSeed Then
    ChangeSeed = True
    Else
    ChangeSeed = False
    End If
    Set col = Nothing
    Set cat = Nothing
    Set cnn = Nothing

    End Function

    Well, "Eureka!", I cried! And I sat down to include it in my code. However, when testing it, I continually get thrown into an error popup screen as soon as the function attempts to store the value into the "Seed". The accompanying message is "Invalid Argument". I recalled seeing another message from Hans implying there were problems with the latest Access updates in supporting this code. So I went to the MS Office 2000/Visual Basic Programmer's Guide which showed a subroutine called "CreateAutoNumberField" (Chapter 14, page 600), which dynamically creates a new table, along with multiple columns, one of which is an AutoNumber type. Using that as a model, I wrote a modified version of the above function in which I attempted to delete and re-create the AutoNumber column in the nature shown. The delete fails with a similar error message (code below).

    Function ChangeSeed(strTbl As String, strCol As String, lngSeed As Long) As Boolean
    'You must pass the following variables to this function.
    'strTbl = Table containing autonumber field
    'strCol = Name of the autonumber field
    'lngSeed = Long integer value you want to use for next AutoNumber.

    Dim cnn As ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim col As ADOX.Column
    Dim cols As ADOX.Columns
    Dim lngSeedx As Long
    Dim flgAuto As Boolean

    'Set connection and catalog to current database.
    Set cnn = CurrentProject.Connection
    cat.ActiveConnection = cnn

    Set cols = cat.Tables(strTbl).Columns
    lngSeedx = cols.Item(strCol).Properties("Seed")
    flgAuto = cols.Item(strCol).Properties("AutoIncrement")
    If flgAuto = True Then
    cols.Delete (strCol)
    cols.Append strCol, adInteger
    cols.Item(strCol).Properties("AutoIncrement") = True
    End If
    Set cols = Nothing
    ' Set col = cat.Tables(strTbl).Columns(strCol)
    ' lngSeedx = col.Properties("Seed")
    ' flgAuto = col.Properties("AutoIncrement")
    ' col.Properties("Seed") = lngSeed
    ' cat.Tables(strTbl).Columns.Refresh
    ' If cols.Item(strCol).Properties("seed") = lngSeed Then
    ' ChangeSeed = True
    ' Else
    ' ChangeSeed = False
    ' End If
    ' Set col = Nothing
    Set cat = Nothing
    Set cnn = Nothing

    End Function

    I've run out of ideas on how to accomplish my underlying goal. Does anyone out there have a suggestion?

    Thanks, Jim

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Resetting Autonumber Seed (Access 2K)

    You don't indicate what Service Release/Service Packs have been applied to Access, so you may be affected by a bug in Access 2000. Actually the bug was in Jet 4.0, and there have been several attempted fixes - but it appears it still isn't quite right. I would suggest you apply the latest SP for Jet which is SP8 - it can be downloaded from Microsoft. However, check the version you have first - see <!mskb=239114>Microsoft Knowledge Base Article 239114<!/mskb> for how to determine that. Info on downloading SP8 can be found in <!mskb=829558>Microsoft Knowledge Base Article 829558<!/mskb>.

    All that aside, I don't understand how you expect to recreate the record with the same autonumber that it previously had. Autonumbers are such that if a record is deleted then that number is lost. In addition, if a user starts to enter a record and then cancels the process, the number is lost and no record exists with that number. It sounds to me like you would be better off not using an autonumber, and creating your own index number with a process that is repeatable, but prehaps more details on why you need to delete all the records would indicate otherwise. If this doesn't make sense to you, please post back.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resetting Autonumber Seed (Access 2K)

    Thanks Wendell for the quick response. I had thought I'd probably not hear from anyone before tomorrow!

    I fully appreciate the potential problems of re-assigning autonumber indexes, and have taken care of that problem thru other means. So, I/we don't need to worry about that (yet)...

    I don't currently know the SP level of my Jet, and will have to read the articles you reference to determine how to display those values, as well as get the latest updates down and loaded.

    Since, by default, you don't have any alternate suggestions for accomplishing a seed-reset, then I guess I will have to hope the updates will fix things.

    Thanks again -- Jim

  4. #4
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resetting Autonumber Seed (Access 2K)

    Wendell,
    I've examined the MS Knowledge Base articles. They list 19 DLL's that were updated with SP8 (the latest & greatest), along with the DLL version number for each one. I located all 19 files on my system and displayed their version numbers to match against the list. Of the 19 files, 18 had version numbers corresponding with SP8. The one file, "msjint40.dll", was listed with version 4.0.8015.0, but displayed on my system, 4.0.6508.0.

    While unlikely to have not updated one DLL of the 19, I went ahead and downloaded the SP8 update package and ran it. It finished quite quickly, and when done, a redisplay of the "msjint40.dll" version still produced 4.0.6508.0. Either the KB article listed an incorrect version number for the one file, or there is a very peculiar bug in my environment...

    Assuming that I do have a functioning SP8 installed, it is beginning to sound as if the Jet 4.0 bug is still tromping around in there. I guess I'll try and contact MS tomorrow.

    Thanks again for your assistance.
    -- Jim

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Resetting Autonumber Seed (Access 2K)

    Jim,

    I am using Access 2002, not 2000, but still with Jet 4.0 SP-6 on my work PC; the code you posted works fine there...

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resetting Autonumber Seed (Access 2K)

    Just curious why you haven't upgraded from Jet 4.0 SP-6 to 7 or 8?

    John

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Resetting Autonumber Seed (Access 2K)

    Because it's my work PC - I have no power over what is installed on it; that is ordained by the IT department. At home I do have SP-8.

  8. #8
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resetting Autonumber Seed (Access 2K)

    Very interesting situation. I have a solution for you. Here's my thought process. First, you can put a value into an Autonumber field by using an INSERT INTO statement (APPEND QUERY). Also, if you want to 'start' an Autonumber at a specific number, you can use an Insert Into to insert the value before the number you want, and the Autonumber is then 'set' for the number (ie, if you want to start the Autonumbering at 500, you would say INSERT INTO tblMyTable ([MyID]) VALUES (499). You then delete that record, and as long as you don't compact the database, the first record you add will have 500 for the Autonumber.

    So after reading your post, I thought to myself, why can't you just 'roll up' the Autonumber to it's max value, and have it start over. So first I tried -2147483648. Adding records after that, simply count's 'up' (-2147483647, etc.). So then I tried -1, the next record showed 0. Just putting in 0, though, didn't do the trick, because if I put in a few records, 1, 2, 3, 4, then delete them, appending in 0, still has the next record show up as 5. Go figure. However, putting in -1 (after manually adding records for 1, 2, 3, 4....), it would go 0,1,2,3,4. So then I tried appending -1, then 0, and sure enough, the next Autonumber was 1.

    Nifty eh? So here's some sample code:

    Function ResetAutoNumberID()
    Dim strSQL As String
    DoCmd.SetWarnings False
    strSQL = "INSERT INTO tblTest ([TestID]) VALUES (-1)"
    CurrentDb.Execute strSQL
    strSQL = "INSERT INTO tblTest ([TestID]) VALUES (0)"
    CurrentDb.Execute strSQL
    strSQL = "DELETE * FROM tblTest"
    CurrentDb.Execute strSQL
    DoCmd.SetWarnings True
    End Function

    Of course you will need to change the table and field names in the SQL statements, and if any fields are 'required', you'll need to add valid 'dummy' data to them, in order to make them work. But the end result with have the Autonumber 'counter' set back to 1, without compacting the database.

    <img src=/S/sailing.gif border=0 alt=sailing width=25 height=25>

  9. #9
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resetting Autonumber Seed (Access 2K)

    Drew,

    Thank-you for your very thoughtful evaluation and reply. While somewhat more cumbersome than the MS Knowledge Base solution, what ever works....

    I'll definitely make use of your solution while I bug MS as to why their solution does NOT work!

    Thanks again. -- Jim

  10. #10
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resetting Autonumber Seed (Access 2K)

    No problem. Actually, if you change the CurrentDB.Execute's into DoCmd.RunSQL's, that code becomes more portable, because it wouldn't be dependent upon ADO or DAO. It would just use whatever that version of Access uses. You could also get real spiffy, and feed it the table name, and the 'seed'. From the table name, you could find the tabledef, find the required fields, and the AutoNumber fields, and build an SQL statement from that. If you wanted a seed other then 1, then you could just run one INSERT INTO statement, with x-1 as the value for the ID field.

    Just a thought...

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Resetting Autonumber Seed (Access 2K)

    However, using RunSQL requires mucking around with SetWarnings and it doesn't give you a RecordsAffected count. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  12. #12
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resetting Autonumber Seed (Access 2K)

    I already included the Setwarnings part, and don't really need a RecordsAffected count for this issue.

    Is this carryover from somewhere else? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Resetting Autonumber Seed (Access 2K)

    Yes, Drew, I realize you included the SetWarnings manipulations. My point was that they have to be there when you use RunSQL or you see a bunch of unnecessary warnings. With the Execute method, you don't need SetWarnings and you have the additional information provided by RecordsAffected. I wasn't suggesting you shouldn't use RunSQL, but there are also reasons to choose Execute, although I prefer not to use CurrentDb.Execute. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  14. #14
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resetting Autonumber Seed (Access 2K)

    What Execute are you refering too, if you aren't talking about CurrentDB.Execute. If you mean CurrentProject.connection.Execute, the reason I recommended RunSQL is that it isn't affected by what version you are in 97 and up, where as currentdb or currentproject.connection is going to be depending on which references you have set.

    Either way, the key is using INSERT INTO to force the Autonumber into 'rolling' over works.

    <img src=/S/grin.gif border=0 alt=grin width=15 height=15> I still think this is carryover.....

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Resetting Autonumber Seed (Access 2K)

    You're entitled to your opinions, Drew, here and elsewhere. I generally use the execute method on an object, either a command object or a connection object in ADO or a database variable or a querydef object in DAO. I realize that RunSQL works in 97 and up, but are you suggesting it just because it also works in 97? Since the question was in 2000, why not use what is currently available? I don't follow your logic there. I don't have a problem with what you suggested, even though I almost never use RunSQL, I was just pointing out the downside.

    PS Going *forward*, in 2002 you can use CurrentDb or CurrentProject without either the DAO or ADO reference being set.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •