Archive deleted records - Best methods??

[jabhawk2]

I have a production system where we cannot PACK a table without archiving the deleted records. We have to maintain for a minimum of 3 years.

Does anyone have any recomendations on a procedure to handle these archives?

I can always write a routine to SELECT the deleted records to external tables and then PACK but I am limited to 8.3 naming so creating a significant filename is difficult. I would need to use a management routine to assign a unique filename and log the table date and count to a master table. Recovering the data would require getting the saved filename from the master table.

Anyone have this situation in the past, I would rather not reinvent the wheel. Code samples welcome. Batch processing required.

FP-Dos 2.6

 

[DSummZZZ]

Besides the 8.3 filename format, you also have the option of putting them in unique directories/folders.
Depending on how large the tables are or how often you need to archive, you could create folders like JAN07, FEB07, MAR07 to archive them monthly. If you only need to do it yearly, you could do something like ARCH2007. Then again, you could use that format and and create sub-folders of JAN, FEB etc.
Your lookup routine could use date functions to translate the date into a folder name if you needed to find records not in the 'live' tables.
Also, if you were to archive the records, you could DELETE and/or BLANK them. You could then recycle the records without the need to PACK them.



-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[r0gerc]

I had a problem creating a file with a long filename so I used TEXTMERGE to create a batch file which copies a file with a short name to one with a long name, then run it from within FoxPro.

shortname = [a short filename]
longname = [a long filename]

set safety off
set textmerge on
set textmerge to newname.bat noshow

\\@echo off
\if exist <<shortname>> copy /y <<shortname>> <<longname>>
\if exist <<longname>> del <<shortname>>

set safety on
set textmerge to
set textmerge off

run /n2 newname.bat

It's not quite that simple, but it might help with the naming problem.

 

[cricket]

If you carry out this routine on a daily or weekly basis, you can create significant filenames as follows:
YYMMMDD.DBF eg 07jun19

 

[dgrewe]

Just because the file does not have a >DBF extension does not mean Fox will not open the file as a table.

I have used the method of naming it the same as the original filename and changing the extension to HF1, HF2, etc. This Recycles the filenames every 10 years
If Indexes are needed you Save/Create the CDX as MyDataTable.IF1

If I ever needed to access the history file and Index you
USE myDataTable.HF1
Set order to xxxxx of MyDataTable.IF1
... Code as usual


David W. Grewe Dave

 

[Kimed]

How about making a database of *archives* with insignificant 8.3 file names and detailed descriptions in respective fields.

 

[Steve Bowman]

using a small assumption that each record has at least 1 unique field for the record i usally include on out of habit ie tranID N(15) then every new record gets the next number. anyway I then can have an Archive.dbf with the same structure as the main table. As part of my delete code is scatter to ARRAY and then insert into archive from ARRAY followed with the delete command.

the unique number field is not required but comes in handy many times also including a date field would help in locating the archive data later.

useing this method also allowes for easy restoring of a record via the reverse process

Steve Bowman
Independent Technology, Inc.
CA, USA

 

[ezopaci]

I have writted some fox (dos) code to creating long file names, change/create/list directory in lfn, etc. (only in windows environment, but in dos window) without needs create some BATch file.

With it you can rename short file name to long and vice versa. And you can obtain SHORT file name from LONG, so you can do something like:
USE (Long2Short('My file name.dbf'))

If somebode will want it, I will send it to some web.


Tomas