Problem trying to remove BAS module after running a procedure within it

[Nates]

I have a .bas file saved locally that I load into my Acces project to run a particular sub. I use the following code to load the module (which works fine):

Application.VBE.ActiveVBProject.VBComponents.Impor t FileName:=FilePath & CODE_FOLDER & RatesYear & "\" & RatesMonth & "\" & ProductName & ".bas"

I use the following loop to remove the module:

For Each Comp In Application.VBE.ActiveVBProject.VBComponents
If Comp.Name = ProductName Then
Application.VBE.ActiveVBProject.VBComponents.Remov e Comp
Exit For
End If
Next

The above works fine (both adding the module and subsequantly removing it) provided I do not run any code within the module once it is loaded. The second I use code within the module the deletion loop does not seem to remove the module immediately (as it would do prior to running any code in the module).

The procedure in it's entirity is as follows:

Public Property Let Product(ProductName As String)

Dim Comp As Object

'Close rates connection if it is already open
If RatesConn.State <> 0 Then RatesConn.Close

'Remove old code modules
For Each Comp In Application.VBE.ActiveVBProject.VBComponents
If Comp.Name = ProductName Then
Application.VBE.ActiveVBProject.VBComponents.Remov e Comp
Exit For
End If
Next

'Retrieve most recent code module
Application.VBE.ActiveVBProject.VBComponents.Impor t FileName:=FilePath & CODE_FOLDER & RatesYear & "\" & RatesMonth & "\" & ProductName & ".bas"

'Establish rates database connection and set product var
RatesConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & RATES_FOLDER & RatesYear & "\" & RatesMonth & "\" & ProductName & ".mdb;"
RatesConn.Open

prvProduct = ProductName

End Property

Really baffling, if no code is run within the loaded BAS the deletion loop removes the module immediately and a fresh version of the ub can be loaded. If code is run the deletion loop does not work immediately and when the procedure comes to add a fresh version of the BAS it creates a duplicate with "1" on the end.

I have tried substituting the deletion loop with a simple docmd.deletobject acmodule, ProductName without much luck.

Are you only able to add/remove modules whilst no code within that module has been run?

Any help greatly appreciated!!

Thanks

EDIT: Just to point out the obvious, I have made sure that all code within the loaded bas has finished executing prior to attempting to remove it.

[Nates]

Further to the above, if I try to remove the module (once its been added and the sub within it has been run), using docmd.deleteobject from the immediate window, an error box appears suggesting the project is unable to find the module (where as can be seen from the attached screenshot, it is clearly present!).

Should I file this as another odd MS bug and try to find an alternative solution (any suggestions on that front greatly appreciated)?

Cheers

[ADezii]

A long shot is that the Module has actually been Deleted, but the Environment is not fully aware of it. Immediately after the Module has been Deleted, insert the following line of code and see what happens:

Application.RefreshDatabaseWindow

[Nates]

Thanks for the suggestion, unfortunately it did not work. I suspect you are on the right lines with the VBA IDEA no refreshing when the module has been deleted. It only seems to refresh once the current sub has finished IF a process within the imported bas has been run.

The other theory I have is with the saving of the imported module. If I use one of the exit buttons on the form (a simple docmd.quit) Access prompts me to save the imported module. I suspect I need to save the module once it has been imported so that it removes instantly? Long shot, but in my head it half makes sense!!! Anyone know the function for saving an imported module?

[NeoPa]

My understanding is that any module that is used (invoked) is, at that point only, loaded into the current code environment. Until that point any code module is simply stored in the database. In many ways like any other storable object.

Once it is loaded up to run, it is not possible to remove it. This would be akin to trying to delete an EXE file while it was still running.

I'm not aware of any procedure that would allow any module to be unloaded, thereby allowing it to be removed after it's code had been run.

[Nates]

That would explain the problems I've been having and the reason why a module can be deleted only if the code within had yet to be run.

As an alternative, is it possible to import code from a saved BAS overwriting the contents of a stored module?

[NeoPa]

I had a look, but I couldn't find a way to access the contents of a module programmatically I'm afraid. Someone else may know that they can and how to do it though.

[ADezii]

@Nates

why a module can be deleted only if the code within had yet to be run.

Not exactly true, Nates. I duplicated your functionality to a certain degree and had no problems Executing Code within a Module then Deleting it. Here is what I did.

1. Created a Standard Code Module, in this case Module1.
2. Created a Public Function within Module1 named fGenerateRandoms(). This Function will generate 1,000 Random Numbers, then Print them to the Immediate Window.
3. Outside the context of Module1 (in the Click() Event of a Command Button on a Form), executed Code that will Call fGenerateRandoms(), then immediately DELETE Module1.
4. The Code runs flawlessly
5. I'll post the relevant Code below as well as Attach a simple Test Database for a visual cue.
6. This is a very interesting problem, so let's keep this Thread moving, and I'm sure we'll come up with an answer. In other words, I shan't give up! Is that a word? (LOL)?
7. Function definition in Module1:
   

   Public Function fGenerateRandoms()
   Dim intRndNums As Integer
    
   Randomize
    
   For intRndNums = 1 To 1000
     Debug.Print "Random & "; Format$(intRndNums, "0000") & ": " & Rnd
   Next
   End Function

8. Call to fGenerateRandoms(0 then subsequent Deletion of Module1, code executed from the Click() Event of a Command Button:
   

   Private Sub cmdTest_Click()
   On Error GoTo Err_cmdTest_Click
   Dim Comp As Object
    
   Call fGenerateRandoms
    
   For Each Comp In Application.VBE.ActiveVBProject.VBComponents
     If Comp.Name = "Module1" Then
       Application.VBE.ActiveVBProject.VBComponents.Remove Comp
         Exit For
     End If
   Next
    
   Exit_cmdTest_Click:
       Exit Sub
    
   Err_cmdTest_Click:
       MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
       Resume Exit_cmdTest_Click
   End Sub

[ADezii]

Another point comes to mind, Nates. What is the context of the Code Execution within the Module to be Deleted? What I am getting at is this: if the Code has significant Execution Time, the Code may not be finished executing prior to the Deletion of the Code Module. Assuming it is run asynchronously, you will be attempting to Delete a Code Module which is still Active. Just rambling.

[ADezii]

@Nates

Anyone know the function for saving an imported module?

To Import a Module named basGlobals from a pre-determined Path, then Save it:

Application.VBE.ActiveVBProject.VBComponents.Import FileName:="C:\_TheScripts\VB Project\basGlobals.bas"
DoCmd.Save acModule, "basGlobals"

[NeoPa]

@ADezii
Yes. I shan't ==> I will not.

Sounds like ADezii's curiosity is piqued. That's always good news :)

I will monitor this closely as manipulation of code by code is always interesting (even if quite dangerous in the wrong hands).

[ADezii]

@NeoPa
I somehow knew the shan't would get ya! (LOL)!

[FishVal]

Gentlemen.

I've experimented a while with this problem and the only way I could reproduce the bug is when subroutine supposed to remove module contain early compiled call to public subroutine in the module to delete.

e.g.

Module: Module1

Public Sub Sub1()
    .....
End Sub
 

Module: Main

Public Sub DelAndImport()
    With Application.VBE.ActiveVBProject.VBComponents
        .Remove .Item("Module1")
        .Import "X:\Module1.bas"
        Sub1
    End With
End Sub
 

on the other hand, indirect call of the sub works flawlessly


Module: Module1

Public Sub Sub1()
    .....
End Sub
 

Module: Main

Public Sub DelAndImport()
    With Application.VBE.ActiveVBProject.VBComponents
        .Remove .Item("Module1")
        .Import "X:\Module1.bas"
        Run "Sub1"
    End With
End Sub
 

This, I guess, makes a perfect sense. Since there is a compiled and running code referencing code module, it could not be removed.

So, in few words, the problem is not that the module is loaded since some code in it has been invoked, but that there is a compiled call to this module in code being executed.

Regards,
Fish

[NeoPa]

That makes sense Fish.

late-binding should be fine for such a temporary module I would expect.

[ADezii]

Don't forget 2 other viable, and probably more efficient approaches to running Functions/Sub-Routines in External Modules, namely:

1. Create a Library Database (*.mda/*.mde) consisting of the Module's functionality. You could then set a Reference, either manually or dynamically, to this Library Database and access its Sub-Routines, Functions, Constants, etc...
2. (Automation) - Set an Object Reference to the Database containing the External Module, then use the Run Method of the Application Object to execute its Routines, etc. This approach, would of course, involve encapsulating the Module's Code within a DB.
3. I feel as though either approach would be better than the current one.

[Nates]

I have managed to delete a BAS then import a new version and then run the sub within the loaded BAS without any trouble. The problem arrives when I try to remove this BAS to load a new one.

The database is a motor insurance quote engine which will need to load one of several hundred BAS files at runtime dependant on the effective month / years rates selected by the user. I have attached a number of screenshots to try to better highlight this. It seems the first time a BAS is removed and a new one loaded everything works fine, however, each subsequant BAS seems to cease any classes that are active (they will appear to be set back to Nothing).

We are limited on space hence why we are not using multiple MDB files to act as code containers.

EDIT: Have uploaded a ZIP with the full size screenshots.

Thanks for all your help so far guys :)

[FishVal]

Hello, Nates.

• Did you try indirect call using Application.Run method.
• What is so different and unpredictable in those calculations that you have to write a separate VBA code for each?

[Nates]

@FishVal
I've not tried to indirectly call, will give that a go now.

The code generates an insurance premium. Although the same table of risk information is used to generate a premium i.e. they all use Driver's Sex, Age, Vehicle, Cover etc they behave differently when generating underwriting terms based on this information (be it differing excesses, endorsements, premium breakdowns). Some of the product BAS files require little over 200 lines of code to achieve the final premium, others are closer to 2000.

[ADezii]

@Nates
Any possibility of Uploading a Mini-Version of your DB with only a few Modules, and only the minimal and essential code that is failing? You need only to include the relevant code along with any other specifics. The data itself is also irrelevant, can even be dummy, and need only parallel the actual data in the DB.

[Nates]

Will see if I can get something uploaded today.

Thanks for continuing to offer assistance, really appreciated.

[ADezii]

@Nates
If the size of the Attachment is too large for the System to accept, let me know, and you can send it to my personal E-Mail Account which I will give you in a Private Message.