• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2670
  • Last Modified:

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

0
phsmyth
Asked:
phsmyth
  • 3
  • 3
1 Solution
 
DrSQLCommented:
phsmyth,
    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!
0
 
phsmythAuthor Commented:
DrSQL:

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.

Pat
0
 
phsmythAuthor Commented:
DrSQL:

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
http://microsoft.apress.com/asptodayarchive/72009/resynchronising-disconnected-ado-recordsets

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

Pat
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
DrSQLCommented:
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
      cnnDB.Open
       
      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!


0
 
phsmythAuthor Commented:
Dr SQL:

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.

Pat
0
 
DrSQLCommented:
Pat,
    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!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now