Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2015

    Unanswered: Speed Up Data Deletion On Backend

    Greetings everyone! I need some pointers and additional feedback to my dilemma. It revolves around the speed of deleting data.

    Background: I'm using Access 2007. An initial project required data calculations from a single office. Since then, more branches have been added. This results in the database bloating up in size and at times reaching the 2GB maximum.

    Attempted Solutions: The database was split and a front/back end was created. This resolved the bloating issue but significantly added to the processing time. I've been able to trace where the lag time is occurring. During the calculation process, records have to be deleted. This deletion part adds time to the total processing time. The full report takes up to 11 minutes in the original database while it can take up to 40+ minutes in the front/back end model. Keep in mind that the database runs off the local PC and not a network.

    Auto compact and repair is not a full solution because during that process the backup file is not deleted; thus, leaving multiple copies. This is an IT parameter that cannot be changed.

    I've used VBA to open a direct connection to the backend and delete the data but the lag time is not improved.

    I'm currently trying to export the "deletion" module to the temporary backend where I can then run it via the " method". However, the code is not working. I've been able to run a macro on an external database before, but somehow it will not run. Even the example below from MS will not work until I open the database, make it visible and accept to run macros. I know I can create an autoexec macro, but would prefer to not use it as VBA should work as intended.

    I've considered using the original database and making a clone once it loads; however, it may cause issues if the end user wishes to revisit the data after closing the database. Reopening it would create a new instance and be more trouble than may be needed.

    Private Sub RunAccessSub() 
     Dim appAccess As Access.Application 
     ' Create instance of Access Application object. 
     Set appAccess = CreateObject("Access.Application") 
     ' Open WizCode database in Microsoft Access window. 
     appAccess.OpenCurrentDatabase "C:\My Documents\WizCode.mdb", False 
     ' Run Sub procedure. 
     appAccess.Run "Greeting", "Joe" <---- Errors out here. If I enable macros on external database and continue it finishes the procedure as expected but not before macros are enabled.
     Set appAccess = Nothing 
    End Sub

    Again, ideally I'm really looking for a way to delete data from the backend in a faster way; however, I will accept other workarounds.

  2. #2
    Join Date
    Apr 2014
    Provided Answers: 32
    Deleting data is only the 1st part....are you also COMPACTING? Not compacting causes slowdown.

  3. #3
    Join Date
    Apr 2015
    Quote Originally Posted by ranman256 View Post
    Deleting data is only the 1st part....are you also COMPACTING? Not compacting causes slowdown.
    From my time trials the lag starts in the deletion cycle even before it bloats. Compacting it is not the concern at the moment and I already have a solution for it.

    Any thoughts on why I cannot run the procedure on the external database from the front end?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts