[AccessD] Stored Procedure not producing results

I have no way to verify whether you mused on this subject or not, but since you asked, I will provide a thumbnail sketch:

0. You could do it with triggers, but that would not be my first choice.
1. every table will contain two columns called BeginDate and EndDate.
2. any inserted row will have a NOT NULL value in BeginDate, not necessarily equivalent to today (otherwise you couldn't insert a row that takes effect on January 1, 2007).
3. any row  without an EndDate is assumed to be effective now.
4. any update to an existing row copies its data to a new row, fills in the EndDate column on the existing row with GetDate(), and supplies GetDate() as the BeginDate on the new row.
5. Any "deleted" row is not actually deleted. Rather, it is preserved, but its EndDate column is updpated to GetDate().

That's a quick thumbnail sketch of PITA. I may have left out a quirk or two in the sketch, but that's the basic idea. Nothing is destroyed. Everything is kept, and its time-span is demarcated by its BeginDate and EndDate columns. 

Taking a practical example, I just switched doctors yesterday. This involves several operations.

1. The "family physician" has changed, but do not destroy the previous data. Therefore insert a new row whose BeginDate is yesterday, and update the previous row, changing its EndDate to yesterday.
2. Agree that all previous medical data concerning me shall be forwarded to the new physician. (Called Consent, in the medical lingo.)
3. Step two can be further refined, as in "release data about conditions x, y and z, but not t, u and v" to my new physician.

At the end of the day, for various important reasons, not the least of which is litigation, we must be able to determine the state of your particular record(s) in the database as of January 1, 2004. Who was your physician? What rights did she have to your prior data? What tests were performed upon you, and on what dates, and with what results and with what follow-ups?

I chose this example a) because I have been there and written that, and b) because it illustrates the problem vividly. We could easily substitute lawyers for the physicians, or dentists, or any professional, including ourselves, software developers. Consider a project as a metaphorical person in the above. In 2001, the practitioner list comprised Fuller, Colby and Salakhetdinov. In 2002, it comprised Hindman, Carbonell, Colby and Lawrence. In the medical schema, one could easily have several practitioners, one for heart, one for ENT (ear, nose, throat), one for gynecology, etc. And the same holds true for a software client -- she employs an accounting expert, a CRM expert, an Excel macro wizard, an Access expert, and so on.

That was a bit of a sidetrack, but remains on point, I think. The point being, "We need to be able to roll back the database to any given PIT without actually having to do a restore. We just want to specify a date (Jan 1 2004) and without changing a single line of code, run the reports.

That is the point of PITA.

It must be designed in from the outset. Not every app needs PITA, but it becomes very difficult to add it after the fact. As anyone who has followed my threads must know by now, I tend toward extremism. That said, I suggest that you design PITA in, just in case it later becomes needed. In that case, you look like a genius. And if the requirement never emerges, you wasted some very cheap disk space. 

Arthur
 ----- Original Message ----
From: Shamil Salakhetdinov <shamil at users.mns.ru>
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Sent: Wednesday, November 22, 2006 8:27:43 AM
Subject: Re: [AccessD] Stored Procedure not producing results


<<<
So. Today's job is to dig out that PITA article and pitch it to somebody.
>>>
Arthur,

I must say I did "muse" on similar to your PITA ("Point in Time
Architecture") subject somewhere in year 1992 or 1993, that time in FIDO
groups and in Russian...

Am I wrong - wasn't that feature of versioned database data somehow limited
but built-in way implemented in Borland Interbase?

And in MS SQL Server it can be relatively inexpensive way
implemented/simulated using triggers - correct?

--
Shamil