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

John,

You are going to have to force the save on the Access side
of the parent record and then the field on the form should
be populated.

Robert

At 12:00 PM 4/2/2007, you wrote:
>Date: Sun, 1 Apr 2007 13:24:42 -0400
>From: "JWColby" <jwcolby at colbyconsulting.com>
>Subject: Re: [dba-SQLServer] Getting the PK of a SQL Server table
>To: <dba-sqlserver at databaseadvisors.com>
>Message-ID: <003d01c77482$a4f09830$657aa8c0 at m6805>
>Content-Type: text/plain;       charset="us-ascii"
>
>Francisco,
>
> >I also would avoid your coding from the client end to add the record,
>instead I'd setup parameters and pass them to a sproc (stored
>procedure) and allow the sproc to return a value (scope_identity())
>
>Of course that is true.  Unfortunately my client is now entering the third
>week of the database corrupting 6 times per day.  I am doing an emergency
>port to SQL Server to get THAT problem stabilized.  I just want my
>APPLICATION to run, tweaks can follow.  For now, I have the code shown below
>and need to change it, 'cause it don't work no more.
>
>Dim RS As ADODB.Recordset
>     If mlngUserID = 0 Then Exit Function
>     Set RS = New ADODB.Recordset
>     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
>     Set RS = Nothing
>
>The code
>
>         mlngLogID = !LWSL_ID
>
>Fails because mlngLogID is a long int which cannot contain a null, and
>!LWSL_ID returns a null.  Thus I need an emergency fix that gives me back my
>new ID.
>
>John W. Colby
>Colby Consulting
>www.ColbyConsulting.com