Solved

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

Posted on 2014-09-06
5
332 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

792 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