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 .ActiveVBProjec t.VBComponents. Import FileName:=FileP ath & CODE_FOLDER & RatesYear & "\" & RatesMonth & "\" & ProductName & ".bas"

I use the following loop to remove the module:

For Each Comp In Application.VBE .ActiveVBProjec t.VBComponents
If Comp.Name = ProductName Then
Application.VBE .ActiveVBProjec t.VBComponents. Remove 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(Product Name 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 .ActiveVBProjec t.VBComponents
If Comp.Name = ProductName Then
Application.VBE .ActiveVBProjec t.VBComponents. Remove Comp
Exit For
End If
Next

'Retrieve most recent code module
Application.VBE .ActiveVBProjec t.VBComponents. Import FileName:=FileP ath & CODE_FOLDER & RatesYear & "\" & RatesMonth & "\" & ProductName & ".bas"

'Establish rates database connection and set product var
RatesConn.Conne ctionString = "Provider=Micro soft.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.deletobje ct 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.deleteobj ect 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 programmaticall y 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 fGenerateRandom s(). 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 fGenerateRandom s(), 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 fGenerateRandom s(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"