Hi there.



I have been developing an access database with two tables. The first table

gets a record inserted into it and then the ID generated from that table is

used to insert several other records into another table using the auto-id

from the first table. Pretty standard right? Well during the development I

encountered a couple of situations where a process was not completed- Like

the first insert occurred but the second did not. SO I went in a manually

deleted the record in the first table to start again. But I am finding now

that the auto-id field is returning some strange results. Such as the

database has 143 records in Table1, and therefore the auto-id for the last

record is - you guessed it- 143.But when I perform a query to sort all

records by the auto-id DESCENDING and ask for the first id in the recordset

I get 152.

I check the DB manually again  and I find that the last record is infact

still 143. SO I delete all the records except for 1 and see what happens- I

still get 152 as the last record. I don't get it. And the .mdb file is still

the same size after I delete 142 records.



Anybody know what gives?



Spencer

Autonumbers are not meant to *number* your records.

There are they to act as a unique primary key. Don't worry about "missing" a

few numbers - just worry about them being unique.



In answer to your second question:

The database doesn't get any smaller because you have not run the "compact

and repair" utility (available from the Tools menu, or programatically via

the Jet Replication Object). It is not efficient from a file-system point of

view if the .mdb file shrank everytime you deleted a record - because the

database would need to get more space from Windows file system as soon as

you added a new record. Instead, the record is marked as deleted, and the

database uses this "empty space" to store any new records you add.



Lastly, you could use transactions to ensure that you get records inserted

into both thables.



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Spencer Saunders" <ssaunders@i...>

Subject: [access_asp] Autonumber problem in ACCESS





: I have been developing an access database with two tables. The first table

: gets a record inserted into it and then the ID generated from that table

is

: used to insert several other records into another table using the auto-id

: from the first table. Pretty standard right? Well during the development I

: encountered a couple of situations where a process was not completed- Like

: the first insert occurred but the second did not. SO I went in a manually

: deleted the record in the first table to start again. But I am finding now

: that the auto-id field is returning some strange results. Such as the

: database has 143 records in Table1, and therefore the auto-id for the last

: record is - you guessed it- 143.But when I perform a query to sort all

: records by the auto-id DESCENDING and ask for the first id in the

recordset

: I get 152.

: I check the DB manually again  and I find that the last record is infact

: still 143. SO I delete all the records except for 1 and see what happens-

I

: still get 152 as the last record. I don't get it. And the .mdb file is

still

: the same size after I delete 142 records.

:

: Anybody know what gives?

:

: Spencer



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~