We're starting to use Oracle for the back-end instead of a separate
Access .mdb file for the data and everything as gone surprisingly well
so far. We are learning Oracle as we go; Yikes! But we have very
patient DBA's at the main office to answer newbie questions.
Using Access 2002 with linked ODBC tables to Oracle 9 (9i? not sure),
ODBC driver is SQORA32.DLL, verson 9.02.00.00.
My "Error 3167: Record is deleted" comes when I use .AddNew, .Update,
..Bookmark, then try to grab the "faked" Autonumber ID of the record
just entered, like I've done in Access many times before (code is
below).
For most things I have the users enter records into forms and
everything works just as it did with the tables in an Access back-end.
But for this table (long story) I have them click a button to get the
new record started, then I want to make that record current so they can
finish putting values in the rest of the fields.
This is how I've faked an Autonumber ID in Oracle:
1. set up the ID as primary key
2. set up a Sequence
3. set up a Trigger:
CREATE OR REPLACE TRIGGER MFD.BIFER_SIGLEGFEATURE_ID_PK
BEFORE INSERT
ON MFD.SIG_LEG_FEATURE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
SELECT siglegfeature_seq.NEXTVAL INTO :NEW.FEATURE_ID FROM dual;
END bifer_siglegfeature_id_pk;
This seems to make FEATURE_ID behave just like an Access Autonumber
field except in this instance.
' /\/\/\/\/\/\/\/ START CODE
Private Sub btnAddFeature_Click()
On Error GoTo Err_btnAddFeature_Click
Dim rst As Recordset
Dim rstClone As Recordset
Dim intID As Long
If IsNull(txtLegNbr) Then
MsgBox "Please select a Leg/Approach", , "NO LEG SELECTED"
Else
Set rst = CurrentDb.OpenRecordset("SIG_LEG_FEATURE")
With rst
.AddNew
.Fields![LEG_NUM] = txtLegNbr
.Fields![SYSTEM_ID_NO] = curSysID
.Update
.Bookmark = .LastModified
'** This next line throws the error 3167 **
intID = rst.Fields![FEATURE_ID]
End With
'** I'm kind of rusty on using bookmarks, too ...
Forms![frmMainSignals]![frmFeature].Form.Requery
Set rstClone = Me.RecordsetClone
rstClone.FindFirst "[FEATURE_ID] = " & intID
If rstClone.NoMatch Then
MsgBox "Not found"
Else
' Display the found record in the form.
Forms![frmMainSignals]![frmFeature].Form.Bookmark =
rstClone.Bookmark
End If
End If
Exit_btnAddFeature_Click:
On Error Resume Next
rst.Close
Set rst = Nothing
rstClone.Close
Set rstClone = Nothing
Exit Sub
Err_btnAddFeature_Click:
MsgBox "Error " & Err.Number & ": " & Err.DESCRIPTION,
vbInformation, "Add Feature ERROR"
Resume Exit_btnAddFeature_Click
End Sub
' /\/\/\/\/\/\/\/ END CODE
I've set a breakpoint at the top and checked the data in Oracle using
TOAD and the new record is really there and FEATURE_ID is filled in
with the next number in sequence as soon as the .Update line is run.
The .Bookmark line also runs fine. The Recordset just doesn't seem to
know that the value of FEATURE_ID is now filled in. So it calls it
deleted??
Should I be making a dbUseODBC workspace? I haven't had to deal with
workspaces before, when using Access back-ends.
Any advice on this would be greatly appreciated. THANKS!
Denise 4 3288
My References are:
1. VBA
2. Access 10 Object Library
3. DAO 3.6 Object Library
4. Office 10 Object Library (I don't usually include this but I'm doing
something elsewhere that needed it. Maybe Application.FileSearch?)
Hello.
Why do you use before triger for autoincrement ID primary key ?
I don't know how it works with Oracle, but in PostgreSQL (should be similar
to Oracle) you can set field datatype to BIGSERIAL. The server then creates
the sequence itself, so there is no need for any trigger. I suppose that
there is something similar in Oracle...
I avoid triggers. I use them only for audit trail...
Bye.
<dh*******@yahoo.com> je napisao u poruci interesnoj
grupi:11**********************@g44g2000cwa.googleg roups.com... We're starting to use Oracle for the back-end instead of a separate Access .mdb file for the data and everything as gone surprisingly well so far. We are learning Oracle as we go; Yikes! But we have very patient DBA's at the main office to answer newbie questions.
Using Access 2002 with linked ODBC tables to Oracle 9 (9i? not sure), ODBC driver is SQORA32.DLL, verson 9.02.00.00.
My "Error 3167: Record is deleted" comes when I use .AddNew, .Update, .Bookmark, then try to grab the "faked" Autonumber ID of the record just entered, like I've done in Access many times before (code is below).
For most things I have the users enter records into forms and everything works just as it did with the tables in an Access back-end. But for this table (long story) I have them click a button to get the new record started, then I want to make that record current so they can finish putting values in the rest of the fields.
This is how I've faked an Autonumber ID in Oracle: 1. set up the ID as primary key 2. set up a Sequence 3. set up a Trigger: CREATE OR REPLACE TRIGGER MFD.BIFER_SIGLEGFEATURE_ID_PK BEFORE INSERT ON MFD.SIG_LEG_FEATURE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE BEGIN SELECT siglegfeature_seq.NEXTVAL INTO :NEW.FEATURE_ID FROM dual; END bifer_siglegfeature_id_pk;
This seems to make FEATURE_ID behave just like an Access Autonumber field except in this instance.
' /\/\/\/\/\/\/\/ START CODE Private Sub btnAddFeature_Click() On Error GoTo Err_btnAddFeature_Click Dim rst As Recordset Dim rstClone As Recordset Dim intID As Long
If IsNull(txtLegNbr) Then MsgBox "Please select a Leg/Approach", , "NO LEG SELECTED" Else
Set rst = CurrentDb.OpenRecordset("SIG_LEG_FEATURE")
With rst .AddNew .Fields![LEG_NUM] = txtLegNbr .Fields![SYSTEM_ID_NO] = curSysID .Update .Bookmark = .LastModified '** This next line throws the error 3167 ** intID = rst.Fields![FEATURE_ID] End With
'** I'm kind of rusty on using bookmarks, too ... Forms![frmMainSignals]![frmFeature].Form.Requery Set rstClone = Me.RecordsetClone rstClone.FindFirst "[FEATURE_ID] = " & intID If rstClone.NoMatch Then MsgBox "Not found" Else ' Display the found record in the form. Forms![frmMainSignals]![frmFeature].Form.Bookmark = rstClone.Bookmark End If
End If
Exit_btnAddFeature_Click:
On Error Resume Next rst.Close Set rst = Nothing rstClone.Close Set rstClone = Nothing
Exit Sub
Err_btnAddFeature_Click: MsgBox "Error " & Err.Number & ": " & Err.DESCRIPTION, vbInformation, "Add Feature ERROR" Resume Exit_btnAddFeature_Click
End Sub ' /\/\/\/\/\/\/\/ END CODE
I've set a breakpoint at the top and checked the data in Oracle using TOAD and the new record is really there and FEATURE_ID is filled in with the next number in sequence as soon as the .Update line is run. The .Bookmark line also runs fine. The Recordset just doesn't seem to know that the value of FEATURE_ID is now filled in. So it calls it deleted??
Should I be making a dbUseODBC workspace? I haven't had to deal with workspaces before, when using Access back-ends.
Any advice on this would be greatly appreciated. THANKS! Denise dh*******@yahoo.com wrote: We're starting to use Oracle for the back-end instead of a separate
I don't use Oracle as linked tables in Access, I tend to use pass
through queries and will also be using ADO eventually. So I don't
really have anything Oracle related to suggest.
CREATE OR REPLACE TRIGGER MFD.BIFER_SIGLEGFEATURE_ID_PK BEFORE INSERT ON MFD.SIG_LEG_FEATURE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE BEGIN SELECT siglegfeature_seq.NEXTVAL INTO :NEW.FEATURE_ID FROM dual; END bifer_siglegfeature_id_pk;
I don't use the DECLARE line nor do I qualify the end statement with the
name of the trigger as you've done here, but it's working so I guess
it's OK.
With rst .AddNew .Fields![LEG_NUM] = txtLegNbr .Fields![SYSTEM_ID_NO] = curSysID .Update .Bookmark = .LastModified '** This next line throws the error 3167 ** intID = rst.Fields![FEATURE_ID] End With
Have you tried removing the rst? It seems to me the line causing the
error should be:
intID = .Fields![FEATURE_ID]
Should I be making a dbUseODBC workspace? I haven't had to deal with workspaces before, when using Access back-ends.
I've never bothered.
Not much help, I know, but I take interest in Oracle related questions
and I saw this one had not been answered.
On Zlatko's post, you do need a trigger to incorporate a sequence with a
table.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Thanks Zlatko and Tim for responses.
I've worked around trying to find out what the Feature_ID is on the
newly inserted record. Instead I show them a small form that makes them
define the last piece I need to make a unique new record. Then I insert
the record and show them the main form which they can tweak at this
point. I don't think removing the superfluous rst made a difference.
The working code is below.
Thanks again!
Denise
Private Sub btnAddFeature_Click()
On Error GoTo Err_btnAddFeature_Click
Dim rst As Recordset
Dim rstClone As Recordset
Dim intID As Long
Dim varNewRec As Variant
intFeatureID = 0 ' set as Public in MainMod, stays 0 if user
cancels frmNewFeature
If IsNull(txtLegNbr) Then
MsgBox "Please select a Leg/Approach", , "NO LEG SELECTED"
Else
DoCmd.OpenForm "frmNewFeature", , , , , acDialog
If intFeatureID <> 0 Then
Set rst = CurrentDb.OpenRecordset("SIG_LEG_FEATURE",
dbOpenDynaset)
With rst
.AddNew
.Fields![SYSTEM_ID_NO] = curSysID
.Fields![LEG_NUM] = txtLegNbr
.Fields![FEATURE_TYPE_ID] = intFeatureID
.Fields![INSTALLED_DT] = Date
.Update
End With
Me.Requery
Me.Refresh
Set rstClone = Me!frmFeature.Form.RecordsetClone
rstClone.FindFirst "[SYSTEM_ID_NO] = " & curSysID & " and
[LEG_NUM] = " & txtLegNbr & " and [FEATURE_TYPE_ID] = " & intFeatureID
If rstClone.NoMatch Then
MsgBox "Not found"
Else
'Display the found record in the form.
Me!frmFeature.Form.Bookmark = rstClone.Bookmark
Me!frmFeature.Form.Controls!INSTALLED_DT.SetFocus
End If
End If ' end of if intFeatureID <> 0
End If ' end of if isNull txtLegNbr
Exit_btnAddFeature_Click:
On Error Resume Next
rst.Close
Set rst = Nothing
rstClone.Close
Set rstClone = Nothing
Exit Sub
Err_btnAddFeature_Click:
If Err.Number = 3146 Then
MsgBox "That feature already entered for this approach. No
action taken", vbInformation, "Feature Exists"
Else
MsgBox "Error " & Err.Number & ": " & Err.DESCRIPTION,
vbInformation, "Add Feature ERROR"
End If
Resume Exit_btnAddFeature_Click
End Sub This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Sue Adams |
last post by:
I actually have two issues/questions:
I have an autonumber field in an access db table that I grab and later use to update a record in another table withing the same db.
The code I use to get...
|
by: Surendra |
last post by:
I have this query that I need to use in an Update statement to
populate a field in the table by the value of Sq
----------------------------------------------------------------------------
Inline...
|
by: rrh |
last post by:
I am trying to update a field in one table with data from another
table. The problem I'm running into is I need to base the update on a
range of data in the 2nd table.
Table 1 has:
date field...
|
by: Lee C. |
last post by:
I'm finding this to be extremely difficult to set up. I understand
that Access won't manage the primary key and the cascade updates for a
table. Fine. I tried changing the PK type to number and...
|
by: EKL |
last post by:
Hi,
I'm making a sort of Customer and Orders database in MS Access 2003. My
problem is that I wish to update the table "tblTransaction" based on changes
made in the table "tblOrderDetails"....
|
by: PAUL |
last post by:
Hello,
I have 2 tables with a relationship set up in the dataset with vb
..net. I add a new record to the parent table then edit an existing child
record to have the new parent ID. However when I...
|
by: Darin |
last post by:
This is something that on the surface seems like it should be simple,
but I can't think of a way to do this. I have a table that is a list
of "jobs", which users create and use. It has a single...
|
by: travhale |
last post by:
in a new project using .net 2005, c#.
getting err message "Update requires a valid UpdateCommand when passed
DataRow collection with modified rows."
source RDBMS is oracle 8i. I add a new...
|
by: ARC |
last post by:
Hello all,
So I'm knee deep in this import utility program, and am coming up with all
sorts of "gotcha's!".
1st off. On a "Find Duplicates Query", does anyone have a good solution for...
|
by: tracyyun |
last post by:
Hello everyone,
I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
|
by: giovanniandrean |
last post by:
The energy model is structured as follows and uses excel sheets to give input data:
1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
|
by: NeoPa |
last post by:
Hello everyone.
I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report).
I know it can be done by selecting :...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
|
by: isladogs |
last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, Mike...
|
by: GKJR |
last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
|
by: SueHopson |
last post by:
Hi All,
I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...
| | |