[dba-SQLServer] Getting the PK of a SQL Server table

Well, I can see that the first is not what I want.  It would seem, in a
multi-user environment that the second is also not what I want.  The third I
am incapable of understanding well enough to evaluate it.  What exactly does
scope mean in this context?  If I use the last method, then I change my code
as follows?  Scope_Identity() sounds suspiciously like a SQL Server
function, remember that this is VBA code running in my FE.  Is VBA going to
understand a SQL statement "SELECT SCOPE_Identity()" or anything remotely
like it?

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim
Lawrence
Sent: Sunday, April 01, 2007 8:58 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Getting the PK of a SQL Server table

Hi John:

Check these scripts out:

<quote>
SELECT @@IDENTITY
This is everyone's favorite function, unchanged from earlier versions of SQL
Server. It returns the last IDENTITY value produced on a connection,
regardless of the table that produced the value, and regardless of the scope
of the statement that produced the value.

SELECT IDENT_CURRENT('tablename')
This new function returns the last IDENTITY value produced in a table,
regardless of the connection that created the value, and regardless of the
scope of the statement that produced the value.

SELECT SCOPE_IDENTITY()
This new function returns the last IDENTITY value produced on a connection
and by a statement in the same scope, regardless of the table that produced
the value.
</quote>

HTH
Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Sunday, April 01, 2007 5:13 AM
To: 'Access Developers discussion and problem solving';
dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Getting the PK of a SQL Server table

In an Access database the PKID (autonumber) of a table is available the
instant after doing the insert or AddNew, so I would use code such as:
 
    With RS
        .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
        .Close
    End With

the code line
 
        mlngLogID = !LWSL_ID

fails when used in SQL Server because the ID is not yet valid.  How do I
work around this when the table is out in SQL Server?
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com