Performance or timeout issue when deleting using RunCommand 1

[bhoran]

I am using a databse over a network, when I use a Macro using RunCommand SelectAllRecords and RunCommand Delete it has a record Locking error.

I can only assume this is due to inefficiencies in these commands causing time out performance issues.

I have tested the same process using a delete query and that works fine, however, I am worried that as the data grows (currently only testing with 2 brands) that may too have a performance issue.

Is there a better/more efficinet way to delete all records in a table so it can be refreshed with an import?

 

[psemianonymous]

A SQL Delete query will be faster than the clipboard select/form record delete method. So make a delete query, save it to a stored query, and either use DoCmd.OpenQuery "deletequeryname" or CurrentDB.Execute "DELETE * FROM tablename", dbFailOnError

Either will work.

If you have a form with stuff being displayed, and you're going to delete THAT, then do:

Me.Undo
'run the delete query here, as shown above
Me.Requery

That should do what you need.

 

[bhoran]

Cool thanks Foolio I have created the delete query and it works fine, I am not having a problem with the part which is an update query to update nulls to zero. I have lots of queries off this so am reluctant to use the Val(nz()) function for each of them.

I have tried using a maketable query instead but that has storage space implications due to the table having 300k records.

Anyway I have posted this question in a new thread, please feel free to look it over.

 

[psemianonymous]

Yeah, sorry, I don't have any good advice. I read the thread, but will answer here, because threads with "0 replies" generally get more looks than those with exactly 1 reply. Anyway.

My only possible advice is to (maybe) leverage the benefits of indexing. Indexing a field will make it faster for SELECT ... WHERE statements, but will actually make the process slower for INSERT/DELETE/UPDATE statements. So if you're UPDATE'ing a ton of records, it may just be faster to -un-index the field, -run the update, -re-index the field.

But I may be way off. The benefit of indexing/unindexing may or may not be significant, and there may actually be a penalty for using this process over a standard update. What I'm basically saying is that I don't know. So that's why you're getting the answer here, instead of there.


Good luck.


Pete

 

[bhoran]

Thanks Pete, i have no indexes on that field, i am going to bite the bullet and use the val(nz()) function and ingore the update process al together, it is probably what I should have done in the 1st place.

Thanks for your help.