Its driving me crazy, please help me

[yomyom]

Hello all,

I've nearly completed a sales ledger application (using Ms sql and delphi) but one concept is holding me back.

I want to share the idea so I can find out if its alright and how to do it.

Idea: At the end of each year, I would naturally want to store the year's data (sales database) in a directory called 2003 (for this year, 2004 next year ...etc and rename the database 2003 or 2004 etc.) and then delete the tables in the sales database so that fresh data can be captured during the following year. The customer number file and the lastbal tables would not be affected since they point to the firms customers and thier last account balances. All other tables would be collapsed.

The user can then refer to the database stored in 2003 (assuming it works and we're now in 2004), directory to view / print the previous year's transactions but not be able to alter the database(read only).

The problem: How to achieve the above, either through code with delphi or with T-sql (so that I can still perform it from my delphi).

when I do it manually i do the following:
1. switch off the server
2. make a new directory i.e \2003
3. copy sales database i.e sales.mdf and log.ldf to \2003
4. rename the sales database in \2003 i.e sales.mdf to 2003.mdf and sales.ldf to 2003.ldf.
5. turn back on the server
6. use sp_attach_db to place the 2003 database in the server

How can I do it all through Delphi code ?
(I've tried CopyFileto, but its not satisfactory or I did not use it well, ...)
I hope answers will flow....

yomyom.

 

[svanels]

I would do this through a stored procedure.
In this case the server will take care of empting and backing up the data.
With SQL-Server only the administrator, or table(s) owner would have the right to perform the action.
But why you want to delete historical data? In your Reports or queries limit the time span.
SQL-Server? let it work it designed to hold data.

Steven van Els
SAvanEls@cq-link.sr

 

[LorenPechtel]

I'm afraid I don't know any good psychiatrists to refer you to. Sorry. <G>


One issue: You shouldn't do this at the end of the physical year, but rather when the user says to.

 

[yomyom]

Thanks 4 your responses. The advantage of time and hind sight is immense. It seemed so necessary when I was fussing.....
SAVanels is right .... I'll limit the queries though dates etc.
Thanks all.

 

[lespaul]

As a former accounting professional, I would also like to point out that you have to allow your users to make prior year adjustments. If you were to have made the information read only, it would have made those adjustments very difficult to implement.



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!