is it ok to delete unwanted records and whats the Best way if needed?

[ShaunG]

if a record has accidentally been added, whats peoples opinion here on deleting it, from what I've read its a little frowned apon.

I have Add new Entry forms and Edit Forms.



is it better to delete straight from the table or is there a better way or should you just let that record go?

[ranman256]

if its a bad record , delete it.
In the form, select it, press delete key.

[ShaunG]

if its a bad record , delete it.
In the form, select it, press delete key.

ranman256 Thanks that's what I thought.

I have 5 subforms on a parent form which I have the Deletions set to No.

can code over come the deletions control?

[ShaunG]

I am using the below code in a module

Function DeleteARecord
If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
"This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If
End If
End Function

and then on click of the delete button

Private Sub cmdDelete_Click()
DeleteARecord
End Sub


I get a run time error 3200 as has related info in a tbl

[isladogs]

Sounds like your table is in a relationship with another table with referential integrity.
The record could be deleted by first breaking the relationship - almost certainly a bad idea.
Alternatively you need to delete that record in the master table - having confirmed its OK to delete

An alternative method is to have a boolean field 'Active' with default value = true
When you want to 'delete' a record you change Active to false
So the record still exists IF needed in future.

However, doing that will mean you need to alter existing queries to filter for Active=True

[ShaunG]

Sounds like your table is in a relationship with another table with referential integrity.
The record could be deleted by first breaking the relationship - almost certainly a bad idea.
Alternatively you need to delete that record in the master table - having confirmed its OK to delete

An alternative method is to have a boolean field 'Active' with default value = true
When you want to 'delete' a record you change Active to false
So the record still exists IF needed in future.

However, doing that will mean you need to alter existing queries to filter for Active=True

yeah ridders52 Generally all my tables are in a relationship.... I think I have found a way by deleting the record from an Edit form, I have turned the deletions on and it seems to work fine.

I do have accesslevel security in place on any forms that can change records of interest, so only the owner or myself will have access

[orange]

Shaun,

What kind of data are you dealing with? How important to the business is any data manipulation?
Whether it's OK to remove such a record or not really depends on the business and business rules.
If it was your bank account, and someone removed $XX, what would you want to know?

[ShaunG]

Shaun,

What kind of data are you dealing with? How important to the business is any data manipulation?
Whether it's OK to remove such a record or not really depends on the business and business rules.
If it was your bank account, and someone removed $XX, what would you want to know?

the data being deleted would not be important like bank accounts or to do with money...

for example a client might give us the go ahead to do a job so we create a job record of the clients details. But then the client may cancel the job due to what ever reason and may not proceed down the track

im only giving permissions to delete to owner and myself which will be taking over down the track

[Micron]

But then the client may cancel the job

For this, I would not delete. What I might I want to know later:

- how many times they did this. Could indicate they are too loose with their letting of orders/contracts or just don't take quoting seriously
- if they change their mind again, the info could be easily re-activated if kept
- I could know how many jobs/quotes or $$ potential earnings did not transpire into sales. Might be fodder for investigating sales staff, or whether or not it's limited to this customer
- etc, etc.

Knowledge is power - or so someone apparently once said.

[ShaunG]

For this, I would not delete. What I might I want to know later:

- how many times they did this. Could indicate they are too loose with their letting of orders/contracts or just don't take quoting seriously
- if they change their mind again, the info could be easily re-activated if kept
- I could know how many jobs/quotes or $$ potential earnings did not transpire into sales. Might be fodder for investigating sales staff, or whether or not it's limited to this customer
- etc, etc.

Knowledge is power - or so someone apparently once said.

yeah that's a good way to think Micron, thanks for pointing out. I think I was worried about waisting memory but in the scheme of things is nothing.

Knowledge sure is power

[June7]

Volatile memory is not wasted if you apply filter. Storage is cheap. Access does have 2GB file size limit which might be a concern.

[ShaunG]

Volatile memory is not wasted if you apply filter. Storage is cheap. Access does have 2GB file size limit which might be a concern.

yeah ok I haven't had much to do with applying filters...

I wasn't aware of the 2gb size limit - is that for the whole database in total with data?

I think Ive gone past that already and have had some out of memory errors happen

[June7]

The 2GB size limit encompasses everything in the db - data, forms, reports, code.

Run Compact & Repair then look at the file size stat in Windows Explorer.

[isladogs]

ShaunG
Are you sure you aren't confusing MB with GB

If the db really is over 2GB I doubt you will be able to open it at all let alone compact it.
However, you might be able to compact it successfully from another database

[ShaunG]

The 2GB size limit encompasses everything in the db - data, forms, reports, code.

Run Compact & Repair then look at the file size stat in Windows Explorer.

Sorry June7 I got my GB and MB mixed up...