Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 11th, 2005, 10:05 AM
Authorized User
Join Date: Jun 2004
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default Delete slows down

I am using a combination of tabledefs.delete to remove tables followed by SQL: UPDATE, and DELETE to remove records of that table in 4-5 other related master-tables.

This works ok in principle. But in some cases when there is a need to remove large numbers of tables (and associated records), it is somewhat slow to start, but it even slows down a great deal as more deletions complete.

My only guess is that the database, being large enough(>1.5Gb), becomes more and more fragmented with each delete, and that causes slower operation in general.

Any ideas on how to improve this will be appreciated.
Old April 11th, 2005, 02:09 PM
Friend of Wrox
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts

If you can adjust your deletion SQL so that it defines more of the records to be deleted, that will run faster than a loop that deletes them 1 at a time.

Where is it that you are using .UPDATE?

Could you illustrate your arrangement with some of the VB that accomplishes this task?
Old April 11th, 2005, 02:19 PM
Authorized User
Join Date: Jun 2004
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts

Here is an example, if a table (called symName) is deleted all master tables should be UPDATEd:
    SQL = "UPDATE DataSets INNER JOIN DataSetSymbols AS S ON DataSets.DataID = S.DataID "
    SQL = SQL & " SET DataSets.lastEdit = Now() WHERE (((S.Symbol)='" & symName & "'))"

You are correct in your inference that the deletion is done one a time.
The "mass" delete was added as a giveaway, and it simple loops through individual deletes.
Somehow it has become a critical tool in data migration, so its crude nature is showing.
So it is not optimized at all. For one, it connects and disconnects to the same DB for each delete.
But still that does not explain the incremental slow-down with the number of symbols.

Any hints on this will be appreciated.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable to delete file System.IO.Delete error maricar C# 13 March 14th, 2014 06:50 AM
php/mysql delete button and delete query dungey PHP Databases 17 April 11th, 2009 12:24 PM
Visual Studio 2005 slows on loading WebProjects. amitjoshipune ASP.NET 2.0 Basics 1 April 17th, 2008 09:35 PM
delete aytacakin J2EE 0 November 22nd, 2005 09:27 AM
how to delete a row when click delete hyperlink naveenkumarg1 Pro JSP 1 August 16th, 2004 01:29 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.