Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-10-28
7
Medium Priority
?
902 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
Independent Software Vendors: 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!

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

604 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