Solved

ADO Disconnected Recordsets and Bound Access Forms

Posted on 2015-02-17
15
599 Views
Last Modified: 2015-03-06
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
Comment
Question by:Jim
  • 6
  • 4
  • 3
  • +1
15 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 
LVL 84
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
 

Author Comment

by:Jim
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Jim
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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
 

Author Comment

by:Jim
Comment Utility
Thank you very much Scott!
0
 

Author Comment

by:Jim
Comment Utility
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
 
LVL 84
Comment Utility
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
 

Author Comment

by:Jim
Comment Utility
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
 

Author Comment

by:Jim
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now