Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Delete query takes FOREVER to run 1

Status
Not open for further replies.
TheresAlwaysAWay

TheresAlwaysAWay

Programmer
Mar 15, 2016
135
0
0
US
I have a table that is used to calculate monthly commissions. Once the month is over and data has been saved there's no need for any of the info in the table, so it is deleted entirely awaiting the start of the following month. It couldn't be simpler. All data is removed and it's usually not more than about 10k records or so, yet it takes several minutes to complete.

This is the statement. DELETE RoadRepCommissions.* FROM RoadRepCommissions;

Can someone please assist on this? What's even odder is that if I open the table itself and highlight all the records in it, it still takes just as long to delete. Something's weird and I haven't a clue what.

As always, thanks in advance for any assistance.
 
Sort by date Sort by votes
Looks like:
[tt]
Currentdb().Execute "DELETE FROM RoadRepCommissions;", dbFailOnError[/tt]

Should work pretty fast, according to this place

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Upvote 0 Downvote
Thanks for this. Unfortunately, the accountant started the commissions for this month today and once she's working on it I can't start deleting data for test. Once she finishes for the month then I'm free to do whatever I want until the next month. She usually takes from somewhere around the 10th of the month to somewhere around the 20th. It's aggravating but there's nothing I can do to test this until she finishes.

If you can, I'd appreciate an explanation of what this code does vs. the original.

Thanks.
 
Upvote 0 Downvote
One other question. If I were to use this in another circumstance where there's a WHERE clause, do you know how to include that in this expression format?
 
Upvote 0 Downvote
The two Delete statements are pretty much the same, both should work fast. Unless there is something wrong with your data base. Maybe it needs repairs?

But, having the table for one month, delete all records and start over - is not the best design.
"Once the month is over and data has been saved..." - why not saving the data wherever it needs to be without the RoadRepCommissions table... [ponder]

As for WHERE clause:

Code: 
Dim strSQL As String
Dim strLastName As String

strLastName = "Smith"[green]
'Delete all records for Smith[/green]
strSQL = "DELETE FROM RoadRepCommissions WHERE LAST_NAME = '" & strLastName & "'"
Currentdb().Execute strSQL, dbFailOnError



---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Upvote 0 Downvote
Thanks for your help again.

The answer to your question about why the records are deleted from that table is because the corrected, updated info after the accountant is finished is saved in other tables and spreadsheets. It is her working table to edit the records and manipulate them as she needs to. Once that process is complete and the finalized info is stored elsewhere the table is deleted.

You could think of it as a temporary holding table that is used just long enough for her to finalize all commissions.

After hearing my explanation does it still seem weird to you?
 
Upvote 0 Downvote
Yes, kind of...
I would (probably) keep the time stamp on the records and display just the records for the current month, and keep all of it in the table.
But - if that is what the user wants, that's what she gets. :)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Upvote 0 Downvote
Like I said, the information holds no value after the end of the month, and that's why it's deleted.

It's not gonna be until after the 20th before I can test this. I'll let you know then.
 
Upvote 0 Downvote
Have you considered?
- a copy of the database for your testing totally separate from the copy being used by the accountant
- a database /table designed specifically for you to "tune your skills" before working with the "official" database

Either option would allow you to check/revise the performance as necessary.

Perhaps there are other features of your database and/or concurrent users/processes that are making your query painfully slow.
 
Upvote 0 Downvote
I've finally had the chance to test this and I run into this error message.

You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.

Can you provide further guidance?

Thanks.
 
Upvote 0 Downvote
I was able to work out the issue with the dbSeeChanges option and test this. It worked well. Thank you for your help.
 
Upvote 0 Downvote
Glad you have it resolved. Didn't realize you have SQL server BE.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TheresAlwaysAWay
  • Locked
  • Question
Can Access be used to generate and respond to text messages? 1
Replies
6
Views
372
TheresAlwaysAWay
TheresAlwaysAWay
lmcate
  • Locked
  • Question
How to Open ACCESS (MDB) from an HTML Page 1
Replies
2
Views
345
lmcate
lmcate
atadbitslow
  • Locked
  • Question
how to search data and display matches that include the user's input, but not necessarily match
Replies
5
Views
184
atadbitslow
atadbitslow
TheresAlwaysAWay
  • Locked
  • Question
Convert PDF to Excel spreadsheet within Access 2
Replies
10
Views
305
TheresAlwaysAWay
TheresAlwaysAWay
Jaime_78
  • Locked
  • Question
DoCmd.OpenReport Hangs when user wants to select location of report to save to
Replies
2
Views
111
Jaime_78
Jaime_78

Part and Inventory Search

Sponsor

Back
Top