Opiions on what is the best method/ format for storing Procedures and Functions developed over time

[kc-mass]

I have hundreds of DBs and thousands of Procedures and Functions that I've
written over time.
Right now they are stored in the original DBs. To reuse them, I have to
remember which GD DB they were in. Does anyone have a system or concept
(preferably Access based) for storing these in an easily retrieved fashion?

Thanks

Kevin

 

[Steve Sanford]

Hi Kevin,

This is an interesting idea. Here is what I came up with.

The idea is to export the modules as code, parse the procedures and save
them in a record.

Arvin has some code that will export each module as a text file:
http://www.accessmvp.com/Arvin/DocDatabase.txt


I modified Arvin's code to just export the modules code. Then, I created a
new mdb and a table.

I named the table "tblCode".

The fields in the table are:
ID Autonumber
txtSubFunc Text 'Function or Sub
txtName Text 'Procedure name
mCode Memo 'the code

To parse the text files, I came up with this code:
(could be attached to a button)
'*********************************************************
Option Compare Database
Option Explicit


Public Sub ParseTextFile()
Dim d As DAO.Database
Dim rs As DAO.Recordset

Dim k As Integer
Dim t As Integer

Dim FileName As String ' Name of text file to process
Dim InputString As String ' input string
Dim ProcName As String ' name of function
Dim subSTR As String
Dim FuncSubType As String

Dim IsFuncSub As Boolean ' is this the start of a proc

Dim paren As Long
Dim f As Long

Set d = CurrentDb

'open the recordset
Set rs = d.OpenRecordset("tblCode")

'----------------------------------------------------------------------
'could read all of the text file names into an array
' then loop thru them
FileName = "D:\docs\modules\Test_mod.txt"
'----------------------------------------------------------------------

' open the text file
k = FreeFile
Open FileName For Input As #k

'loop thru each line of the text file
Do While Not EOF(k)
IsFuncSub = False

'read line
Line Input #k, InputString
InputString = Trim(InputString)

'check for start of Function or Sub
If InStr(1, InputString, "Function ") > 0 Then
FuncSubType = "Function"
f = InStr(1, InputString, "Function") + 9
IsFuncSub = True
ElseIf InStr(1, InputString, "Sub ") > 0 Then
FuncSubType = "Sub"
f = InStr(1, InputString, "Sub") + 4
IsFuncSub = True
End If

'is this the start of a Sub or Function?
If IsFuncSub Then
'get function name
paren = InStr(1, InputString, "(")
ProcName = Mid(InputString, f, paren - f)

'insert a new record
rs.AddNew
rs.Fields("txtSubFunc") = FuncSubType
rs.Fields("txtName") = ProcName
rs.Fields("Mcode") = InputString & vbCrLf

'loop thru the rest of the procedure
Do
Line Input #k, InputString
rs.Fields("Mcode") = rs.Fields("Mcode") & InputString & vbCrLf
Loop Until Trim(InputString) = "End " & FuncSubType
rs.Update
End If
Loop

'clean up
Close #k
rs.Close
Set rs = Nothing
Set d = Nothing
End Sub
'******************************************************


Additional modifications to the code:

~ Error handler code
~ Read all of the text file names into an array and loop thru them; delete
the files when all files have been processed
~ Read all of the MDBs into an array, link/import the Modules, then export
the code, unlink, process text files



HTH

 

[Steve Sanford]

One problem I haven't figured out how to deal with is if there are global
variables or a global type definition for one or two procedures. Right now
they are skipped.

HTH

 

[Banana]

One problem I haven't figured out how to deal with is if there are global
variables or a global type definition for one or two procedures. Right now
they are skipped.

HTH

If there's a variable that should be shared among certain procedure and
isn't really meant to be truly global, mark them as private. This will
restrict the visibility to only procedures within the same module and be
inaccessible by any other procedures in other modules.

Example:

<code>

Option Compare Database
Option Explicit

Private MyVar As Long

Public Sub ShowIt()

Msgbox MyVar

End Sub

Public Sub IncrementIt()

MyVar = MyVar + 1

End Sub

</code>

In the example, MyVar is accessible only to ShowIt and IncrementIt. It
cannot be reached by any other procedure that aren't in the same module.

An addition to this is that for certain variables you may want to use
Property procedures which gives you more control over how the variable
can be managed.


<code>

Option Compare Database
Option Explicit

Private pMyVar As Long

Property Get MyVar() As Long

MyVaar = pMyVar

End Property

Property Let MyVar(lInput As Long)

If lInput > 0 Then
pMyVar = lInput
End if

End Property

</code>


Here, we restrict the publicly accessible variable "MyVar" to only
positive range of the Long by using Property Let to check the new value
and change the value only if it's bigger than zero.

It is possible to have only Proeprty Get without Property Let, thus
making the variable read-only, or Property Let without Property Get,
making the variable write-only. There's also Property Set but I really
don't like it as this requires using Set, and Property Let can handle
this just fine.

HTH.

 

[kc-mass]

Thanks very much Steve
Regards

Kevin

One problem I haven't figured out how to deal with is if there are global
variables or a global type definition for one or two procedures. Right now
they are skipped.

HTH

 

[Steve Sanford]

I agree with you, but Kevin's first post said "I have hundreds of DBs and
thousands of Procedures and Functions ......."

so Kevin would have to go thru each one of his DBs; he was trying to avoid
this.

Where I discovered the problem with my code was when I ran the code on a
module that only had the standard Windows File Open/Save dialog box code from
"The Access Web" (http://www.mvps.org/access/api/api0001.htm)

It has a Type definition and global variables; the dialog box wouldn't
function without those declarations.

One solution (maybe) is to check for words "Type", "Declare", "Global", ....
that is before the first SUB/FUNCTION and print the MDB and module name so
it can be reviewed or added to the specific SUB/FUNCTION it pertains to.


HTH