Programmatically Creating Modules in an Access 97 Database 4

[GComyn]

I'm trying to create a function that will take a input a code file into a module.

I know about AddFromFile, but I can't seem to get the database to create the module in the first place.... Every example that I've seen needs to already have the module created before you can use the AddFromFile method.

Any help would be appreciated.

Gwydion

 

[MichaelRed]

Hmmmmmmmmmmmm,

Interesting question. Never tried it that way -and- can't seem to find a reference to doing this (at least not quickly). I have, however, often re-set the "contents" of a module by deleting all of the lines of code in it and then 're-writting' the code by copying the contents of another file (either Code as text or another "module&quot. I have normally done this ONLY as a large "Search and Replace" process utillized in the conversion of code from one app (or version of an app) to another, but I am curious as to what use you have for the technique, would you mind 'sharing' a bit nore of your puzzle?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[GComyn]

Well....

I've got alot of text files that have functions/subs in them (from the web) and I wanted to import the files and put each file into a module of it's own.

I've already written a function that will import the files into a table (1 record per file), but you still have to manually move them into a module. I want to be able to just import them directly into a module.

As you said... I've not been able to find a way to do that programatically... I've looked all through the help file that comes with Access, and I've searched for 'create modules' here... but no luck.....

help that helps, and that you can help....

Gwydion

 

[Databaseguy]

Look into the containers and documents collections. Somewhere in there is Modules. And, if you search long enough, you'll run across LineofProc, ProcofLine, etc inside there. Probably what you're looking for... Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[MichaelRed]

O.K. I think this is doable -but only in a roundabout manner.

Adapting my BIG search & replace (destroy?).

From some vb code, you need to create (e.g. COPY) a VB header for a module with the correct module name. then, copy the related text file of procedures into the same text file. Save the thing as a .BAS file. (In Explorer, you can see these as individual items or files.)

From any MS. Access project, just drag and drop the files into it.

How about a list of thinggys? Some hungry soul may be interested.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

Tyrone,

NOt quite -I think-. Gwydion wants to CREATE the module from within Ms. A. I do not see a method for this. As I posted, I know how t0 create a valid VBA MODULE from almost anywhere - but instantiating it in a specific instance of Ms. A. is something I have only ever done manually, although I SUPPOSE one could be included via the references collection. IF this (references) can be used for a .BAS file as well as a .MDB file.

I'm not at all sure Gwydion would be happy with this, but I can easily see the path to this soloution.


Gwydion, if you will send me at least a sample of the text doc w/ the code and how you would set the naming convention, I can create the module(s). I would return the modules, and the process to create them to you. If inclusion via references is acceptable, I will also return a routine to do this (if it works properly).
MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[THoey]

Not trying to be a stick in the mud, but although this sounds doable (by some God like MichaelRed) wouldn't it be just as easy to take all the files and combine them, and then cut and paste in to a new module called UTILITIES. You could then import this into any MDB that you needed those tools...

Just my two cents... Terry M. Hoey

 

[MichaelRed]

Terry - please do not 'deify' me. I'm just an unemployed programmer wandering through the wastland of job searching. If it leaves more time on my hands, I just might as well attempt to keep these unuseable skills from turning to rust & dust for a few hours on occassional dull days.


What I 'understand' Gwydion wants is not really difficult, and could be quite a reasonable soloution to the conversion of a large (or huge) pile of 'Junque' text into useable modules and procedures. Your suggestion to simply concatenate the files and then cut and paste from the text file to a single (large or[HUGE]) nodule may cause some difficulties, as several of the procedures may have hte same declaration (procedure name) but not be the same procedure (code). Some may be variations on the theme.

Some may originate from alternate implementations of BASIC (quick basic? GWBasic? ...) These would probably need to be reviewed / debugged with some eye toward the source conventions and/or key words.

Converting them as seperate modules -with some concept of their orign- could greatly ease the 'grooming' to VB 6 standards.

Further, it the quantity of modules/procedures sources is large, it would be a lot faster and more accurate to do the job programmatically than the eye-hand coordination of cut and paste.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[GComyn]

Terry...

When you Load a module into memory, it loads the entire module... so the larger the module, the more in memory.... I try to have a few functions/subs in a module as possible, taking up as little memory as possible.... Especially if I don't use half of the large module at the time...

so loading the files into seperate modules is what I want to do....

 

[bdfigler]

I know Access 2000 loads the whole module into memory, but I thought that was new -- that Access 97 doesn't do that.

 

[raskew]

Everything seems to have already been resolved, except how to programattically create the module in the first place.

RunCommand may save the day.

The following will create, name, save and close a new module, which can then be used as a container for your text procedures.

It's down and dirty, I haven't included error handling which will surely be necessary since the procedure will bomb if the module name already exists.

As I found out when testing, if you attempt to run it in break-mode, you'll also receive some unexpected errors.

'*********************************************************
Function MyNewModule(pModName As String)
'Purpose: Programatically create and
' name a new module in the
' current db.

' Create the module.
docmd.RunCommand acCmdNewObjectModule

' Save module as pModName
docmd.Save , pModName

' to close the new module, uncomment the next line
' docmd.Close acModule, pModName, acSaveYes
End Function
'*********************************************************

For more on RunCommand usage, go here:

http://home.clara.net/tkwickenden/

and select 'One Stop'.

 

[MichaelRed]

interesting,

but it doesn't work in 2K -at least not as shown.

DoCmd.Save , pModName REQUIRES the acmodule argument

DoCmd.Save acModule, pModName

however even this doesn't quite get there, as the Module is created as ModuleN, in the traditional New Module naming convention. Thus, when attempting to "Save", (or Close) the module does not exist (as the supplied name), rasing an error.

Perhaps, the name property of the new module needs to be set before either save or close -at least for the 2K ver.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[llocklin]

A search of the MS KB gives ACC2000: How to Programmatically Create, Search, Replace, and Modify Code (Q208793) with examples of exactly what you're wanting to do.

Luther

 

[raskew]

Luther-

Thanks for finding that! Tried modifying the previous '97 code and works exactly as advertised (making sure that the Microsoft DAO 3.6 Object Library is selected):

Function MyNewModule2000(pModName As String)
'Purpose: Programatically create and
' name a new module in the
' current db.

Dim MyModule As Module

' Create the module.
DoCmd.RunCommand acCmdNewObjectModule

' Set MyModule to be the new Module Object.
Set MyModule = Application.Modules.Item(Application.Modules.Count - 1)


' Save, close, and rename the new Module.

DoCmd.Save acModule, MyModule

DoCmd.Close acModule, MyModule, acSaveYes

DoCmd.Rename pModName, acModule, MyModule

End Function

 

[GComyn]

Ok... I've looked at all of your suggestions... and here is what I did. I tried to use the AddFromFile method from the modules... but, if the file had any errors in the code, it would crap out.... so... I used the AddFromString instead... by going through the file one line at a time, adding an appostraphy at the beginning of each line (commenting the line out), then saving the module.... yes.. I know that means I have to go through each module and take the comments out, and fix them... but that's better than having to either retype them one by one, or cut and paste them one by one...

Here is the code I use:

Option Compare Database
Option Explicit
Const TABLENAME As String = "tblImportedCode"
Const REPORTSPATH As String = "C:\Backups\Access\Code\"
Const DblQuote As String = """"

Public Sub ImportCodeFilesToModules()
    Dim db As DAO.database
    Dim rs As DAO.Recordset
    Dim intFileNumber As Integer
    Dim strFileName As String
    Dim strLine As String
    Dim datDate As Date
    Dim strSourceFile As String
    Dim intFileSize As String
    Dim intFileCount As Integer
    Dim mdl As Module
    Dim intPos As Integer, i As Integer
    Dim strModuleName As String


    DoCmd.SetWarnings False
    Close
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(TABLENAME)
    intFileCount = 0
    strFileName = Dir(REPORTSPATH & "*.*")
    Do While strFileName <> &quot;&quot;
        If InStr(1, strFileName, &quot;.zip&quot;) = 0 And InStr(1, strFileName, &quot;.doc&quot;) = 0 And InStr(1, strFileName, &quot;.exe&quot;) = 0 And _
           InStr(1, strFileName, &quot;.mdb&quot;) = 0 And InStr(1, strFileName, &quot;.ppt&quot;) = 0 Then
            intFileCount = intFileCount + 1
            Close
            strSourceFile = REPORTSPATH & strFileName
            intFileNumber = FreeFile
            Open strSourceFile For Input As #intFileNumber
            datDate = CDate(Format(FileDateTime(strSourceFile), &quot;mm/dd/yyyy&quot;))
            intFileSize = FileLen(strSourceFile)
            intPos = InStr(1, strFileName, &quot;.&quot;)
            If intPos = 0 Then
                strModuleName = &quot;mod&quot; & strFileName
            Else
                strModuleName = &quot;mod&quot; & Left(strFileName, intPos - 1) & &quot;_&quot; & Mid(strFileName, intPos + 1)
            End If
            On Error Resume Next
            If Not IsError(db.Containers(&quot;Modules&quot;).Documents(strModuleName)) Then
                If Err.Number <> 3265 Then
                    For i = 1 To 20 Step 1
                        strModuleName = strModuleName & i
                        If IsError(db.Containers(&quot;modules&quot;).Documents(strModuleName)) Then
                            If Err.Number <> 3265 Then
                                strModuleName = strModuleName & i
                            Else
                                Exit For
                            End If
                        End If
                    Next 'i
                End If
            End If
            ' Create the module.
            DoCmd.RunCommand acCmdNewObjectModule
            
            ' Set MyModule to be the new Module Object.
            Set mdl = Application.Modules.item(Application.Modules.Count - 1)
            ' Save, close, and rename the new Module.
            DoCmd.Save acModule, mdl
            DoCmd.close acModule, mdl, acSaveYes
            DoCmd.Rename strModuleName, acModule, mdl
            DoCmd.OpenModule strModuleName
            Set mdl = Modules(strModuleName)
            mdl.AddFromString &quot;'File Date: &quot; & datDate
            mdl.AddFromString &quot;'File Size: &quot; & intFileSize & &quot; bytes&quot;
            Do While Not EOF(intFileNumber)
                Line Input #intFileNumber, strLine
                If Len(strLine) = 0 Then strLine = &quot; &quot;
                mdl.AddFromString &quot;' &quot; & strLine
            Loop
            DoCmd.close acModule, strModuleName, acSaveYes
            Close
        End If
        strFileName = Dir
    Loop
    MsgBox &quot;Completed importing &quot; & intFileCount & &quot; files.&quot;, vbInformation + vbOKOnly, &quot;I M P O R T I N G   C O M P L E T E&quot;
    rs.close: Set rs = Nothing
    db.close: Set db = Nothing
    DoCmd.SetWarnings True
End Sub

(remember, if you cut and paste this, check for carriage returns and line feeds in the wrong locations)

If anyone has any tweaks for comments on this, I'd be glad to hear them.

Gwydion

 

[GComyn]

In going over the code I just posted, I noticed that I had too many Dim statements. Basically I took the code I use to import the files into a table, and modified it. I didn't take out all of the unused Dim statements. I have now.... for the ImportCodeFilesToModules code, you do not need the TABLENAME constant, or any of the 'rs' variable lines. They are extraneous, and unused. Other than that, everything else is used.

Gwydion