Delete rows (record??) from Database table.

[gmaxey]

I'm trying to delete a row from a table in a database:

'This works because the table is deleted.
oConn.Execute "DROP TABLE [" & NumberingDlg.lbxSavedSchemes & "]"
'Now I want to delete a row in another table named "tblNumSchemes" in the same database.
On Error Resume Next
oConn.Execute "DROP INDEX [" & NumberingDlg.lbxSavedSchemes & "] ON tblNumSchemes"
If Err.Number <> 0 Then
Debug.Print Err.Number & " " & Err.Description
'Returns -2147217900 The database engine could not lock table 'tblNumSchemes' because it is already in use by another person or process.
End If
On Error GoTo 0
'This line runs without error, but the row is not deleted :-(
oConn.Execute "DELETE FROM tblNumSchemes WHERE fldNumSchemeName='[" & NumberingDlg.lbxSavedSchemes & "]'"

I'm a beginner here taking baby steps and I'm not even sure that I am using the rigth methods. I've looked on line and from what I found, it seems that either method I used should work.

Can anyone explain what I've done wrong and offer a suggestion to correct it. Thanks.

[JKwan]

Just a stab in the dark..... Is fldNumSchemeName a NUMBER? If it is, then you don't enclose it with single quotes (').


update:
After reading it closely, I think you need to remove the single quotes

[gmaxey]

JKwan,

I neglected to come back here and post the solution I found abut an hour ago. It wasn't the single quotes in this case, it was the brackets:

This works:

oConn.Execute "DELETE FROM [tblNumSchemes] WHERE fldNumSchemeName='" & NumberingDlg.lbxSavedSchemes & "';"

Do you know a good source to learn when and where single quotes and brackets are supposed to be used? What is the purpose of the trailing semicolon?

Thanks.

[JKwan]

I don't have good source material.... However, if you use [], this signifies it is a database field. If you use a single quote, this usually is to equate to something, like what you did above [something] = 'abc'. Now, if it is numeral, then you don't need/want to put single quote around it.