Solved

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

Posted on 2014-09-06
5
342 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 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 85
ID: 40309027
0
 
LVL 55

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 55

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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
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…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

689 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