Solved

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

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

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 51

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now