get next value of an autoincrement field

  • Thread starter Thread starter frank
  • Start date Start date
F

frank

Hello everybody:
I would like to get the next value of an autoincremental field. I don't want
to know it by going to the last record ang getting the last value used. I
suppose access uses an internal table to mantain this sequence. Is possible
to access it?
How?

Thank you very much.
Frank
 
frank ha scritto:
Hello everybody:
I would like to get the next value of an autoincremental field. I don't want
to know it by going to the last record ang getting the last value used. I
suppose access uses an internal table to mantain this sequence. Is possible
to access it?
How?

Thank you very much.
Frank
Click to expand...

You have Two way to do it:

1) SELECT MAX(ID) AS LastID FROM T1
or
SELECT TOP 1 ID FROM T1 ORDER BY ID Desc

2)DMAX("ID","T1")

The first solution required Recordset usage

The 2nd no

@Alex
 
Frank

The "next" value ... do you mean which value will be used next, but before
you create a record that would use it?

If you are using a JET Autonumber field, remember that it could be set to
"Random". I suspect the same is true of a SQL-Server field used as
autoincrement.

So I'm curious... why would it matter what the value was going to be on the
next record? Why do you wish to know? I ask because it may be possible to
solve the problem a different way, if we knew what problem you are trying to
solve.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
The second option is perfect.
Click to expand...

I'm confused. The OP said, "I don't want to know it by going to the
last record ang getting the last value used," yet when someone posts
just this, it's deemed to be the perfect answer.

IMO this is not a reliable approach. Even with an incremental
autonumber, the last value created may not be the MAX for various
reasons e.g. a value was allocated but the transaction was rolled back,
an explicit value was used in an INSERT statement, the maximum INTEGER
value has been already been allocated but there are other positive
INTEGER values yet to be allocated, all positive integers have been
allocated and negative values are now being allocated, etc. And good
point about a random autonumber, Jeff.

I assume the next autonumber value cannot be known in advance. In the
case of an incremental autonumber it could possibly be predicted but
would require something so low level (e.g. file cracking with a hex
editor) as to be unworkable in production code.

Jamie.

--
 
Jamie said:
IMO this is not a reliable approach.
Click to expand...

I have to agree for the same reasons you point out. Max + 1 isn't guaranteed
to be the *next* autonumber.
I assume the next autonumber value cannot be known in advance. In the
case of an incremental autonumber it could possibly be predicted but
would require something so low level (e.g. file cracking with a hex
editor) as to be unworkable in production code.
Click to expand...

SELECT ((ID * 4096) + 20) AS WhereNextAutoNumIs
FROM MSysObjects
WHERE ([Name] = 'TableA');

... opening the file in binary mode and reading 4 bytes at that address and
converting the hexadecimal to long I think is workable. Whether it's
practical or advisable is another matter.
 
Granny said:
SELECT ((ID * 4096) + 20) AS WhereNextAutoNumIs
FROM MSysObjects
WHERE ([Name] = 'TableA');

.. opening the file in binary mode and reading 4 bytes at that address and
converting the hexadecimal to long I think is workable. Whether it's
practical or advisable is another matter.
Click to expand...

Sounds interesting. Excuse my ignorance (I am but a humble SQL coder
<g>) but can you please give an example on how to opening the file in
binary mode etc.

TIA,
Jamie.

--
 
Jamie said:
Excuse my ignorance (I am but a humble SQL coder
<g>) but can you please give an example on how to opening the file in
binary mode etc.
Click to expand...

Hon, you and I should stick to SQL and leave the file hacking to the
foolhardy. <g>
 
Back
Top