[AccessD] SQL Server table autonumber

The simplest way is SELECT @@IDENTITY, executed right after your insert
statement. If you are using a sproc, then you could declare an OUTPUT
parameter and use the above statement to set its value. But if you insist on
using dynamic SQL then execute the statement above immediately after your
insert.
Merry Christmas!
Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
rusty.hammond at cpiqpc.com
Sent: December 22, 2005 10:15 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] SQL Server table autonumber

John, for a linked table in Access using DAO recordset I do the following:

.AddNew
!LWSL_IDLWSU = mlngUserID
.Update
.BookMark = .LastModified
mlngLogID = !LWSL_ID

HTH,

Rusty

-----Original Message-----
From: John Colby [mailto:jwcolby at colbyconsulting.com]
Sent: Thursday, December 22, 2005 7:05 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] SQL Server table autonumber


In Access (Jet, MDB BE) when I create a new record in a table with an
autonumber, 

        .Open "usystbllwsLog", gcnn, adOpenKeyset, adLockPessimistic
        'build a logout record.
        .AddNew
        !LWSL_IDLWSU = mlngUserID
        !LWSL_FE = CurrentProject.name
        !LWSL_Login = blnLogIn
        !LWSL_WorkstationID = CurrentMachineName()
        mlngLogID = !LWSL_ID
        .Update

I grab the new PK before doing the update.  When ported to SQL Server the
!LWSL_ID in the next to the last line is null.  How do I grab the PKID from
a new record when using SQL Server as the BE?

John W. Colby
www.ColbyConsulting.com 
Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com