C & R

[pdanes]

Is it perhaps related to deleted data fields? I was able to reproduce this error.
The space is only released again after Compact.

I wouldn't think so - I don't delete or create any fields in these tables.

You did get the error, though? How did you finally do it?

 

[pdanes]

@pdanes - My first question to you would be to check the table when this is done to see if it renamed the autonumber field. Normally, you don't care about the name of the field THAT much because, as noted, autonumbers are in theory not supposed to have any meaning other than "unique record number." If there is no table that depends on the table in question (i.e. no child table exists), then there are also no links involving this field and you would have no collateral issue in a "hidden" rename.

I do not rename the field. I posted the code that does all this in #23. The tables are used in a join, but are not defined as linked via foreign keys.

 

[ebs17]

With CurrentProject.Connection
    .Execute "DELETE * From tmp" & x & "AutoID"
    .Execute "ALTER TABLE tmp" & x & "AutoID ALTER COLUMN RaditPodle Number"
    .Execute "ALTER TABLE tmp" & x & "AutoID ALTER COLUMN RaditPodle COUNTER(1, 1)"
End With

You change the same field RaditPodle twice. Do you notice that?
If you are serious about a temporary table, you will delete it and create a new one. When you delete it, all the legacy data disappears.

With CurrentProject.Connection
    .Execute "DROP TABLE tmp" & x & "AutoID"
    .Execute "CREATE TABLE tmp" & x & "AutoID ( RaditPodle COUNTER )"
End With

 

[pdanes]

Okay, I created a tiny loop calling that routine. It reliably crashes on the second ALTER, which does the reset.

BUT...

It DOES NOT crash when I comment out the only first ALTER.
It DOES NOT crash when I comment out the only second ALTER.
It crashes 100% reliably after 127 iterations when I execute BOTH the ALTER statements.

 

[pdanes]

You change the same field RaditPodle twice. Do you notice that?
If you are serious about a temporary table, you will delete it and create a new one. When you delete it, all the legacy data disappears.

Yes, I noticed that - I mentioned it in #23. I also realized that I don't really need to reset it to one. I use it in an Order By clause, and all I really care about is the sequence. I suppose I could skip the reset and just let it keep increasing, maybe reset it if it ever gets close to max LONG INTEGER, which is not likely. Or maybe I can keep resetting it, but only reset - see previous post.

 

[Josef P.]

It DOES NOT crash when I comment out the only first ALTER.
It DOES NOT crash when I comment out the only second ALTER.

Because then no new data field has to be created for AutoNum.

Test files:

 

[pdanes]

Because then no new data field has to be created for AutoNum.

Test files:

Then why does it crash when I execute both?

 

[pdanes]

Because then no new data field has to be created for AutoNum.

Test files:

I just added this line to the routine, after the second ALTER:

    .Execute "Insert Into tmp" & x & "AutoID (ZaznamAutoID) Values(1)"

No change - if I comment out either one of the ALTER statements, it runs with no problem. When they both execute, it crashes after #127.

 

[Josef P.]

Then why does it crash when I execute both?

Because Jet/ACE seems to need a new data field for the conversion from Number to AutoNumber (also vice versa) all changes of data type and if a new field is inserted, the old one has to be deleted. Deleted data fields only release the space again after compact.

/edit: tested with:

db.Execute "ALTER TABLE " & TabName & " ALTER COLUMN ID byte"
db.Execute "ALTER TABLE " & TabName & " ALTER COLUMN ID Long"

Change data type => will crash after n loops

 

[pdanes]

Because Jet/ACE seems to need a new data field for the conversion from Number to AutoNumber and if a new one is inserted, the old one has to be deleted. Deleted data fields only release the space again after compact.

Oooohhh.. - you mean the first ALTER changes it to a regular number, and the second ALTER changes it back to an autonumber?

Now that makes sense. It would explain why I only get to 127, instead of 255 - two changes with each cycle. And why it makes no difference whether I add data or not. And why there is no problem when I comment out either ONE of the statements - because then JET is presumably smart enough to see that the field is already that way, and nothing needs to be done.

So what I was actually running up against was not the autonumber starting point reset, but the fact that I was (inadvertently and twice) actually changing the field type, back and forth between plain number and autonumber. Simply resetting the autonumber start was blameless in this, and can actually be done an unlimited number of times.

Jeez - I feel like a complete dork, but it all makes sense now. I'm guessing that first ALTER was from some initial testing when I was first putting this together, and I just forgot to remove it when I put in the second one. Or more likely, I commented it out, to leave the text in place if the new code didn't work properly, then accidentally hit Ctrl/Z one too many times after dinking around with subsequent edits, and never noticed that I re-enabled this line.

Okay, I guess I owe MS an apology for this one. And you a round of beers. If you ever get to Prague, look me up.

Many thanks.

 

[Josef P.]

[OT]

If you ever get to Prague, ...

I only have to drive about 500 km north.