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

ADO Disconnected Recordsets and Bound Access Forms

First, I would like to say hello and offer my earnest thanks in advance for any help that an Expert Exchange member can provide.  Here is my scenario I have created a recordset that is disconnected so users can change multiple data points.  The recordset was pulled from a table with around 100,000 rows of data 39 columns wide.  I feel that I have a pretty solid understanding of how to create a basic recordset and bind it to a form.  My big issue is that once that has been done I cannot seem to write code that can save any changes a user makes.  I have looked around a bunch and it appears that access loses the flag for edits to data when the recordset is connected to a form.  I did check this and it does indeed seem that they are lost because when I call .status it returns an  8 which is adUnmodified.  I have read a few users who had used beforeupdate or afterupdate in the form events to help catch edited records but that is my issue I am not sure how they went about that.  So I guess that's my question how do I build a save function that catches updates in the recordset? For good measure how would you go about cancelling changes of a disconnected recordset (I am just recalling the connected recordset currently)? Assume both save and cancel would be attached to a button.  Oh and if there is a way to change lots of datapoints without saving until I press the save button while keeping the recordset connected that would be awesome to know.  Thank you very much for your time and effort.  -Jim
0
Jim
Asked:
Jim
  • 6
  • 4
  • 3
  • +1
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Your disconnected recordset should be saving the changes to each underlying record, so you could loop through the recordset and write basic SQL statements to handle the update. I believe I'd add a "Dirty" field to the recordset, and then set that when the form dirties the record (there's a Form Dirty event you could use). You could then only update those records which have been modified:

rst.MoveFirst

Do Until rst.EOF
  If rst("Dirty") Then
    '/ made changes, so write SQL
   Dim s As STring
   s = "UPDATE YourTable SET Field1=" & rst("Field1") & ", Field2=" & rst("Field2") & etc etc & " WHERE IDField=" & rst("IDField")
   YourConnection.Execute s
  End If
  rst.MoveNext
Loop
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Couple of things:

1. Access bound forms won't work with a disconnected record set.  They require an active connection in order to work properly.

2. Given #1, that means no form events and you will need to work unbound.  All the CRUD operations are up to you.  You need to provide the users with a means of saving, canceling, deleting, etc via command buttons and then carry out the operations on the record set.

3. Usually with a disconnected record set, most track changes with a Last Date/Time modified field and set that field when the user clicks "save".  Then when the record set is reconnected, update based on the flag.  

 With those points in mind,  I would strongly suggest taking a different approach, either through UI changes (a slimmed down record select screen, then fetch one record at a time for editing, and using a bound form), or a temp table with a bound form.

  I say that because there are a load of in's and out's to working with ADO and Access to start with (there are actually very few cursor types that you can use with ADO and Access for example), and even more so with disconnected recordsets as it simply goes against the grain of the way Access was designed to work.

 I'm not saying you can't get it to work in some fashion, but your going to have to write a lot of code to support it and may have to employ work arounds for things that Access would normally handle, like keeping track of what's been modified.

Jim.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access bound forms won't work with a disconnected record set.  They require an active connection in order to work properly.
If you set the cursor location to adUseClient, they'll work as a standard bound recordset - but they won't save data back to the source. You have to do that yourself.

Here's the MSFT article on it for 2003 (which is also relevant for other flavors as well): https://support.microsoft.com/kb/281998
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<ut they won't save data back to the source.>>

That's what I meant by "won't work".

If you use a disconnected record set, then you need to do something in one form or another to handle CRUD operations.

Jim.
0
 
JimAuthor Commented:
Just wanted to start by saying thanks to both you for your comments.  I did put a lot of time into building this out and wanted to finish with ado if possible.  So under the private dirty form event how would I indicate current record that is being edited?  After indicating the record set I guess I would add something like rst.field("isdirty").value = true as I made the field Boolean.  Thanks again for the support!
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
I'll let Scott finish this off as I typically work with DAO and I know he has a lot more ADO experience than I have.   Plus I haven't worked with ADO disconnected recordsets for at least ten years.   What I remember is that in general, they were difficult to work with and there were easier ways to accomplish what I needed.

Jim.
0
 
PatHartmanCommented:
Jim,
Why did you choose to use an unbound form?  Were you having trouble with bound forms?  As Jim Dettman has already mentioned, that isn't the "Access" way.  You loose a significant amount of the RAD capabilities of Access since you are completely responsible for all the CRUD operations.  I've been working with a variety of RDBMS databases and find that bound forms work fine as long as I bind them to queries with selection criteria to limit the rows returned.  Occasionally I'll need to create a view or for certain bulk updates use a pass-through query but in general, the Access way works if you use good client/server techniques.
0
 
JimAuthor Commented:
I guess mainly so I can eventually hook up the Access front end to a sql server instance which will be needed as the database gets bigger and has more users. That and it's kind of nice just having a few vb modules instead of 50 different queries. Though I do agree with Jim a temp table would have been substantially easier.  đŸ˜
0
 
PatHartmanCommented:
If you create your bound forms correctly, it doesn't matter how may rows or how many concurrent users.  Some of my linked tables have millions of rows.  I'm only updating one at a time so I prefer to not write all that code.  Remember, Access passes through every query as long as you don't do something to prevent it.  If your query selects one row, that is all you get back from the server.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can check the .Status of each Row in that recordset to determine if it's been changed, added, or deleted.

If rst.Status = adRecModified Then
  '/ run an update statement
Elseif rst.Status = adRecNew Then
  '/ run an Insert statement
ElseIf rst.Status = adRecDeleted Then
  '/ run a Delete statement
End If

You can then "walk" the recordset and commit the changes.

You may also be able to use the UpdateBatch method, if you use a KeySet cursor:

Dim con As New ADODB.Connection
con.ConnectionString = "Your connection string"
con.open

rst.ActiveConnection = con
rst.UpdateBatch , True

UpdateBatch method: https://msdn.microsoft.com/en-us/library/windows/desktop/ms675283(v=vs.85).aspx

I've never used the UpdateBatch method, as I preferred to do the work myself.
0
 
JimAuthor Commented:
Thank you very much Scott!
0
 
JimAuthor Commented:
Scott one more thing when I use status  it returns an 8 on modifcation and 262144 when I add a record.  Which is odd.  Also I am using me.recordset to get those results as the rst variable I set up has an "object variable or with block variable not set up" error.  Not sure how to get around this issue.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you using the Enum values? You should be, since the actual "value" of the Enum can change, but the "name" of the enum will not. So basically you'd do this:

Do Until rstTitles.EOF
        If rstTitles.Status = adRecModified Then
            Debug.Print rstTitles!title_id & " - Modified"
        ElseIf rstTitles.Status = adRecDeleted Then
          '/ do something
        ElseIf rstTitles.Status = adRecAdded Then
          '/ do something
        Else
          '/ default here
        End If
 
            Debug.Print rstTitles!title_id
        End If
    rstTitles.MoveNext
    Loop


From here: https://msdn.microsoft.com/en-us/library/windows/desktop/ms681022(v=vs.85).aspx
0
 
JimAuthor Commented:
Scott here is how I have the code set:

Private sub form_dirty(cancel as integer)
Select case me.recordset.status
Case adRecModified
me.recordset.fields("updatedfield").value = "updated"
Case adRecAdded
 me.recordset.fields("updatedfield").value = "added"
End case
End sub

if I change me.recordset with rstData the actual disconnected record set I get the error.  If I keep me. Recordset when it goes to update the fields I get multi step operation generated errors.  Again thanks for your time!
0
 
JimAuthor Commented:
I finally see my problem for some reason when I use the forms .dirty event it erases my global variables which makes my record set empty.  How do I get around this?
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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