Link to home
Start Free TrialLog in
Avatar of Jim
Jim

asked on

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
SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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
<<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.
Avatar of Jim
Jim

ASKER

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!
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.
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.
Avatar of Jim

ASKER

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.  😁
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jim

ASKER

Thank you very much Scott!
Avatar of Jim

ASKER

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.
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
Avatar of Jim

ASKER

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!
Avatar of Jim

ASKER

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?