VBA Code Example to Resync RecordSet after update

Posted on 2008-06-20
Last Modified: 2013-12-18
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

Question by:phsmyth
  • 3
  • 3
LVL 22

Expert Comment

ID: 21834126
    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!

Author Comment

ID: 21834629

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.


Author Comment

ID: 21834777

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.

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

LVL 22

Expert Comment

ID: 21834938
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!


Author Comment

ID: 21835715

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.

LVL 22

Accepted Solution

DrSQL earned 500 total points
ID: 21837204
    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!

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

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

13 Experts available now in Live!

Get 1:1 Help Now