[AccessD] Getting the Primary Key of a New records in Oracle

 Here's another more recent article with a little more info:

PRB: Explaining "Record is deleted" error accessing ODBC table
http://support.microsoft.com/kb/172339 

One thing that you can sometimes do as well is use a different unique index
on the JET side.  This is not at all obvious.  You can find the technique
discussed here:

Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204.aspx

Read the section "Adjusting Dynaset Behavior" in "Understanding and
Addressing Performance Issues".  Also in "Understanding and Addressing
Updatability Issues" , read "Specifying a Unique Index".

While this article was written for SQL Server, I don't see why what is
stated would not work for any ODBC data source and it might get you around
your problem above.

Jim.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Wednesday, November 06, 2013 09:18 AM
To: 'Access Developers discussion and problem solving';
'ACCESS-L at PEACH.EASE.LSOFT.COM'
Subject: Re: [AccessD] Getting the Primary Key of a New records in Oracle

That looked really promising, but as soon as the nPrimKey = ![ID] line
executed I got an error 3167: Record is deleted. ;-(

Sub GetLastModfied()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim nPrimKey As Long

    Set db = curdb
    Set rs = db.OpenRecordset("IB_Amounts", dbOpenDynaset)
    With rs
        .AddNew
        !CovId = "Test"
        !strComment = "Testing getting primary key"
        .Update
        .Bookmark = .LastModified
        nPrimKey = !ID
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
    Debug.Print nPrimKey, DLookup("Description", "IB_Amounts", "ID=" &
nPrimKey)
End Sub

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rusty Hammond
Sent: Tuesday, November 05, 2013 4:49 PM
To: Access Developers discussion and problem solving;
ACCESS-L at PEACH.EASE.LSOFT.COM
Subject: Re: [AccessD] Getting the Primary Key of a New records in Oracle

Lambert, use the LastModified property:

     With rst
	  .AddNew
             ![SomeField1] = "SomeValue"
             ![SomeField2] = "SomeValue2"
        .Update
        .BookMark = .LastModified
        intPriKey = ![PrimKeyID]
    End With

HTH,

Rusty

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Tuesday, November 05, 2013 3:14 PM
To: 'Access-D Email (accessd at databaseadvisors.com)'; 'ACCESS-L
Email(ACCESS-L at PEACH.EASE.LSOFT.COM)'
Subject: [AccessD] Getting the Primary Key of a New records in Oracle

Does anyone know how I can retrieve the primary key value from a new record
just added to a linked Oracle table using VBA?

I.e. I create a records using VBA, populate the fields with VBA and update
the record. How do I then find out what the primary key value allocated by
Oracle is? The key field is of type NUMBER(10) and it is populated by a
trigger (which I have no control over).

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

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com