Delete data from form or via table.

[calvinle]

Hi,

I just a simple question regarding deleting a data.

I have a listed of record on my continuous form, so I am wondering which will be the safe/better way/quicker to delete record?

1/

CurrentDb.Execute "DELETE FROM tblname" & "WHERE id = " & Me.txtId

2/

     DoCmd.SetWarnings False
    sSql = "DELETE FROM tblName WHERE id = " & Me.txtId      DoCmd.RunSQL sSql
    DoCmd.SetWarnings True

3/

     DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.SetWarnings True

Is there any difference? My database is store backend.

The reason I am asking if I run into so many issue using the method 3 on the form itself, so since each record has its own primary key, I think it's safer to delete via sql and just requery as 1 or 2.

Any thought?

 

[arnelgp]

they are all the same.
#3 is better, you delete it from the form and it will delete on the back table.

you will notice when you use method #1 and #2, the form does not automatically refresh (you see #deleted#), while on #3 you do not.

imagine a scenario where you are at 3,250th record on datasheet or continous form. and you delete it using #1 & #2, then you requery, you will be tossed back again at the top of datasheet that is record 1. when actually you want to continue whatever you are doing with the next records.

 

[MarkK]

I would always go with #1. I would run it with the dbFailOnError option, as follows . . .

CurrentDb.Execute "DELETE FROM tblname WHERE id = " & Me.txtId, dbFailOnError

I like it because it's the simplest, and it shows right in the code what will be deleted from where. Zero ambiguity, maximum simplicity.

 

[calvinle]

they are all the same.
#3 is better, you delete it from the form and it will delete on the back table.

you will notice when you use method #1 and #2, the form does not automatically refresh (you see #deleted#), while on #3 you do not.

imagine a scenario where you are at 3,250th record on datasheet or continous form. and you delete it using #1 & #2, then you requery, you will be tossed back again at the top of datasheet that is record 1. when actually you want to continue whatever you are doing with the next records.

Yes, the option 3 looks nicer as you don't see the #Delete flashing on the form. However, on my continuous form, I use filter directly on it, and many other code, so when using this, I encount that kind of error such as: Record already being deleted, etc.

 

[calvinle]

I would always go with #1. I would run it with the dbFailOnError option, as follows . . .

CurrentDb.Execute "DELETE FROM tblname WHERE id = " & Me.txtId, dbFailOnError

I like it because it's the simplest, and it shows right in the code what will be deleted from where. Zero ambiguity, maximum simplicity.

Can you explain the dbFailOnError does to the code? I read on the web but still not catch what that does to the code.

Thanks

 

[MarkK]

It raises a trappable error in VBA if the operation fails.

 

[Galaxiom]

Can you explain the dbFailOnError does to the code? I read on the web but still not catch what that does to the code.

It tells VBA to raise an error if there are any problems with the query.

For example, if the delete was blocked by Referential Integrity, the query would quietly fail without dbFailOnError.

 

[arnelgp]

sorry, i dont really use filter the form through code, its annoying to see on record navigation you have Filtered there.

me.filter="blah, blah"
me.filterOn=True

what i use is change recordsource on the fly,

me.recordsource="select * from table where (filter)...."

 

[Galaxiom]

its annoying to see on record navigation you have Filtered there.

I more than agree. It is annoying to see the record navigation at all.

It just encourages users to try and work with that horrid little search box.

 

[calvinle]

I more than agree. It is annoying to see the record navigation at all.

It just encourages users to try and work with that horrid little search box.

Right but I disabled the navigation, so they dont see its filtered. As to set it to recordset, I have experienced in the past a big ussue where all my data were changed to #Error and I cudnt find a fix for it and I had no backup or new data. Not sure if the recordset was the problem but I dont persee that issue with filtering data.