Solved

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

Posted on 2013-10-28
7
864 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now