VBA Code Example to Resync RecordSet after update

I have a MSAccess VBA application that is querying multiple Oracle tables.  I need to allow the user the ability  
to update records which are displayed in a continuous subform.  I am able to accomplish this by using OraOLEDB and then setting the Unique Table property of the form to one of the tables contained in the Recordset.

The problem is when I update a record I want to be able to resync information associated with the row effected.  

I have searched the internet and this site, but I have been unable to generate the code necessary to accomplish this.

cnnDB.Provider = "OraOLEDB.Oracle"
cnnDB.Open "raja", "hlsd", "h_l_s_d17"
rst.CursorLocation = adUseClient
rst.Open sqlstmnt, cnnDB, adOpenStatic, adLockOptimistic, adCmdText
Set Me.Recordset = rst
Me.UniqueTable = "LLARCHIVE_ANIM_US8592"
Me.ResyncCommand = sqlstmnt + keyfield = ?

Open in new window

Who is Participating?
DrSQLConnect With a Mentor Commented:
    We're all volunteers here, there's no staff.  It's just people who are willing to share for the love of the challenge and what we can learn.  While I do almost all my VBA coding in Word, I'm pretty good at it.  When I asked about the architecture I mean that I need to know what code is attached to userforms, what is event based, what is procedural, and what the context is.  If you can describe the flow of the application I might be able to help.  But in the past, for me, many of my solutions to these sorts of problems have been about the context.  If you want, you can try posting a note to the admin to get this question deleted and try again (experts tend to look for questions with little or no dialog started).  I won't object.

Good luck!
    Sorry if I'm being obtuse, but I can't quite get what you're trying to do.  Or what's keeping you from doing what you said.  If you just want to be able to re-execute the code you "snipped" then you have to have a cnnDB.close and an rst.close or the cursor and the connection stay open (and dedicated to the prior query).  If this is off-base, perhaps a little more background?

Good luck!
phsmythAuthor Commented:

I don't want to loss the recordset I just want the recordset to get "resync" 'd to reflect the changes the user makes to a particular row.

In the Form's "AfterUpdate" Event or someplace else (another person cited the OnCurrent Event), I need to somehow send the Key associated with the record updated to the resync command and then effectively resync the form.

I would have preferred to do this via the Recordset object itself, but I am unable to set the "Unique Table" property for a Recordset generated via OraOLEDB.

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

phsmythAuthor Commented:

In my preceeding comment I stated:

"I need to somehow send the Key associated with the record updated to the resync command and then effectively resync the form."

I should have said "resync" ' d the updated record contained in the recordset.

Also, an article that performs something similar to what I am attempting to do - WITH MS SQL SEVER HOWEVER - can be found at the following link

There is a problem with this link also in that they never tell you how to pass values to the Resync Command.

ok, I don't know what you're doing in line 10 of your snippet.  But, assuming you've populated your array successfully and use public variables for cnnDB and rst, then I think what you'd need to do in the event handler or trigger is:

                cnnDB.ConnectionString = DBConnect
      Set rst.ActiveConnection = cnnDB

      rst.Resync adAffectAll, adResyncUnderlyingValues

Which should resync the rst recordset.  But I don't know the context of "Me" in this case.  If the problem you are having is that the context is lost for the UniqueTable property when you want to do the resync, then I'd see if there's a way to pass that or declare it globally.  I'm still missing a lot of the architecture here.

Good luck!

phsmythAuthor Commented:

The architecture is a MSAcess VBA application connecting to an Oracle database.  The "Me" referred to in the code snippet is the MS Access form that I am binding to the recordset.

Is there someone on staff that is a VB/VBA, ADO to Oracle Expert that can help us out?  Unfortunately, VB can be extremely picky in what it expects.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.