Retrieving row- and field-level modification dates

Hello all,

(This was previously posted @. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1935938&SiteID=1&mode=1, I transferred it here because it's more relevant)

I have an Access database that contains (for the purpose of my application) a lot of information (15,000+ records in one of the tables).
I use C++ non-.NET ADO in order to access that database (msado15.dll).
My application suffers from slow performance because of the size of the tables (this is because the data has to be compared to data on a Palm device).
I have done a lot of optimizations to the code, but I haven't found a way to implement the best optimization for my case: Getting row- and field-level modification dates.
That way I can only compare relevant data and not the whole database.

For example, I wish there was a way to filter out rows from a table that were modified before January 1st, 2007.
Another example: Filter out rows that their Address column was modified in the last two weeks.

I know that a partial solution will be to implement this on my own: adding a Modified column to all my tables.
But, as I said, this is only a partial solution because it saves only the row's modification date, and not for each of the fields. Plus, this is a really ugly solution, having this column standing out everywhere.

Is there a chance this is implemented inside Access (or SQL server, for that matter)?
The closest I came was finding out that there's a field in msysobjects that specifies when a table was last modified. But not a row or a field in a row.

Any help will be greatly appreciated!

Thanks.

P.S. The weird thing is that this "modified" bit that I'm looking for is implemented on Palm devices and is very easy to use... Seems ironic that there won't be a simple way to do this on a PC.

P.P.S. I guess row- and field-level modification dates are the same... of course, Modified(row) = MAX(Modified(field1), Modified(field2), ...)

Hi Tim,

In SQL Server 2005 there has been row versioining feature implemented which can help you but this has some reatining capacity to certain time period depeding on the settings for your Engine.

Read about it more in BOL.

If you have lesser than SQL server 2005 version then you may have to create columns which track these changes based on date.

Can you tell me if this database is OLTP or OLAP database ?

Cheers

Sat

|||Hi Sat

You seem to know more than me about different kinds of databases and database technologies.
I don't know what's BOL, OLTP or OLAP.
I use Microsoft Access, and accessing it through OLEDB (.NET).