Row- and field-level modification dates

Hello all,

This is the best suited forum I found that fits my problem. If there's a better one, please direct me to it.

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).
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), ...)
This mechanism of auditing can be implemented in Acces with help of AfterUpdate Event code and in SQL with triggers.
I should create a table
ID(identity) Column DataofModifying(DataTime default value) Before After

When the user do something, after update you can write in this table.

There is no automatic way (wait some time because I heard Katmai, SQL Server 2008 have data auditing)|||

There is no specific, built in, way to determine if a row or field has been altered. In situations where alteration auditing is requried, it is often necessary to use 'a really ugly solution' and add ChangedDate columns to every table. (And other columns necessary for auditing are often added as well.)

However, SQL Server does provide 'Replication' where data changes are constantly 'set aside' in a special set of tables to be sent to the replicant. I don't think that would be a solution for your situation.

|||Thanks for replying so quickly.

I think that is amazing.
Are you saying that this feature exists on all Palm devices (even the old black-and-white ones), but is missing from all data server solutions?

I find that extremely hard to believe.

Just for argument's sake, here is a quote out of Palm OS Conduit Development Kit 6.01, describing the eRecAttrDirty flag:

eRecAttrDirty = 0x40 Indicates that the record has been marked as modified. This replaces the older constant DIRTY_BIT.

|||

You should recognize that Palm is an 'application', not a data server.

.NET applications also have the capability to track data changes to a datset.

But SQL Server does not offer the functionality you had hoped to find.

|||Hi again.

Can you elaborate on the functionality in .NET applications?

That might be the solution I'm looking for, if there's any way to make it interact with Access.
|||

Yes, .NET applications can use data stored in Access databases.

But you would be better positioned to post your .NET/Access questions in a .NET (VB/C#) or Access related forum. Not as many folks here that are deeply very familiar with those environments.

|||Alright then, I'll direct this question to a more suitable forum.

Thanks for the help!
|||

SQL Server has a data type called "timestamp", which is really a "row version" and has nothing to do time. You can use this to determine whether a row has been modified when (manually/in your app) synchronizing against another database.