data corruption help.

[sss8885]

Good morning,

Recently we have experienced a bout of data corruption. In one of our databases, entries from one of our tables (it's a linked table; sql server) have been deleted. We are not sure why.

When we started digging through the code we noticed that a lot of it used recordsets. Unfortunately, the recordsets were not closed or set to nothing. I've been taught that is what you are supposed to do, but I was never really taught what would happen if they weren't. Could this be a cause of the data corruption? Has anyone experienced data corruption with recordsets? I'll give an example:



Private Sub Delete_Required_Items_Click()

Dim ctlList As Control
Dim varItem As Variant
Dim FieldToUpdate As Long
Dim dbs As Database
Dim rst As DAO.Recordset
Dim SelectedDevID As Long
Dim strSQL As String


Set ctlList = Me.lstRequiredItems
SelectedDevID = Me.DevID.Value
Set dbs = CurrentDb
strSQL = "SELECT * FROM dbo_DevStatus WHERE DevID = " & SelectedDevID


' Open record to update


Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)


With rst
' Find the record to edit
.FindFirst "[DevID] = " & SelectedDevID


If Not .NoMatch Then
' Loop through selected items and set the required fields in dbo_DevStatus
For Each varItem In ctlList.ItemsSelected
' Begin the edit
.Edit
Select Case varItem
Case 0 To 5
FieldToUpdate = (varItem * 2) + 12
Case 6 To 11
FieldToUpdate = (varItem * 2) + 27
End Select
rst.Fields(FieldToUpdate).Value = "N"
' Commit the Change
.Update
Next varItem
Else
MsgBox "Record not found."
' Add code to create a new DevStatus record
End If
End With

End Sub


As you can see, the database nor the recordset close or are set to nothing. The table the recordset connects to wasn't hit, but again, I am not sure what can happen when they don't close. Is it pervasive enough to affect other tables? Thanks in advance.

[June7]

It is my understanding that Access/VBA automatically releases variables and closes recordsets when a procedure completes, unless variables are globally declared. Although it is best practice to explicitly close recordsets, I find that where I have neglected to follow through on this I have not experienced any negative impacts.