Jim Dettman
jimdettman at verizon.net
Wed Nov 6 08:40:06 CST 2013
Lambert, The logic is sound, but the problem is the ODBC table and that JET works with keyset driven cursors. You should read through the following: "#Deleted" errors with linked ODBC tables http://support.microsoft.com/kb/128809 and see if there's anything you might do differently with the table. With SQL Server for example, an easy fix in many cases is to add a timestamp column to the table (which is a misnomer as it's not a timestamp field, but works to turn on row versioning). Not sure if you can do anything with the table, but that logic typically does work. 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