closing database question

[SidCharming]

I work with numerous databases where I have to freeze the current database in time and data for retreiving reports. Every aspect of the database needs to stay in tact whether reports are run the day of close or a year from closing. The reason is for FDA reporting.

Is this a feature that has to be built into the database? or is there any other method?

My current method is this:

1. copy the mdb to a restrictive area for no more data entry
2. Build a module that resets the current computer clock to the closing date (fixes the now() function calculations)


Any insight or help would greatly be appreciated!




Sid from Minnesota

 

[stotzc001]

Sid,

The way I see it, you have 2 issues. The fisrt being user access. The only trouble with copying the database to another location is that someone could log on to the database in the original location and enter data. A better option would be to open the database in exclusive mode. The help setion of Access talks about setting this option. It will prevent anyone else from logging into the database while you have it open.

The second issue is setting the date and time for your reports/queries. My solution is to have a form called reports with a control where you would input the date that you want to run the report. Then Link all the queries/reports to the date value in the control. You can even make it a drop-down list. That should take care of it for you.

If you need any more help let me know.

Chuck

 

[BNPMike]

I am a consultant in preserving data for retail and investment banks for compliance purposes, amongst others (legal, tax, audit etc). I've not seen this problem so it suggests there is something slightly odd about your application. To report a position in the past is, I would have thought, a routine thing so doing for a long time in the past presents no special challenge.

Possibly you just need to replace all now() with a specific timestamp parameter.

 

[SidCharming]

First issue is the database will still be receiving new records and updating after the lock. The lock is to preserve the data up to that point in time when reports are generated

Second, the databases I am using I have inherited and I am working on new db's with built in functions that call on a date field instead of the now() funciton.

I will have to use something like Speed Ferrit to search and replace the now() function with a specific date. I was hoping I didn't have to do that or change my computer system clock if the now() replacement does not work.

Thanks for the insite, comments and help!!


Sid from Minnesota

 

[stotzc001]

Sid,

I'm a little confused on the locking issue. I thought you just wanted to restrict user input for a given period of time. What it sounds like you want to do is take the database at a given point in time and save it to a location, then allow users to start inputing data in a fresh database sort of like an archive function. A good example would be to have a database called "Current_Year.mdb" that exports itself to a closeout database at a given point in time "Records_2004.mdb" then empty all the tables in "Current_Year.mdb" so that when users open the database it will only have data from that point on. If you need to run a report, you can always load the old database. If this is the case, let me know and I have a couple of solutions for you.

Second, was the database you inherited written using a lot of VBA or Queries? If it was using queries, it should be fairly easy to locate the date references and swap them out.

Chuck

 

[SidCharming]

Okay, there technically is only one database. with data saves (snapshot in time best describes these 'locked' databases). I guess I am using wrong lingo when I say locked... Our clinical group referrs to saving the database away from the continuously data entered one as 'Locking the database'. What I really am doing is copying the database when they give me the go ahead. I copy it to a directory that has only read rights to all other users. My problem is that the database was written with some VBA and queries that use the NOW() function or DATE() function that kick out reports. With the older databases I inherrited I am having to change my system clock to the date of the 'lock' so the reports return current data based on the system (modified) clock.

The reports I have inherrited have at least 3 layers of queries that compile each report. Some of them are so well bound with their current configuration they don't work when I replace the now() or date() funcitons.

As I am moving forward with new reports I am limiting my queries to at most one query per report. My current objective is to imbed the queries inside the report, instead of having a stand alone query that other users can monkey with.


Sid from Minnesota