Solved

VBA Code Example to Resync RecordSet after update

Posted on 2008-06-20
6
2,626 Views
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

0
Comment
Question by:phsmyth
  • 3
  • 3
6 Comments
 
LVL 22

Expert Comment

by:DrSQL
ID: 21834126
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
 

Author Comment

by:phsmyth
ID: 21834629
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
 

Author Comment

by:phsmyth
ID: 21834777
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Expert Comment

by:DrSQL
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
      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
 

Author Comment

by:phsmyth
ID: 21835715
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
 
LVL 22

Accepted Solution

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 “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…

770 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