?
Solved

How to update a function in multiple Access databases (MDB, ACCDB)

Posted on 2014-09-06
5
Medium Priority
?
343 Views
Last Modified: 2014-10-26
We have a large number of access databases that contain a number of common functions.  We need to make a small change to one of the functions, and are looking for a way to automate this via scripting, etc.  Is there any way to get at the VBA code inside an Access database, and make a change to it?

I have used ADOX before from PS or VBS to be able to list tables, views and queries, but it does not expose the VBA code in its API.

~bp
0
Comment
Question by:Bill Prew
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 4

Assisted Solution

by:Jack Leach
Jack Leach earned 1000 total points
ID: 40308150
Hi,

You're looking for Visual Basic Extensibility (it's a reference that can be added, but by default you have some limited access to the application's code without it).

The best resource that I know of for this stuff is from Excel MVP Chip Pearson:

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

Get your bearings with it in a test project, then use automation to open other Access files and apply it to them.  Presumably, you can gather a list of databases to attempt this on, loop them, find the procedure by name and make the change.

It's a fair bit of work to set up, but is certainly capable.

hth
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 1000 total points
ID: 40308814
You might want to consider versioning and naming standards to make this easier in future change scenarios.  

Name:

By default, modules are created/imported with the default naming standard (name=module#).  Depending on the order of creation/import, your common module might be a different name.  You can force the imported module to have a specific name by including this line at the top of the .bas file:
Attribute VB_Name = "Q_28513065"

Open in new window

Alternatively, you might use a VB statement in the General Declarations section to hold that data.
Example:
Option Explicit

Const cModName As String = "Q_28513065"

Open in new window


Version:

Unfortunately, There is no equivalent Attribute statement for a user-defined module version.  So, you would need to do one of the following:
* incorporate the version data into the name
Example:
Attribute VB_Name = "mUtils_v24"

Open in new window

Note: If you have application code that refers to the module, qualified sub/function names during invocation, this scheme will not work well.

* Add a statement/line in the General Declarations section
Example:
Option Explicit

Const cModVersion As String = "2.4.23"

Open in new window

Note: Your change routine will need to iterate the modules/components and look for the version statement.  If the existing module is already at the same version level, there wouldn't be any reason to update it.

Compiled code:

Be sure to compile the code.

===================
A simplified and modified code snippet from RoryA:
    If ModuleExists("Module1") = True Then
        'Remove Modules
        VBProj.VBComponents.Remove VBProj.VBComponents("Module1")
        DoEvents
        'Import New Modules
        VBProj.VBComponents.Import "\\servername\sharename\common\Module1.bas"
    End If

Open in new window

The ModuleExists() function in the above snippet can be implemented as an iteration of the VBComponent collection items.
Option Explicit

Public Function ModuleExists(parmModName As String) As Boolean
    'matching by name only
    Dim oMod As Object
    ModuleExists = False
    For Each oMod In Application.VBE.ActiveVBProject.VBComponents
        If StrComp(oMod.Name, parmModName, vbTextCompare) Then
            ModuleExists = True
            Exit For
        End If
    Next
End Function
Public Function CodeModuleExists(parmModName As String) As Boolean
    'matching by name and code module type
    Dim oMod As Object
    CodeModuleExists = False
    For Each oMod In Application.VBE.ActiveVBProject.VBComponents
        If oMod.Type = 1 Then
            If StrComp(oMod.Name, parmModName, vbTextCompare) Then
                CodeModuleExists = True
                Exit For
            End If
        End If
    Next
End Function
Public Function ClassModuleExists(parmModName As String) As Boolean
    'matching by name and class module type
    Dim oMod As Object
    ClassModuleExists = False
    For Each oMod In Application.VBE.ActiveVBProject.VBComponents
        If oMod.Type = 2 Then
            If StrComp(oMod.Name, parmModName, vbTextCompare) Then
                ClassModuleExists = True
                Exit For
            End If
        End If
    Next
End Function

Open in new window

===============================
If you embed the version or name, you could search for the name from the result of the Lines() method of the CodeModule property.
instr(1, oMod.CodeModule.Lines(1, oMod.CodeModule.CountOfLines), _
"Const cModName As String = ""Q_28513065""", vbTextCompare)

Open in new window

0
 
LVL 85
ID: 40309027
0
 
LVL 56

Author Comment

by:Bill Prew
ID: 40309130
@Scott,

In my earlier question I was specific to ADOX, which I now understand likely can't work.  So I asked another question trying to broaden a bit, looking for any and all creative approaches to solve the problem.

~bp
0
 
LVL 56

Accepted Solution

by:
Bill Prew earned 0 total points
ID: 40405594
No answers here were what I was looking for (but as always, thanks to the participating experts).  The problem has changed a bit for me at this point, and I may be working it differently, so I'm going to close this one out.

~bp
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question