reset an empty counter/autonumber to 1 without compacting 1

[PizMac]

I have a table with an autonumber/counter which has all records deleted (through code) and I wish it to reload the table starting at 1 (again through code). If I copy and paste the table (structure only) in the database window this has the desired effect but I can't find a way of coding this. I don't want to compact every time I run through the code either.
Any ideas please?

 

[PHV]

ALTER TABLE yourTable ALTER COLUMN yourColumn COUNTER(1,1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PizMac]

I'd already tried that thanks but it doesn't reset to 1 - if there were 9 records in the table, I delete them, then add more and do the "alter" they still start at 10.....
code below:-
DoCmd.RunSQL "DELETE * FROM IpDetails;"
DoCmd.TransferText acImportFixed, "DSFspec", "IpDetails", myFullFile, False, ""
DoCmd.RunSQL "ALTER TABLE Ipdetails alter COLUMN RowNum COUNTER(1,1)"

 

[PizMac]

Sorry PHV - I may have spoken too soon - am testing agian now - will get back to you

 

[PHV]

I think the correct order of operations is:
DoCmd.RunSQL "DELETE * FROM IpDetails"
DoCmd.RunSQL "ALTER TABLE Ipdetails ALTER COLUMN RowNum COUNTER(1,1)"
DoCmd.TransferText acImportFixed, "DSFspec", "IpDetails", myFullFile, False, ""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PizMac]

PHV - yes - thanks a bundle - it works !!!! I had previously tried ADDING a counter not altering one and that sometimes gave me the records in an odd order but as far as I can see this has solved my problem - I owe you a drink!