Autonumber still broken?

  • Thread starter david epsom dot com dot au
  • Start date
D

david epsom dot com dot au

Running Microsoft Access 2000 (9.0.6926 SP-3),
with MSJet40.dll 4.0.8618.0 (SP-8, but not the
most recent hotfix).

I run this query on a linked table, and the autonumber
seed goes from 1030 to 560:

INSERT INTO tbl_B ( idxAutoNumber ... strReportAlias )
Select idxAutoNumber ...strReportAlias
FROM (tbl_A LEFT JOIN tbl_B
ON tbl_B.idxAutoNumber = tbl_A.idxAutoNumber)
LEFT JOIN tbl_B_1
ON tbl_B_1.strReportAlias = tbl_A.strReportAlias
WHERE ((tbl_B.idxAutoNumber Is Null)
AND (tbl_B_1.strReportAlias Is Null));

Table A is local and has 559 Records, Table B is linked
and has 1029 records.

From Table A, the query inserts record 519, which is
missing in table B. In Table B the next autonumber value
becomes 560: the seed has apparently been reset downwards
using the value from the source table: but 560 is already
taken in Table B: the seed should only reset upwards, and
it should not reset from the value in Table A.

Is there anything I can do or fix or install to avoid
this repeatable error?

(david)
 
G

Gary Walter

Very funny imposter....

I cannot help but think that
the real "david" whose posts
I have read over the years
(and learned much from)
would know that the ONLY
thing one uses an autonumber
field for is to guarantee uniqueness...
and if you want to maintain a
consecutive series of numbers,
you maintain them yourself.

You owe "david" an apology.

gary
 
D

david epsom dot com dot au

Gary Walter said:
Very funny imposter....

I cannot help but think that
the real "david" whose posts
I have read over the years
(and learned much from)
would know that the ONLY
thing one uses an autonumber
field for is to guarantee uniqueness...
and if you want to maintain a
consecutive series of numbers,
you maintain them yourself.

You owe "david" an apology.

gary
Click to expand...


{whimper}

That is the least of the problems: the table in question
needs to be continous because it has an incorrectly
declared relationship to logically unrelated records.

I see that there is a KB article
http://support.microsoft.com/default.aspx?scid=kb;en-us;884185


(david)
 
Top