Solved

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

Posted on 2013-10-28
7
889 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 150 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

856 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