[AccessD] Getting the PK of a SQL Server table

And in fact that is just what I did (more or less).  Since this is framework
code I strive for "any environment" so I changed it to  

        !LWSL_WorkstationID = CurrentMachineName()
	  On Error resume next
        mlngLogID = !LWSL_ID
        .Update
	  if mlngLogID =0 then
            mlngLogID = !LWSL_ID
	  endif
        .Close

The first will work correctly for an MDB and if the value is still zero then
the second picks up the slack.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
rusty.hammond at cpiqpc.com
Sent: Sunday, April 01, 2007 1:23 PM
To: accessd at databaseadvisors.com; dba-sqlserver at databaseadvisors.com
Subject: Re: [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.
**********************************************************************
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com