[AccessD] Getting the PK of a SQL Server table

John,

If you're working with linked SQL server tables in Access the LastModified
property does the trick for me in a DAO recordset.  I don't know if it's
available in an ADO recordset like you are using but the following is how it
might be used.
 
    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()
        .Update
        .BookMark = .LastModified
        mlngLogID = !LWSL_ID
        .Close
    End With

HTH
Rusty

-----Original Message-----
From: JWColby [mailto:jwcolby at colbyconsulting.com]
Sent: Sunday, April 01, 2007 7:13 AM
To: 'Access Developers discussion and problem solving';
dba-sqlserver at databaseadvisors.com
Subject: [AccessD] 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
 
-- 
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.
**********************************************************************