Connection.Execute command to Delete Records from MS Access Database Table

[SPereira]

Hi Guys,

I have written a program in VB 6 wherein I want to delete a particular record from MS Access Database Table. I noticed that the record does not get deleted immediately. Only after I close the recordset it disappears. I have a Delete Command Button in a Form that deletes a particular record. After deletion, when I browse the records in the table I noticed that the record has not been deleted. It is only when I close the Form which closes the recordset to that table and when I reopen the recordset I notice that the record gets deleted then. My code is as follows.

Conn.Execute "DELETE FROM <tablename> WHERE FIELD1 = '" & cmdField1.Text & "'"

where Conn is set to a New Connection. So what I did was close the recordset and then reopen it again soon after the above command is executed as follows.

Rs.Close
Rs.Open "SELECT * FROM <tablename> ORDER BY FIELD1", Conn, , , cmdTable

Please let me know if I am doing this the right way or is there any other method of solving this issue? But I want to use the Connection.Execute command only.

Please help. Thanks.
SPereira

[manoshailu]

HI,

To Refresh the Recordset you can use the ReQuery which the query executes again to get the proper results.

Shailaja:)

Hi Guys,

I have written a program in VB 6 wherein I want to delete a particular record from MS Access Database Table. I noticed that the record does not get deleted immediately. Only after I close the recordset it disappears. I have a Delete Command Button in a Form that deletes a particular record. After deletion, when I browse the records in the table I noticed that the record has not been deleted. It is only when I close the Form which closes the recordset to that table and when I reopen the recordset I notice that the record gets deleted then. My code is as follows.

Conn.Execute "DELETE FROM <tablename> WHERE FIELD1 = '" & cmdField1.Text & "'"

where Conn is set to a New Connection. So what I did was close the recordset and then reopen it again soon after the above command is executed as follows.

Rs.Close
Rs.Open "SELECT * FROM <tablename> ORDER BY FIELD1", Conn, , , cmdTable

Please let me know if I am doing this the right way or is there any other method of solving this issue? But I want to use the Connection.Execute command only.

Please help. Thanks.
SPereira

[debasisdas]

try to use this

conn.begintrans
Conn.Execute "DELETE FROM <tablename> WHERE FIELD1 = '" & cmdField1.Text & "'"
conn.committrans

[SPereira]

Hi Shailaja,

Thanks alot. The ReQuery option did the trick. Now I can move on with ease.

C Ya.

HI,

To Refresh the Recordset you can use the ReQuery which the query executes again to get the proper results.

Shailaja:)

[vishvaabala]

HI

Give the Follwing Cmds

Con.Begintrans
Con.execute "Delete From Table Where Cond..."
Con.Commitrans

[SPereira]

Hi

I did try the begintrans / committrans but it somehow didn't work out. But the Requery option did work.

Thanks anyway.

[Ariah]

hi!:(
I'm Ariah,
I got problem with vb 6.0 codes to delete records from tables, w/c I used MS Access 2003 as my database. Can u please help me in coding? just give me a very simple code for this, without using any modules. . . ok? thanks a lot! hope u can help me too. . .
good day!

[SPereira]

hi!:(
I'm Ariah,
I got problem with vb 6.0 codes to delete records from tables, w/c I used MS Access 2003 as my database. Can u please help me in coding? just give me a very simple code for this, without using any modules. . . ok? thanks a lot! hope u can help me too. . .
good day!

Refer to the previous posts. After using the Connection.Execute command refresh the Recordset using the ReQuery option.