Help with DELETE Statement

[jfarrell]

I need help with an SQL DELETE statement using Access97, ADO
and VB. I'm getting the error message
'No Value given for one or more required parameters'
I have tried every which way to make it work.
My code is as follows.
Private Sub cmdDelete_Click()
Dim strSQL As String
Dim cnAP As Connection
Dim cmdSchool As Command
Set cnAP = New Connection
Set cmdSchool = New Command
cnAP.CursorLocation = adUseClient
cnAP.Open "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\project\Student.mdb"
cmdSchool.ActiveConnection = cnAP
cmdSchool.CommandType = adCmdText
strSQL = "DELETE * FROM School WHERE School = 'txtName'"
cmdSchool.CommandText = strSQL
cmdSchool.Execute
adoSchool.Refresh
end sub
Can anyone tell me the correct statement. I want to be able to delete out the chosen record.
Many thanks in advance.
John

 

[Alira]

Here is how I did this. Try it out...

Dim cnn As ADODB.Connection
Dim ConStr As String
Dim SQL As String
Dim ERRLOOP As ADODB.Error

ConStr = "PROVIDER=MSDataShape;Data PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=mydb.mdb;"

Set cnn = New ADODB.Connection
cnn.Open ConStr
cnn.BeginTrans

If cnn.Errors.Count > 0 Then
MsgBox "EN ERROR HAS OCCURED"
For Each ERRLOOP In cnn.Errors
MsgBox "ERROR # : " & ERRLOOP.Number & vbCr & ERRLOOP.Description
Next
cnn.RollbackTrans
Exit Sub
End If

SQL = "your SQL statement"

cnn.Execute SQL
cnn.CommitTrans

 

[balves]

I don't think Access likes "DELETE *" like in SQL Server. Try "DELETE Table.Field FROM Table..."

Also, you must have a primary key on the table or Access won't delete records.

To be sure of the exact syntax, try it out in the Query Designer.