By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
461,947 Members | 736 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 461,947 IT Pros & Developers. It's quick & easy.

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

P: 16
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.
May 28 '09 #1
Share this Question
Share on Google+
20 Replies

P: 16
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
Attached Images
File Type: jpg ImmediateTest.jpg (16.5 KB, 357 views)
File Type: jpg Immediate2.jpg (19.1 KB, 329 views)
May 28 '09 #2

ADezii
Expert 5K+
P: 8,732
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:
Expand|Select|Wrap|Line Numbers
  1. Application.RefreshDatabaseWindow
May 28 '09 #3

P: 16
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?
May 28 '09 #4

NeoPa
Expert Mod 15k+
P: 31,770
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.
May 28 '09 #5

P: 16
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?
May 28 '09 #6

NeoPa
Expert Mod 15k+
P: 31,770
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.
May 28 '09 #7

ADezii
Expert 5K+
P: 8,732
@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:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fGenerateRandoms()
    2. Dim intRndNums As Integer
    3.  
    4. Randomize
    5.  
    6. For intRndNums = 1 To 1000
    7.   Debug.Print "Random & "; Format$(intRndNums, "0000") & ": " & Rnd
    8. Next
    9. End Function
  8. Call to fGenerateRandoms(0 then subsequent Deletion of Module1, code executed from the Click() Event of a Command Button:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub cmdTest_Click()
    2. On Error GoTo Err_cmdTest_Click
    3. Dim Comp As Object
    4.  
    5. Call fGenerateRandoms
    6.  
    7. For Each Comp In Application.VBE.ActiveVBProject.VBComponents
    8.   If Comp.Name = "Module1" Then
    9.     Application.VBE.ActiveVBProject.VBComponents.Remove Comp
    10.       Exit For
    11.   End If
    12. Next
    13.  
    14. Exit_cmdTest_Click:
    15.     Exit Sub
    16.  
    17. Err_cmdTest_Click:
    18.     MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
    19.     Resume Exit_cmdTest_Click
    20. End Sub
May 28 '09 #8

ADezii
Expert 5K+
P: 8,732
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.
May 28 '09 #9

ADezii
Expert 5K+
P: 8,732
@Nates
Anyone know the function for saving an imported module?
To Import a Module named basGlobals from a pre-determined Path, then Save it:
Expand|Select|Wrap|Line Numbers
  1. Application.VBE.ActiveVBProject.VBComponents.Import FileName:="C:\_TheScripts\VB Project\basGlobals.bas"
  2. DoCmd.Save acModule, "basGlobals"
May 28 '09 #10

NeoPa
Expert Mod 15k+
P: 31,770
@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).
May 28 '09 #11

ADezii
Expert 5K+
P: 8,732
@NeoPa
I somehow knew the shan't would get ya! (LOL)!
May 28 '09 #12

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. Public Sub Sub1()
  2.     .....
  3. End Sub
  4.  
Module: Main
Expand|Select|Wrap|Line Numbers
  1. Public Sub DelAndImport()
  2.     With Application.VBE.ActiveVBProject.VBComponents
  3.         .Remove .Item("Module1")
  4.         .Import "X:\Module1.bas"
  5.         Sub1
  6.     End With
  7. End Sub
  8.  
on the other hand, indirect call of the sub works flawlessly


Module: Module1
Expand|Select|Wrap|Line Numbers
  1. Public Sub Sub1()
  2.     .....
  3. End Sub
  4.  
Module: Main
Expand|Select|Wrap|Line Numbers
  1. Public Sub DelAndImport()
  2.     With Application.VBE.ActiveVBProject.VBComponents
  3.         .Remove .Item("Module1")
  4.         .Import "X:\Module1.bas"
  5.         Run "Sub1"
  6.     End With
  7. End Sub
  8.  
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
May 28 '09 #13

NeoPa
Expert Mod 15k+
P: 31,770
That makes sense Fish.

late-binding should be fine for such a temporary module I would expect.
May 28 '09 #14

ADezii
Expert 5K+
P: 8,732
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.
May 28 '09 #15

P: 16
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 :)
Attached Images
File Type: jpg CodeFiles.jpg (14.1 KB, 344 views)
File Type: jpg MonthYearSelect1.jpg (7.7 KB, 319 views)
File Type: jpg QuoteInfo.jpg (13.3 KB, 309 views)
File Type: jpg PremiumGeneratedByBASSub.jpg (14.0 KB, 290 views)
Attached Files
File Type: zip Screenshots.zip (337.2 KB, 100 views)
Jun 2 '09 #16

FishVal
Expert 2.5K+
P: 2,653
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?
Jun 2 '09 #17

P: 16
@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.
Jun 2 '09 #18

ADezii
Expert 5K+
P: 8,732
@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.
Jun 2 '09 #19

P: 16
Will see if I can get something uploaded today.

Thanks for continuing to offer assistance, really appreciated.
Jun 3 '09 #20

ADezii
Expert 5K+
P: 8,732
@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.
Jun 3 '09 #21

Post your reply

Sign in to post your reply or Sign up for a free account.