Solved

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

Posted on 2014-09-06
5
328 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
5 Comments
 
LVL 4

Assisted Solution

by:Jack Leach
Jack Leach earned 250 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 45

Assisted Solution

by:aikimark
aikimark earned 250 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 84
ID: 40309027
0
 
LVL 53

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 53

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

770 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