delete an Access module using VBScript 2

[teach314]

is it possible to use VBScript to delete an Access module?

Let's say we know the following---
mdb file's Path is: C:\Users\Teach\Desktopmdb file's Name is: TEST.mdb
mdb Module's NAME is: mod_16

How could I use VBScript to delete mod_16?

many thanks for any assistance

 

[MajP]

http://www.cpearson.com/excel/vbe.aspx

 

[strongm]

I think it may be somewhat simpler than that.

Try:

[blue]Private Function DeleteModule(strDatabase, strModule)
    Dim acModule
    acModule = 5
    With CreateObject("Access.Application")
        .OpenCurrentDatabase strDatabase
        .DoCmd.DeleteObject acModule, strModule
    End With
End Function[/blue]

And invoke with
[tt]DeleteModule "C:\Users\Teach\Desktop\TEST.mdb", "mod_16"[/tt]

 

[teach314]

thanks strongm. that worked perfectly.

I'm new to VBScript, but it really helped in this case. I wonder if I could ask 2 followups.

1) in the line acModule = 5 , what does the 5 represent?

2) in another part of my code, I use VBScript to IMPORT a module from one mdb file into another mdb file. Instead of opening 2 mdb files, I'd like to know if I can use VBScript to create module Mod_32 in the Destination mdb file, then insert text into it directly from VBScript into the destination mdb file?

Thanks in advance for any clues.

 

[MajP]

Take a look at that link provided.

 

[strongm]

>what does the 5 represent

It is a constant. When passed to various Access functions and procedures that can operate on a variety of different Access objects (e.g modules, forms, queries) it tells the function or procedure that the object being operated on is a module.

 

[teach314]

thanks to MajP and strongm for taking the time. I did look at MajP's link, which was full of great ideas, but it seemed to be in more of an Excel setting. Also, each routine made reference to VBIDE.VBProject, which was a bit beyond me.

I'm really hoping to see if I can use VBScript to create...

empty module: (Mod_32) in
strPathName = C:\Users\Teach\Desktop\TEST.mdb

then add text directly from the VB Script into the newly formed module.

Thanks again

 

[MajP]

Can I ask why you want to do this? There may be a better and simpler way to accomplish the same thing. Writing dynamic code is an advanced function, and used only in extreme cases. If you were an experienced VBA programmer then it may be an option, but I have a sense you are not. I have written tens of thousands of lines of vba code, and never had the need to do this.

 

[teach314]

that's a fair question - I'm assisting with some mathematics research, but coding isn't my background at all, and there's a good chance I've got the wrong approach. Here's an overview:

(SOURCE) : I have a SOURCE folder holding several SOURCE mdb files, each holding several SOURCE tables.
(most of the mdb files are from 300 MB to 1.3 GB in size - that's relevant to what follows)

Because of the large number of large tables, the SOURCE folder is kept on an external drive.

These SOURCE tables hold data that will run through a large number of tests. Each test is contained
in an Access module (i'll call them mod_A, mod_B, mod_C, etc), but these are stored in a separate
mdb file called TEST_TEMPLATES.mdb. They DO NOT appear in the SOURCE mdb files that contain the data about to be tested.

(DESTINATION): As each test is run, results are stored in a DEST folder. This folder holds several DEST mdb files, each holding several DEST tables.
There is a 1:1 correspondence between each SOURCE mdb file and each DEST mdb file, and between each SOURCE table and each DEST table.


I plan to use VBScript (a total novice at this!) to orchestrate the whole process. The main steps i'd planned are:

a) use VBScript to move the appropriate module (let's say, mod_A) from TEST_TEMPLATE.mdb into a SOURCE mdb file, kept on EXTERNAL DRIVE G:\) *the point of my 2nd question
b) use VBScript to run the functions in mod_A to process each table in the SOURCE mdb file...
c) store the output as it is being produced in an EXTERNAL DESTINATION folder. (this is the only way I could beat the BLOAT problem
in the SOURCE files - use code in, say, mod_A now in the SOURCE mdb file to crunch the data, then store it externally on EXTERNAL DRIVE H:\)
d) use VBScript to delete mod_A from the SOURCE mdb files, restoring them to their original condition. *the point of my 1st question

e) use VBScript to repeat all of the above for the tables in each SOURCE mdb file.

f) use VBScript to repeat all of the above for the tests in mod_B, mod_C... storing each output in its own DEST folder on EXTERNAL DRIVE H:
well, MajP - I bet your sorry you asked what I was trying to do! There very well could be an easier approach, but I don't see it yet.
By the way, all of the Access queries, VBA, etc already work perfectly, and I can step through the process manually and get the desired outputs.
I'm just struggling with getting VBScript to oversee and automate the process. My 2 questions about using VBScript to manipulate Access modules
were 2 of the final details I needed. Thanks again for your help!

 

[MajP]

There is no reason to move code. There is one code mdb that references the other source MDBs. Not sure why you think the code needs to move into the source files.

 

[teach314]

hi MajP - you've got right to the heart of the issue. I was originally trying to get the CODE mdb to process data in the SOURCE mdbs, then store the output in the DESTINATION mdbs. You seem to be indicating that that's the correct approach. I guess the basic approach is to:

a) SELECT.... FROM the external SOURCE tables.... then
b) INSERT INTO the external DESTINATION tables

I'll try that approach again. Thanks for taking the time.