updating one record

  • Thread starter Thread starter Remy B
  • Start date Start date
R

Remy B

I have a view on MSSQL which I load into a form in Access 2000 via ADO.
The view takes up to 5 seconds to generate depending on the number of
rows I ask for. On the same form are text boxes where the user can edit
various database values which are in turn used in the view's query.

My problem is that when the user edits these values, I need that to be
reflected in the controls that show the result of the view. The obvious
method is to refresh the form's query, but that can take a few seconds,
which is way too long when the user is only editing one record at a
time. Ultimately the user only needs to see the updated results of the
view for the record that the form is up to.

Is there a way to update a single record in an ADO recordset without
updating all of them? I have tried to use the Resync method, but it
seems to rely on the UniqueTable property, which I can not set,
presumably because the view joins many tables and contains many fields
that are not directly from the source tables (eg. there are some fields
in the view that are concatenations of source table fields). However,
the view does have a one-to-one relationship with the rows of one of
the source tables, and the primary key of that table is in the output
fields of the view.

Many thanks for anyone who can wrap their head around that and give me
a useful response :)
 
woohoo! i found the problem.

i couldnt set the UniqueTable property because i did an ORDER when i
was querying the view from the Access form. when i took that out the
UniqueTable could be set, and then the ResyncCommand property of the
form could be set, and then

Me.Recordset.Resync adAffectCurrent

updated the current row without requerying the whole view.
 
Remy,

Could you please provide me with an example of how you set the UniqueTable
property. I'm fairly new to programming ADO. I'm attempting to bind an ADO
recordset consisting of multiple joined tables and I get the infamous
"Recordset not updatable". Upon research I discovered the UniqueTable
property, yet no where saw an example of how to use it. Thanks.

Jerry
 
Back
Top