Table lock problem while trying to delete

[ray2heavy]

Hey guys, I am getting a "database cant lock table "blah" because it is already in use by another person or process" while trying to delete, then re-populate it. This table is viewed in a subform (CSD appender embedded) in datasheet view, and I have been changing the recordsource of the form to another table, making any changes that are required, then setting it back. This is a technique which I have been using successfully throughout the code within this form but has started causing a problem as I have added a new peice of code.

I have checked the recordset properties in both the parent and child form objects, and both of them are set to different tables. I have also checked the recordsets.count property is 0 to ensure that I have closed any recordsets that I may have open on the table.

It seems like a system setting which I cannot access is still set somewhere and I am unsure how to change this.

Code:
Private Sub pull_matches()
Dim display As String
DoCmd.SetWarnings False
frm_view = "matches"

For Each varItm In Show.ItemsSelected
display = display & "[zzappend].[" & Show.ItemData(varItm) & "], "
Next varItm
display = left(display, Len(display) - 2)
Forms![csd appender].Form![csd appender embedded].Form.RecordSource = "zzappend"

If Table_exists("zzappend_temp") Then
DoCmd.DeleteObject acTable, "zzappend_temp" 'This is where the error occurs
End If

sql = "SELECT " & display & ", [zzappend].ID, zzappend.[change to] INTO zzappend_temp FROM zzappend WHERE (((zzappend.shv1) IN (SELECT [shv1] FROM [zzappend] as tmp GROUP BY [shv1], [" & Cmbpostcode.Value & "] HAVING count(*)>1 AND " & Cmbpostcode.Value & " = [zzappend]." & Cmbpostcode.Value & "))) ORDER BY zzappend.shv1, zzappend.[" & Cmbpostcode.Value & "];"
DoCmd.RunSQL (sql)

sql = "ALTER TABLE zzappend_temp ADD changed bit"
DoCmd.RunSQL (sql)

Forms![csd appender].Form![csd appender embedded].Form.RecordSource = "zzappend_temp"
DoCmd.SetWarnings True

End Sub

[PianoMan64]

The reason that you're getting that error, is because the table is never closed, especially if it is in the same current Database. That's the reason that you're getting that error.

If it was something that when you openned another instance of that up, and made sure that the table is not currently being view by anyone including yourself, then you would be able to delete the record, but since you have it open in the current instance of the database, you can't do that, you simply can change the data within it, and even the structure, but not delete the table all together.

Hope that helps,

Joe P.