Record is deleted corruption problem (1 Viewer)

[Roni Sutton]

I am hitting an Access 97 db with a VB Executable that basically archives old data from a couple of tables which are linked. It pulls out records older than 10 days, copies them to another database, and then deletes them. It's fairly straight forward. This executable is started via a batch file at night after everyone has left for the day. Recently, when we come in the next morning and start to work, we receive "record is deleted" errors during every query attempt. When we look at the table, there are multiple records that say #DELETED in every column. Repairing the database takes care of this problem, but we've now had to repair it every morning for a week. I could really use some ideas on what could be causing this problem.

Thanks,
Roni

 

[Travis]

Is the data still being used at the time of the Deletion? (Did someone leave the application running?)

It might be good practice to code your VB app to run the Compact/Repair at night after it archives/deletes the records.

See Microsoft knowledge base for indepth info:


'Compact:

Sub CompactDB()
On Error GoTo CompactDB_Err
Const conFilePath = "C:\Program Files\Microsoft Office\Office\Samples\"

' Compact the database to a temp file.
DBEngine.CompactDatabase conFilePath & "Northwind.mdb", _ conFilePath & "NorthTemp.mdb"

' Delete the previous backup file if it exists.
If Dir (conFilePath & "Northwind.bak") <> "" Then
Kill conFilePath & "Northwind.bak"
End If

' Rename the current database as backup and rename the temp file to ' the original file name.
Name conFilePath & "Northwind.mdb" As conFilePath & "Northwind.bak"
Name conFilePath & "NorthTemp.mdb" As conFilePath & "Northwind.mdb"
MsgBox "Compacting is complete"

Exit_CompactDB:
Exit Sub

CompactDB_Err:
MsgBox Err.Description
Resume Exit_CompactDB
End Sub


'Repair:

DBEngine.RepairDatabase "C:\Program Files\Microsoft Office\Office" _
& "\Samples\Northwind.mdb"

 

[Roni Sutton]

Thanks, Travis! I think that worked!

Thanks, Travis! I think that worked!