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]

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?

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]

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?

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]

1. 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)?

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]

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).

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.