Removing deleted records.

[bigdavidt]

In Visual FoxPro a record can be marked as deleted and yet still exist. It will be hidden if SET DELETED is ON. To remove a deleted record one uses the PACK command.

What is the equivalent command in Access?

 

[AlexMidd]

I don't think there is one.

Have fun! )

Alex Middleton

 

[rstitzel]

I think the equivalent would be COMPACT DATABASE. In Access 2003 it's Tools, Database Utilities and COMPACT & REPAIR DATABASE.

 

[JerryKlmns]

bigdavidt,

You are referring to logical / physical deletion. I would add a boolean field (bDeleted) in every Access table. For logical deletion of the record, change its value to true. For physical deletion, do delete it!

(Visual FoxPro is terra incognita for me)

 

[Golom]

Unlike FoxPro, Access does not allow you to undelete a record. Records are not physically removed until you compress the database but they can't be recovered once deleted although they continue to occupy space until a compress is performed. If you want the FoxPro functionality (inhereted from dBase) then you will need to create a "deleted" field and set it to true or false to logically (but not physically) delete the record. You will also need to write functions that do the equivalent of Pack (i.e. physically delete all logically deleted records and recover unused space.) There will be one slight difference and that is that "Pack" removes logically deleted records and reclaims space in a single operation. "Compress" in Access does the same thing but it won't touch your logically deleted records. It removes only those that have been physically deleted.

 

[GingerR]

If you want to get rid of the records to speed up searching on the table, but want to retain the records, think about making a HISTORY table and writing the 'deleted' records to it.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html