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 "application.run 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
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.