?
Solved

Access 2010: ODBC Oracle Table with acNewRec - getting the triggered Primary Key

Posted on 2013-10-28
7
Medium Priority
?
897 Views
Last Modified: 2013-11-03
I have an ODBC linked table with a form that has an Add button.
The Table has an Oracle Trigger that generates a unique ID from a sequence IN ORACLE.
This is triggered before the update on the Oracle side.


When the ADD button is clicked, I issue a
"DoCmd.GoToRecord , , acNewRec"
I set a few hidden txt boxes that are bound to the ODBC table.
I then look for the Primary key. It is still null.

This means that the Sequence is not being triggered just yet.
How do I get the  record "forced" into Oracle and still allow my user to input values into the new record?

Or any other ideas?
0
Comment
Question by:GNOVAK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39606743
The trigger will have fired. Unlike an Access backend, the text box isn't automatically populated (it will after a while depending on the ODBC refresh interval. I'd suggest that you use a Me.Refresh at the end of the VBA that adds the record. This is also common for ODBC links to SQL Server.

Kelvin
0
 

Author Comment

by:GNOVAK
ID: 39608404
The fields I have been populating are linked to the fields in the ODBC table but are hidden. These are in the detail section.  The header section has an "Add " button.

I have populated them after the "...acNewRec" line.
I then look for the me.txtRun_ID (key) and it is null.

Here's what I tried so far with no luck:
me.refresh - no effect
me.txtID.refresh - crazy, but no change
filling a visible field that is linked.
Relinking the table making sure it has a primary ID (RUN_ID)
None of the above works.

Since it is null, the code errors (I want it to at this point) and if I stop execution, the record appears in Oracle with an ID!

I'm open to ideas...
0
 

Author Comment

by:GNOVAK
ID: 39608681
I did find out that a SAVE macro button forces the save and refreshes the ID.
I would rather NOT use the save macro button and use VBA.

I suppose I could make it invisble but two questions on this -
How do I call it ?
Will it work if I deactivate menus when I compile the menu for users?

The other idea that may work is to create a function that does a SQL insert, filling a field with garbage, getting the id using a dlookup, deleting the garbage and returning the id.
Seems like a lot of unncessary work though.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 450 total points
ID: 39609409
Ah, I see. The acnewrec moves you to a new record. With linked Oracle and SQL Server databases, the ID is not created until you save the record - so you have to enter data into some of the visible fields and save the record before the trigger will fire and create the ID.

You can do the save in VBA - but whatever you do, you cannot retrieve the ID until after the save as it does not exist until then (unlike a linked Access database that will show you the ID, the moment you start entering the data).

Kelvin
0
 

Accepted Solution

by:
GNOVAK earned 0 total points
ID: 39609820
Yep -
The solution was as I mentioned above. Lots of hoops to go through, but it works.
I just call the sub that does a SQL insert and sends back the ID.  Then I add a filter and life is good.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39609839
No problems
0
 

Author Closing Comment

by:GNOVAK
ID: 39619705
The only solution that worked
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question