[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


VBA Code Example to Resync RecordSet after update

Posted on 2008-06-20
Medium Priority
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
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
  • 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.

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.

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 1500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

649 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