The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Question on ADO database programming

I am looking in to using ADO for some hobby related programming projects.
It involves an MS Access database.

I have managed to open the database and read data from it, but I can't seem to figure out how I can get notifications if someone else has changed some of the data.

For instance, if I open a recordset and list the results in a listbox, I can walk through the list, showing details and those details will reflect changes I have made in Access.

Now, how do I get informed of such changes as soon as they have been made, allowing me to update the list immediately?

As a side note, I though opening a recordset with a static cursor would not allow me to see the changes at all, but I do...
Practical Geezer
Wednesday, December 01, 2004
My ref indicates that opening a recordset with a dynamic cursor will give a recordset that is automatically updated with and changes to the data.  Doesn't indicate any kind of notification, so woudl be up to your app to re-read from recordset each time it is displayed?
Wednesday, December 01, 2004
Umm, you can't?

The SQL Relational Database Model is a different world than procedural programming.  It is more oriented toward logical query's returning a recordset than real-time notification of things.

In some databases, there is an 'on-update' Trigger, that you tie some Stored Procedure code to.  In Access you could write a Module triggered by some 'ON_xxx' event to signal your program -- but this wouldn't work unless Access were actually running.

You could use the 'COUNT' modifier in an SQL query (run periodically) to determine when records were deleted or added -- wouldn't help with a modified record, of course.
Wednesday, December 01, 2004
I did notice however, that if I have the database opened in Access, and I change the data from somewhere else, Access updates its view without my interaction.
Practical Geezer
Wednesday, December 01, 2004
It refreshes the table from time to time if you are just opening the table in the standard manner and looking at it as you make changes to it.

Depending on the locking mode, this behavior can change. If you have the db opened exclusively for instance, or if you have object-level locking on, instead of record level locking, it will not update when you watch it.

If you want to be notified of the recordset's changing, it sounds like you basically want a 'trigger' in Access. There is no such animal.

However, if I needed a trigger in Access, I would explore the use of the timer control on a form. The timer could execute a macro every so often that copied/compared table versions. You'd be keeping a 'last version checked' and a 'current version' dataset and the timer would execute code to run a comparison of the tables and email the resulting data, then it would copy the current version table over the lat version checked table.

The caveats are that some machine has to keep the db and form open at all times and when the copy/compare is going on, you will probably have exclusive locks on the tables.
I am Jack's Access trigger
Wednesday, December 01, 2004
Hmmm, alternatively, it should be trivial to make a data entry form and build a form of notification into it. As long as your users edit through the form rather than the table directly, the form could contain code to build a log of changes made.
I am Jack's Access trigger
Wednesday, December 01, 2004
You have to requery the data to refresh your local copy.
Simon Lucy Send private email
Wednesday, December 01, 2004
Thanks to all.
You have confirmed my suspicion that there is no ready made event that asynchronously notifies me of changes that affect a recordset.

I explored the use of a timer to periodically check for changes, but decided against it.
Instead, I will only check for changes when the user performs an operation that will be affected by such changes.
Practical Geezer
Wednesday, December 01, 2004
1 - Add some columns for tracking changes:

AddDate <- When was this record added?
AddUser <- Who addded this record to the database?
ChangeDate <- When was this record last touched?
ChangeUser <- Who last touched this record?

I call these 4 columns "the stamping fields." When the tables are my design, they have these 4 columns added all the time (or equivalent names like "CreateDate" if the company has their own naming standard).

When a row is inserted, insert the logged in user's name to the AddUser/ChangeUser columns, insert Now() into AddDate/ChangeDate.

When a row is altered, ChangeUser = who's logged in, ChangeDate = Now().

2 - Keep a copy of the old data in a "logging table."

When a row is altered, throw the original stuff into a log table. "Log table" is the same as the original table with primary key removed, and a new column added (which just inserts Now() into that column). If you have lots of activity, you might want to trim the log tables regularily (my experience is that if the error hasn't been caught in a year, forget about ever figuring out *why* it was changed).

Select * from YourTableHere where ChangeDate >= LastTimeYouLookedAtTheData.

If some idiot continually messes stuff up (lik spellign errerrs all da thyme), you can find who did it (hmm, jdoe changed that record right before he was fired). If some process messes things up, you can find out (hmm, SnailMailZipCodeValidator touched that at 901am on 11/30). With a log file, you can see who, what and when things changed.

Think of the 6 "W"s that a good reporter should have in their story: Who, What, When, Where, Why and hoW. The first 3 are usually enough to keep track of. If you have a distributed system (users all over the place, and several ways of importing the data), then keeping track of where and how can be helpful. Only a bureaucratic joint will want to keep track of why.

Under DAO, you could use CurrentUser in Access, but that part of the object model disappeared under ADO. A stupid move, in my opinion.

Helpful? Confusing? WTF?
Wednesday, December 01, 2004

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz