ms-access create - How to search through VBA code files

download ide (4)

Another option, not previously mentioned - is to just print the Project Code, from the context menu, in the VBA Editor. The steps below, can be modified, to suit applications available, but the result is the same - searchable text.

From the VBA Editor, right click the project name, and click print, from the context menu that appears. Make sure "Code" is checked, and click "OK". The printer can be changed, from the "Setup..." menu, available in the last dialog.

On a side note - stand alone modules, SQL, tables, etc, are available for printing, from the "Database Documenter", in the "Analyze" group, of the "DATABASE TOOLS" tab.

Very useful, for sorting and outlining underlying SQL, of Access queries.

I just started a job with a new company where previous developers had created many automated tasks. Of course, there is virtually no documentation and I didn't have a chance to work with the previous developer so now I'm trying to sift through all these processes looking for one that modifies some specific files.

I've scripted all the stored procs in SQL and used a search tool and didn't find what I was looking for, so now I am wondering if the process I need is located in one of many Access databases that are used. With SQL Server, it was easy to write a C# app to script the procs so I could search through them, but with Access it looks like I'm confined to opening each db individually to search through the code files.

Is there any way to programatically search through VBA code files?

Best to download the free MZ-Tools for VBA and use their search/replace function.


MZ-Tools for VBA is no longer available. The paid version works with newer office installations.

If your interest is searching code modules in an Access database file, you can use the VBE object model. This sample searches for a word in all the modules of the ActiveVBProject of the current database. If the database includes more than one VBProject, you can enumerate the VBProjects collection and search the projects one at a time by name:

For Each objComponent In Application.VBE.VBProjects(ProjName).VBComponents

Or if you prefer to reference the project by number rather than name, just be aware the numbering starts with 1 rather than 0.

Public Sub findWordInModules(ByVal pSearchWord As String)
    'Dim objComponent As VBComponent
    ' VBComponent requires reference to Microsoft Visual Basic
    ' for Applications Extensibility; use late binding instead:
    Dim objComponent As Object
    Dim strMessage As String
    Dim strModuleList As String

    strModuleList = vbNullString
    For Each objComponent In Application.VBE.ActiveVBProject.VBComponents
        If objComponent.CodeModule.Find(pSearchWord, 1, 1, -1, -1) = True Then
            strModuleList = strModuleList & "; " & objComponent.Name
        End If
    Next objComponent
    strMessage = "Text '" & pSearchWord & "' found in "
    If Len(strModuleList) > 0 Then
        strMessage = strMessage & "modules: " & Mid(strModuleList, 3)
        strMessage = strMessage & "no modules"
    End If
    Debug.Print strMessage
End Sub

Review the Access help topic for that Find method; you may prefer different options than I used.

If you want to target multiple db files and search the modules in each, you could automate this using the OpenDatabase method. I'll leave the details of that part up to you.

Since you're interested in future integration with a web application, you might consider using a Python web framework and running the app locally on your machine, using your web browser as the interface. In that case, one easy option would be web2py. Just download, unzip, and run, and you can use the web-based IDE (demo) to create a simple CRUD app very quickly (if you really want to keep it simple, you can even use the "New application wizard" (demo) to build the app). It includes its own server, so you can run your app locally, just like a desktop app.

You can use the web2py DAL (database abstraction layer) to define and create your SQLite database and tables (without writing any SQL). For example:

db = DAL('sqlite://storage.db')

    Field('name', requires=IS_NOT_IN_DB(db, '')),
    Field('email', requires=IS_EMAIL()))

The above code will create a SQLite database called storage.db and create a table called 'customer'. It also specifies form validators for the 'name' and 'email' fields, so whenever those fields are filled via a form, the entries will be validated ('name' cannot already be in the DB, and 'email' must be a valid email address format) -- if validation fails, the form will display appropriate error messages (which can be customized).

The DAL will also handle schema migrations automatically, so if you change your table definitions, the database schema will be updated (if necessary, you can turn off migrations completely or on a per table basis).

Once you have your data models defined, you can use web2py's CRUD system to handle all the data entry and searching. Just include these two lines (actually, they're already included in the 'welcome' scaffolding application):

from import Crud
crud = Crud(db)

And in a controller, define the following action:

def data():
    return dict(form=crud())

That will expose a set of pre-defined URLs that will enable you to create, list, search, view, update, and delete records in any table.

Of course, if you don't like some of the default behavior, there are lots of ways to customize the CRUD forms/displays, or you can use some of web2py's other forms functionality to build a completely custom interface. And web2py is a full-stack framework, so it will be easy to add functionality to your app as your needs expand (e.g., access control, notifications, etc.).

Note, web2py requires no installation or configuration and has no dependencies, so it's very easy to distribute your app to other machines -- just zip up the entire web2py folder (which will include your app folder) and unzip it on another machine. It will run on *nix, Mac, and Windows (on Windows, you will either need to install Python or download the web2py Windows binary instead of the source version -- the Windows binary includes its own Python interpreter).

If you have any questions, there's a very helpful and responsive mailing list. You might also get some ideas from some existing web2py applications.

ms-access vba access-vba