dlelete records and maintain the databse

[hmurti17]

I want the record in the database to be deleted but while deleting the record should not be actually deleted but it should be marked with "*"
and while maintaining the database the records marked for deletion should be deleted permanently. Also the user should have the option of undeleting the record before the record is permanently deleted.

I would also like to know the codes for compacting and maintaining the database.

Thanks,

Hansa.

[John G Duffy]

Without knowing what type of dataBase you have, it is hard to answer your questions but here are a few suggstions
Add a 1 character field to the record to denote whether this record is scheduled for deletion or not. In your search algorithms, specify to exclude any records with this new field set to "delete".
As for actual maintenance, write a small program that goes through the database, copying non-deleted records to a new Database. Once copy is completed, discard the old dataBAse and rename the new one to the name of the original.

John G

[R. Joseph Newton]

Try this:

Add a Boolean [Yes/No in MS Access] field "ToDelete" to your table.

To soft-delete the record:

Using SQL:


private Sub SoftDeleteItem(lngTargetID as Long)
Dim strConnectLine as string, strDeleteItem as string
Dim ConnectItem as ADODB.Connection
Dim cmdExecute as ADODB.Command

strDeleteItem = "update Item set ToDelete = true where ItemID = " & lngTargetID
strConnectLine = "DSN=ItemDataBase"
set ConnectItem = new ADODB.Connection
ConnectItem.Open strConnectLine
set cmdExecute = new ADODB.Command
set cmdExecute.ActiveConnection = ConnectItem
cmdExecute.CommandText = strDeleteItem
cmdExecute.Execute
End Sub




Using recordset navigation:


private Sub SoftDeleteItem(lngTargetID as Long)
Adodc1.RecordSource = "Item"
Adodc1.Refresh
With Adodc1.Recordset
if .EOF then Exit Sub
.MoveFirst
.Find "ItemID = " & lngTargetID
if .EOF then Exit Sub
!ToDelete = true
End With
End Sub




Undeleting is the same operation, except with ToDelete set to False.

To "empty the Recycle Bin" you can use SQL again, or recordset navigation. SQL is much faster, though.

Using SQL:


private Sub HardDeleteAll()
Dim strConnectLine as string, strDeleteAll as string
Dim ConnectItem as ADODB.Connection
Dim cmdExecute as ADODB.Command

strDeleteAll = "update Item delete where ToDelete = true"
strConnectLine = "DSN=ItemDataBase"
set ConnectItem = new ADODB.Connection
ConnectItem.Open strConnectLine
set cmdExecute = new ADODB.Command
set cmdExecute.ActiveConnection = ConnectItem
cmdExecute.CommandText = strDeleteAll
cmdExecute.Execute
End Sub




Using recordset navigation:


private Sub HardDeleteAll(lngTargetID as Long)
Adodc1.RecordSource = "Item"
Adodc1.Refresh
With Adodc1.Recordset
If Not .EOF then .MoveFirst
Do While Not .EOF
.Find "ToDelete = true"
.Delete
.Update
Loop
End With
End Sub





Joseph