pizzaguy

As simple as it sounds I just cant find a good example. I've searched google and read through many forum posts here.

I've been programming for quite some time (yes, still using VB6), however new to integrating access database files into my programs.

I can load the entire dbase tables into separate flexgrids, but getting it back seems to be a challenge (at least for me).

Could someone please give me an example of:

1) Updating a particular row(s)/column(s) from flexgrid to dbase (change made)
2) Sending entire flexgrid back to dbase (ie, save on close)

Thank you much in advance,

Joe

pizzaguy

A little help guys?

loquin

Well, you need to update the recordset used to populate the flexgrid. Then, apply the changes back.

One approach would be to use disconnected recordsets. (adUseClient, static cursor type, batch update lock type)
After connecting to the table and loading the data, you set the rs.ActiveConnection to nothing. This allows your app to operate without any connection to the table at all.

Then, Whenever you change the data in the grid, make a corresponding change to the data in the recordset also.

And, when you want to force the data changes back into the database, open the connection (if it was closed,) set the activeconnection again, and perform the batch update.

Now, there could be a LOT of changes to the table, and you could possibly have errors in one or more records that you are trying to update, so you need to handle these.

You'll probably want to follow a structured approach in your updates.

• Pre-Process the recordset. Scan through the entire recordset, and look for any data that might cause a problem. Each field in each row has properties that can tell you if the data has changed.
• Perform the update.
• Post-Process (Handle errors/synchronization)

Rob Macdonald devotes the better part of a chapter in his book, "Serious ADO:Universal Data Access with Visual Basic" (APress) to disconnected recordset processing. I would STRONGLY suggest that you track down a copy (Used copies are pretty cheap at half.com or amazon)

I dont have the time to go into all the details, but, essentially, after the batch update, you can adjust the recordset filter property so that recordsets with errors are the only records 'showing' after your batch update. If the recordcount is zero, then you had a clean update with no errors. Otherwise, you handle each record. Depending on the error, you might abort the whole update, might report the error, might log the error, or maybe report/log, and create an 'error' table with the attempted records inserted there, for manual cleanup.

loquin

Another approach might be, if you can stay connected, is to use ADO's page mechanism to only load a page at a time (you define the number of records per page, per recordset,) of records into the recordset, then use datagrids bound to the recordset for display. You edit the data on the grid, and the recordset gets updated simultaneously. And when you propagate the data change back up to the database, you'll know, then and there, if there's an error.

pizzaguy

Thank you for the reply loquin. I'm new to ADO and still trying to figure out how to use it properly. Would it be possible to provide a small project where-in a flexgrid, recordset, and mdb file communicate? Just a small example of how to get modified data from the flexgrid back to the mdb file? I've got the rest figured out, just need to figure out how to do this. Thanks again.

hDC_0

There are many many examples of using ADO with VB6 if you do a search.

Okay..I guess I'll throw you a bone..here's one.

It's "Private Sub cmdAddEntry_Click" has the code to
"refresh the data source [the mdb file] and rebind it to the flexgrid".

pizzaguy

Thank you much hDC_0. Just one more question. In that example, if I wanted to MODIFY an entry in the flexgrid how would I update the mdb file? This shows me how to remove and add an entry but not modify. Thanks!

hDC_0

In ADO, when you want to modify data (say to a bound MsFlexgrid control),
its called an Update.

Specifically you want to do a Recordset.Update.
I guess a few more links are needed.. links(1, 2, 3) .

In case you ever move on to shaped ADO,
the code is a little different.

pizzaguy

Shoot, I think I got it. I'm starting to get this whole ADO thing! Thanks again guys

dilettante

As far as I know the FlexGrid controls are "static." When you data bind them they make a one-time copy of the data, since they are meant only as display controls. Thus bound data updating isn't possible.

As the manual clearly states:
If you use one of the data grid controls (DAO DBGrid or ADO Data Grid) it's another story.


Note also that MSFlexGrid was legacy in VB6, a holdover from VB4 & VB5 meant only for porting old software more easily. MSHFlexGrid was preferred for any new projects.