accessing database autonumber feature

[Carrie]

I'm have a visual basic program that I'm using to get data out of an access
database. I want to show the ID field which is a autonumber. And on my program
I have an "Add New" field and when I click that I would like the autonumbering
feature to be there so they don't try to duplicate the ID#. Can anybody help?

I fought with this one for a bit when I started working with an Access database
again. I believe if you don't send the ID field and use an Insert statement,
the autonum field is populated, but then it is a mess to re-sync any FKs
that are dependent on the ID.

Finally I said "Screw it" and changed over to using GUIDs (Which are really
just Long Integer values) and couldn't be happier. The code is quite simple,
check out this link for an example.

http://www.freevbcode.com/ShowCode.Asp?ID=21

Pass the UDT, then the first Long value is a unique GUID key for your record.
I throw in a quick check for duplicates as well, but the odds of a duplicate
are quite low.

Steve.

"Carrie" <vb.@127.0.0.1> wrote:
>
>I'm have a visual basic program that I'm using to get data out of an access
>database. I want to show the ID field which is a autonumber. And on my program
>I have an "Add New" field and when I click that I would like the autonumbering
>feature to be there so they don't try to duplicate the ID#. Can anybody
help?

[Douglas J. Steele]

You should probably read Michka's
http://www.trigeminal.com/usenet/usenet011.asp for why this may not be a
good idea.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele


<vb.@127.0.0.1> wrote in message news:3d2f5a72$1@10.1.10.29...
>
> I fought with this one for a bit when I started working with an Access
database
> again. I believe if you don't send the ID field and use an Insert
statement,
> the autonum field is populated, but then it is a mess to re-sync any FKs
> that are dependent on the ID.
>
> Finally I said "Screw it" and changed over to using GUIDs (Which are
really
> just Long Integer values) and couldn't be happier. The code is quite
simple,
> check out this link for an example.
>
> http://www.freevbcode.com/ShowCode.Asp?ID=21
>
> Pass the UDT, then the first Long value is a unique GUID key for your
record.
> I throw in a quick check for duplicates as well, but the odds of a
duplicate
> are quite low.
>
> Steve.
>
> "Carrie" <vb.@127.0.0.1> wrote:
> >
> >I'm have a visual basic program that I'm using to get data out of an
access
> >database. I want to show the ID field which is a autonumber. And on my
program
> >I have an "Add New" field and when I click that I would like the
autonumbering
> >feature to be there so they don't try to duplicate the ID#. Can anybody
> help?
>

[Steve]

Nothing there poses a problem. I store PK's generated by VB as GUIDs in a
regular Long data type. They are "meaningless" keys, and would never, under
any circumstance need to be displayed to a user. As far as Jet and ADO have
ever been concerned, it's just a long value that doesn't allow duplicates.

This works extremely well but there are a couple simple rules:
#1. Should be treated as a meaningless key that is not displayed to a user.
#2. Best to have a alternative sorting field since new keys are not sequential.

Steve.

"Douglas J. Steele" <djsteele@canada.com> wrote:
>You should probably read Michka's
>http://www.trigeminal.com/usenet/usenet011.asp for why this may not be a
>good idea.
>
>--
>Doug Steele, Microsoft Access MVP
>http://I.Am/DougSteele
>
>
><vb.@127.0.0.1> wrote in message news:3d2f5a72$1@10.1.10.29...
>>
>> I fought with this one for a bit when I started working with an Access
>database
>> again. I believe if you don't send the ID field and use an Insert
>statement,
>> the autonum field is populated, but then it is a mess to re-sync any FKs
>> that are dependent on the ID.
>>
>> Finally I said "Screw it" and changed over to using GUIDs (Which are
>really
>> just Long Integer values) and couldn't be happier. The code is quite
>simple,
>> check out this link for an example.
>>
>> http://www.freevbcode.com/ShowCode.Asp?ID=21
>>
>> Pass the UDT, then the first Long value is a unique GUID key for your
>record.
>> I throw in a quick check for duplicates as well, but the odds of a
>duplicate
>> are quite low.
>>
>> Steve.
>>
>> "Carrie" <vb.@127.0.0.1> wrote:
>> >
>> >I'm have a visual basic program that I'm using to get data out of an
>access
>> >database. I want to show the ID field which is a autonumber. And on my
>program
>> >I have an "Add New" field and when I click that I would like the
>autonumbering
>> >feature to be there so they don't try to duplicate the ID#. Can anybody
>> help?
>>
>
>