Automatically Import Modules or Forms into an Access 2016 Database when Running

[RMittelman]

Never tried this with Access, but this works with VbScript.

I have several Access databases that I support, and many of them have common code in modules & class modules. If I update a common module with fixes or new code, I then have to propagate that to all my other databases. For example, I have a utility module called modUtil, which contains a bunch of common routines.

When I add new code to modUtil in one of my databases, I then have to export the module for comparison. I then open another database and either delete modUtil and import the one exported from the other database, or export modUtil from 2nd database and use BeyondCompare or another file comparison utility to find differences and resolve them. This is quite time consuming, and must be repeated for each database that contains modUtil.

I noticed VbScript has a way of importing code into a script, and wondered if Access VBA could do this.

Here's some pseudocode for the concept, which could be executed from my AutoExec macro:

Open the Repository database
For Each myModule In MyProject.Modules
    If myModule exists in Repository database
        If any differences between module code
            Delete myModule
            Import myModule from Repository database
            Set a flag so I know something was updated
        End If
    End If
Next
If flag shows any changes were made
    Save my database
End If

This could save a bunch of ongoing maintenance and keep code in my projects always current.
Hopefully, this would work with common forms also.

Is this a good idea, or too fraught with risks to try?

Thanks...

[Micron]

Consider a code library db - all common code in one db that you then add as a reference in the other projects. Then that code is available to those projects, same as if you had set a reference to Excel, Word or Outlook libraries.

[RMittelman]

Consider a code library db - all common code in one db that you then add as a reference in the other projects. Then that code is available to those projects, same as if you had set a reference to Excel, Word or Outlook libraries.

Thanks Micron! That is exactly what I'm looking for. Been developing with Access since 2.0, and didn't know I could do this (slaps side of head).
I presume you can add a form and its code just as well as standard modules and class modules, right?

Also, it seems that using the code from a referenced DB, it may not be so easy to edit on-the-fly. If I'm single-stepping through some code which happens to live in the referenced DB, if I modify the code and Ctrl-S to save it, does it actually save the changes to the referenced DB? Thanks...

[Micron]

IIRC, you can edit referenced code but I don't think you can save changes. As for using a form in a referenced db, I don't know because truthfully, I've never used a code library even though I've known about them for a long time. Wouldn't take too long to test that but please let us know if it's possible, assuming no one else knows.

[CJ_London]

I presume you can add a form and its code just as well as standard modules and class modules, right?

you can - but it can only be called from that reference file. To overcome that, add a public sub or function to the referenced file that opens the form

if I modify the code and Ctrl-S to save it, does it actually save

No - the idea of using another db as a reference is that it doesn't change.

Also note in your reference file, if you want to execute sql relating to tables in the reference file, you use codedb.execute. If you use currentdb then the referenced file assumes you mean the host application.

[RMittelman]

you can - but it can only be called from that reference file. To overcome that, add a public sub or function to the referenced file that opens the form

I tried adding a common form that I can pop-up from any of my databases. I copied the form itself from one of my DBs into the reference DB. Since my form is opened by calling a class which initializes a bunch of values then opens the form, I also copied that class to the reference DB. To avoid problems, instead of deleting the form and class from the calling DB, I simply renamed them.

Now, when I execute code to use this form I get errors. Here is a snippet of the code that usually opens the form:

Private Sub cmdInfo_Click()

    Dim info        As New clsInfo
    Dim isOK        As Boolean
    Dim rs          As DAO.Recordset
    Dim myRecord    As DAO.Recordset
    Dim chosenItem  As String
    
    ' don't process if clicking on "new" record
    If Not Me.NewRecord Then
        
        With info
        
            .FormName = "frmInfo"
            .TableName = "tblInfo" '(or whatever your table is called)

The bold statement in this snippet generates the error "Invalid use of New keyword" Also, the project no longer compiles due to this error. Any ideas why I can't instantiate this class now that it is in the reference project?
Do I need to specifically call a Sub or Function in the reference DB instead of instantiating a class from the main DB, and have that function instantiate the class instead?
Thanks...

[orange]

RMittelman,

I think CJ meant for you to create a function in a standard module in the reference file that returns your class.

Along the lines:

Public Function NewStringXClass() As cStringX

   Set NewStringXClass = New cStringX
End Function

Here's the original link where I asked similar question and Markk responded and the approach was successful.

'---------------------------------------------------------------------------------------
' Procedure : demoClass_CstringX
' Author    : mellon
' Date      : 08/04/2016
' Purpose   :Trying this old class module cStringX.
' Issues with the mda library; had syntax issues trying to use NEW
'
'MARKK  AWF suggested a function in a standard module in jgold01_A2K library
'           to return a new instance of cStringX.  Works!
'
'  Append and Postpend  are same thing
'---------------------------------------------------------------------------------------
'
Sub demoClass_CstringX()
          Dim omystring As cstringx
          
        'this is a function that returns a cStringX  !!!! It is in a std module in jgold01__A2K.mda
10        Set omystring = newstringxclass 'this is a function in the library mda
20        On Error GoTo demoClass_CstringX_Error

          Dim otime As clsTimer
30        Set otime = New clsTimer
40        omystring.Value = "This is a test string to show the functionality of this class O'Malley"
50        Debug.Print "String value : " & omystring.Value
......

[Micron]

Perhaps you need to preface clsInfo with the name of the referenced db: As New testDb.clsInfo or something like that.

[CJ_London]

I think CJ meant for you to create a function in a standard module in the reference file that returns your class.

I did - I was thinking in terms of opening a form in the reference file, but I would expect the same to apply to a class module

[RMittelman]

This is working great so far. Thanks for all your help, guys!

I now have a standard module called "modUtil" in the reference DB, which I used to have to propagate to all of my projects, do text compares, etc. whenever I needed to make changes to it. I did have to make some adjustments to the code, as it's running as a reference project, such as some changing of CurrentDb to CodeDb. This is only if the CurrentDb method is doing things to the reference DB itself (thanks, CJ_London!). But as long as I'm acting against tables in the main DB, then CurrentDb works fine.

I also added a couple of common forms to the reference DB and removed them from my main DBs. This was a bit challenging, as you need to take out any RecordSources, as they don't work in the reference DB. I added code to set the Form's (or ListBox's) Recordset property after form load, and this worked just fine.

I added a couple of classes which can't be instantiated in the main DB. So I added functions in the reference DB which instantiate the classes then return the object to the main DB. Then I call the function to instantiate these classes. After that, everything works just as it did the old way.

One quirk:

I have 2 classes that I moved to the reference DB. One is called clsInfo and the other is clsFileDialog. Once the reference DB is well, referenced, in the code, I can have a statement like Dim info As clsInfo in my main DB method and it works just fine. I had to remove the New keyword as discussed earlier, and add a call to the reference DB function to instantiate the class object.

If I have a statement like Dim fd As clsFileDialog in my main DB, it gives me a compile error "User-defined type not Defined". This is strange because the 2 classes are defined in the reference DB identically. I can get around this by Dim fd As Object, and later Set fd = InstantiateFileDialog (from the reference DB). This then works fine.

Any idea why one class can be Dim'd as the class type and the other one must be Dim'd as Object?

Thanks again...

[Micron]

I'll take a wild guess. It sounds like a late vs early binding issue and you haven't set a reference to M$ File Scripting library in the calling db. Or is it set in the reference db, you're creating the class there and passing it back, in which case I don't know if the calling db needs the reference as well? Perhaps clsInfo works because that db has whatever reference it needs.

[RMittelman]

Thanks for the reply Micron. I definitely have a reference to Microsoft Scripting Runtime, and also Windows Script Host Object Model in the calling DB. Not sure why you picked that as the possible culprit. Doesn't seem, however that simply declaring an object variable as clsFileDialog should have anything to do with other references, as this error is occuring at compile time, not run time. The Microsoft Scripting Runtime is also referenced in the reference DB, but not sure about the Windows Script Host Object Model. I will check on this. Late binding by setting declaring the class object as Object seems to work fine, but I lose any intellisense when doing coding. Not the worst thing, becase since the reference DB is referenced, I can see its code modules and classes even from the calling DB by using the project browser, so I can find the syntax I need. Please advise if you think of anything else. Thanks again...

[RMittelman]

Up to now, I've had to prevent compile errors as follows:

I have a 3rd party library called Chilkat (www.chilkatsoft.com). This is installed via MSI install. Some of my customers' projects use Chilkat, and some don't. Because of this, my utility code, which uses Chilkat, needs to be modified for some customers, but not others. Therefore, I installed the Chilkat library on some customers PCs. In the code window, under Project-Project Properties, I put in a global constant: USE_CHILKAT = -1. For those customers, I use -1, and for the ones who don't use this library, I use 0 for the constant value.

Any of my utility functions that use the Chilkat Library, I surround the functions and declarations with #If USE_CHILKAT Then and #End If. This seems to work.

I can't think of a better way to manage this. I could check for presence of the Chilkat DLL, which is always installed in a known location, but don't know how to apply the #If and #End If to my code based on whether the DLL file is present. Usually these things are hard-coded.

Can I use something like:

If ChilkatFileExists Then
#USE_CHILKAT = True
Else
#USE_CHILKAT = False
End If

If this even works, I'd have to put it at the top of all of the reference DB modules rather than storing the global constant in properties, right?

[CJ_London]

not quite sure what you are asking - not tried it but perhaps store the value in the calling app properties - then in the library code use something like


if currentdb.properties("useChillcat") then

you would need some code to protect against the property not existing so perhaps incorporate into a boolean function that returns the error as false.

function useChillcat() as boolean

    'useChillcat=false - false is default so not really required
    on error resume next
    useChillcat=currentdb.properties("useChillcat")

end function

[RMittelman]

not quite sure what you are asking - not tried it but perhaps store the value in the calling app properties - then in the library code use something like


if currentdb.properties("useChillcat") then

you would need some code to protect against the property not existing so perhaps incorporate into a boolean function that returns the error as false.

function useChillcat() as boolean

    'useChillcat=false - false is default so not really required
    on error resume next
    useChillcat=currentdb.properties("useChillcat")

end function

Actually, I don't need to solve this anymore. I'm so used to having separate classes, and only distributing them to certain customers. If A customer's code calls a method which instantiate the clsChilkat class, and Chilkat is not installed, there is an error at compile time. So I used the USE_CHILKAT constant for conditional compilation purposes. Now that I'm putting all my shared classes into a reference project, I no longer need this. There will always be a clsChilkat in the reference project, so no compile errors. During program use, it's much easier to trap an error which is caused by trying to access the Chilkat library when it's not installed.

Thanks anyway...