[jpamps]

hi, i've been having trouble with deleting a record in a database. i'm very new to adodb, so i dont know much. welps.. this is what i have so far:

Private Sub cmdDelete_Click()

Dim MyConn As adodb.Connection
Dim rs As adodb.Recordset

Set MyConn = New adodb.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\List1.mdb;"
MyConn.Open

If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Delete?") = vbNo Then 'check if you really want to delete this record
Exit Sub
Else

With MyConn
.BeginTrans
.Execute "DELETE FROM List1 WHERE OrigTitle = '" & ListView1.ListItems.Item(ListView1.SelectedItem.Index)
.CommitTrans
End With

End If

rs.Close
Set rs = Nothing


how come this doesnt work? i get an error saying Syntax error in string query expression 'OrigTitle = 'whatever'. where 'whatever' is what the user selects in the first column of the ListView.

it then points to:
.Execute "DELETE FROM List1 WHERE OrigTitle = '" & ListView1.ListItems.Item(ListView1.SelectedItem.Index)

so can anybody help me out with this? or give any other advise on how to delete a record? many thanks in advance for your help!

[2much2young]

Think you might need to add a bit at the end

.Execute "DELETE FROM List1 WHERE OrigTitle = '" & ListView1.ListItems.Item(ListView1.SelectedItem.Index) & "'"

[jpamps]

2much2young, on 7 Dec, 2006 - 01:50 AM, said:

Think you might need to add a bit at the end

.Execute "DELETE FROM List1 WHERE OrigTitle = '" & ListView1.ListItems.Item(ListView1.SelectedItem.Index) & "'"

YAY!!!! it works now!!! thanks a lot!!

This post has been edited by jpamps: 08 December 2006 - 12:20 AM

[arun1516]

jpamps, on 7 Dec, 2006 - 12:53 AM, said:

hi, i've been having trouble with deleting a record in a database. i'm very new to adodb, so i dont know much. welps.. this is what i have so far:

Private Sub cmdDelete_Click()

Dim MyConn As adodb.Connection
Dim rs As adodb.Recordset

Set MyConn = New adodb.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\List1.mdb;"
MyConn.Open

If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Delete?") = vbNo Then 'check if you really want to delete this record
Exit Sub
Else





With MyConn
.BeginTrans
.Execute "DELETE FROM List1 WHERE OrigTitle = '" & ListView1.ListItems.Item(ListView1.SelectedItem.Index)
.CommitTrans
End With

End If

rs.Close
Set rs = Nothing


how come this doesnt work? i get an error saying Syntax error in string query expression 'OrigTitle = 'whatever'. where 'whatever' is what the user selects in the first column of the ListView.

it then points to:
.Execute "DELETE FROM List1 WHERE OrigTitle = '" & ListView1.ListItems.Item(ListView1.SelectedItem.Index)

so can anybody help me out with this? or give any other advise on how to delete a record? many thanks in advance for your help!

if you want to delete all the records from the database use this code to delete all the records from the database

conn.execute "delete from table name "
thats it ....
you can place a message box or anything you want to alert the user with




now if you want delete specific record from the database
here is the code

sql = " delete from table name where fields = '" & fieldname & "'"
( fields here is the database feildname and fieldname is the field on your form)
rs.open , sql , conn , openstatic , adlockpessimistic
with cmd
cmd is basically the command string we have defined in the module section
.activeconnection = conn
.commandtext = sql
.exexute
thats it .........