Nulstil autonummerering i db

compact and repair the database

If you have a record with 33 in the even if you compact repair it will start at 34!
Vacant numbers will NOT get re-used!

Det er præcis det jeg gør.

<snip>
"Jeg har slettet en del poster, komprimeret/repareret databasen, men alligevel..."
</snip>

Autonummer vil ALTID blive det højest brugte nummer +1

Hvis du har en record i din tabel med nummer 33, så er det nye ID (efter komprimering) 34 !!!

I had a table with 34 post. Then I deleted the 29 of them, so now I only have 5 posts.

Then I compacted and repaired the database, but instead of starting the new posts with 5, it starts with 35.

but waht is the LAST number?

proaccess>>Vil det sige at hvis jeg har en database med 20 poster og sletter de 10, kan jeg så ikke få den til at fortsætte med 11 igen?

Det kunne jeg ellers tidligere (se http://www.eksperten.dk/spm/190537 ), det er det jeg ikke forstår.

pelkjaer as proacess says it will start from the highest number + 1
you can NOT re-use empty numbers laying between existing numbers

terry>>I'm not sure you get it.

If I have a table with 20 post (1-20), and I deleted the 10 of them (11-20) and then compact the database, is it then not possible to continue with the number 11?

yes it is, but you DID NOT say which ones you were deleting (I cant see from here) :o)

Send me the dB and I will take a look dktebrsa@europe.lego.com

Or try and import the objects (tables etc.) into a new dB

Okay thanks. Please tell me the solution afterwards :)

OK!

>pelkjaer:  Dit problem er hvis du har en tabel med posterne 1-30 og derefter sletter 1-15, så er 30 stadig højeste nummer, og næste ID (selv efter komprimering) vil blive 31 !!!

proacces>>Nej, fordi jeg sletter kun de bagerste/sidste. Fx. 15-30 ud af 30.

terry>>when do you think you can send it back?
Not to rush you, but I kind of need it :O)

hi! I have looked at the dB and I can NOT find out what the problem is :o( I have also a problem when I make anothe dB table!
Will get back as soon as possible, may need searching on the web

Okay thanks Terry.

I think it is strange too, because I have done it before..

are youby any chance using XP? I can convert the dB to 97 and it works but NOT in XP!

I AM using XP prof. :o)

And Access 2000..

But I think I might as well just create a new database. Otherwise I'll get behind...

But thanks anyway :o)

IF you delete ALL records then it works. If you only delete the some records then it doesnt. There is an error in XP, which I am checking up on. Will post a reply when I know some more :o)
thanks!

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q257408
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q287756

Hey Terry. Did'nt receive an email :o(

Thank you.

This is what you need

    Function ChangeSeed(strTbl As String, strCol As String, lngSeed As Long) As Boolean
    'You must have a reference to ADO and MS ADO ext. 2.x for DDL and Security
   
    '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

Jep :o)

I'll remember that next time. This time I just created a new db :)