[AccessD] SQL Server table autonumber

On 22 Dec 2005 at 8:04, John Colby wrote:

> 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?
> 

You need to get @@IDENTITY returned. I normally do it by using a sproc. 
Pass in the various fields as parameters for an update statement and follow 
it with a Select:

INSERT INTO usystblwsLog
(......)
VALUES
(@UserID, at ProjectName, at Login, at MachName, at WSL_ID)
SELECT @@IDENTITY AS 'Identity'

BTW, maybe your last string of questions would get a better response on the 
dba-SQLServer list.




-- 
Stuart