Last auto-generated replication-id

[Peter Laman]

Hi,
In an MS Access table I have a one-field primary key, that is an auto-
increment field of type Replication-id. After inserting a single
record using SQL INSERT, I want to obtain the key of the new record. I
have done this before with auto-increment fields of type Numeric and
then I can use "SELECT @@IDENTITY AS fieldname". But this doesn't work
for replication id's. So how do I find my new record?

Peter.

 

[Guest]

try Max(IDENTITY )

 

[David W. Fenton]

In an MS Access table I have a one-field primary key, that is an
auto- increment field of type Replication-id. After inserting a
single record using SQL INSERT, I want to obtain the key of the
new record. I have done this before with auto-increment fields of
type Numeric and then I can use "SELECT @@IDENTITY AS fieldname".
But this doesn't work for replication id's. So how do I find my
new record?

That is not suppored for Jet databases. Your only reliable method to
get the value is the same as with a standard Jet Autonumber field,
which is to do the Add in a recordset and store the value.

 

[David W. Fenton]

try Max(IDENTITY )

Er, what makes you think the values will be added sequentially? This
*is* a replicated environment, where sequential values wouldn't be
usable.

 

['69 Camaro]

Hi, Peter.

So how do I find my new record?

GUID's can be difficult to work with, but if there's no way to get around
using the GUID, then place a unique index on your candidate key that doesn't
allow NULL's (if you haven't already). When you insert a new record, you
can find it again by querying for the values in the candidate key, since
there's only one record in the table with these values. When that record is
found, check the column with the GUID for its value if you need to use it as
a foreign key.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Peter Laman]

Hi, Peter.


GUID's can be difficult to work with, but if there's no way to get around
using the GUID, then place a unique index on your candidate key that doesn't
allow NULL's (if you haven't already). When you insert a new record, you
can find it again by querying for the values in the candidate key, since
there's only one record in the table with these values. When that record is
found, check the column with the GUID for its value if you need to use it as
a foreign key.

HTH.
Gunny

Seehttp://www.QBuilt.comfor all your database needs.
Seehttp://www.Access.QBuilt.comfor Microsoft Access tips and tutorials.
Blogs:www.DataDevilDog.BlogSpot.com,www.D....QBuilt.com/html/expert_contributors2.htmlfor contact
info.

Well, finally I decided not to have the key auto-generated, but rather
call CoCreateGuid myself and assign its value. The result is the same
and it eliminates the problem.
Thank for all responses.

Peter

 

['69 Camaro]

Good job! I'm glad you found a reliable solution. Thanks for sharing it
with the newsgroups, so that others may benefit, too.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.