access subform deleting record....

[bobetko]

1) I have main Form and Subform.

The Subform is displaying some data depending on the accountID value on the main form.

The problem is when I delete record in the subform. It takes several seconds for subform to show new state.

Just for the test purpose I created "Refresh" button which does requery on the subform. I usually have to press it 3-4 times untill I see updated information (record deleted)

This is how I delete:

   Dim strSQL
   Dim cnn As New ADODB.Connection
   cnn.Open (CurrentProject.Connection)
   strSQL = "DELETE FROM tblContacts WHERE contactID = " & Me.tblContactEdit.Form.[contactID]
   cnn.Execute strSQL, adExecuteNoRecords
   cnn.Close

What I am doing wrong?

2) Second problem is, I am very often getting a message like "The database has been placed in state by user 'Admin' on machine XXXX (which is my machine???) that prevents it from being opened or locked."????????????
What is this....
My boss dropped this project in my hand. I feel so unfortunate that I have to deal with this Access ... thing.

Thanks.

 

[Remou]

You will get the second problem if you have not saved all changes.
Have you a reason for not using DAO?
Have you tried using Refresh?
It's not all bad.

 

[bobetko]

VBA is not my strongest skill ...
I usually do ASP (vbs). I work with tables using ADO so In Access I attempted to doe the same. I guess, Access doesn't like that. As soon as I switch to DAO (as soon I figured how), everything started working fine.... for now.

Talking about second problem.... it's driving me crazy.... for example, I would open table in design mode and I wouldn't be able to save it becouse table would be read-only. Access would force me to save-as with different name. Then I would exit access, open it again and this time table would let me to make changes. If I try to open table exclusively, I would get error message "File is already "; open" (notice "; in the middle).... very strange..... to me....
Thanks a lot for your response

 

[Remou]

The second problem is an ADO thing, I think; it, too, should go away with DAO. It is by no means impossible, or even that bad, using ADO with Access. You will find quite a bit on the subject in these fora, including debates. Two examples:

ADO vs. DAO
thread701-1171537
DAO to ADO Conversion Guidelines and Tips
thread705-1147156

 

[bobetko]

thanks