[RESOLVED] Find string in a MS Access Module using DAO

[WeePecky]

Hi,

I am trying to write some VB6 code that will seek out a specific string in an Access Code Module. I can get to the modules name but cannot find properties or methods which will give me a handle to the code within the module.

Is it possible to do this?
If it is, could you please point me to an example or help.
Thanks
Wee!

========


Dim ctrLoop As Container
Dim prpLoop As Property
Dim doc As Document

With db
' Enumerate Containers collection.
For Each ctrLoop In .Containers
For Each doc In ctrLoop.Documents

If ctrLoop.Name = "Modules" Then
Debug.Print "Container: " & ctrLoop.Name & " Document: " & doc.Name & " Owner: " & doc.Owner
For Each prpLoop In doc.Properties
Debug.Print doc.Properties(x).Name
Next
End If

Next
Next ctrLoop


End With

[dglienna]

I don't think you can expose those properties.

[WeePecky]

Added reference to Microsoft Access Objects and used the Module datatype.

[dglienna]

So you got the properties of the Docs that were in the Containers in a Module of Access?

[WeePecky]

Yes, I got the properties.

I want to search the code in the modules and find a connection string.
We are trying to id all Access Aps that use data from an appliation that is about to be retired. We have got the linked tables but now want to get the programmed links as well.

Have found the Modules datatype in the MS Access Objects Library but now I am stuck as how to get the Modules to reference the db object being processed so I can get the modules within.

[UncleGizmo]

I Gather you want to search through the code in modules? If this is correct, then I recall reading about being able to add and delete lines from modules with code and I knew it was in one of my books. I've just had a look through and found it in my book "Programming Microsoft Access 2000" by Rick Dobson.

Further: I have also discovered that the chapter on this, chapter 7 is also available online. -
-
-
-Here is a link to the particular part of the chapter that may be of interest.
I would be very interested in seeing any code you develop and your general experience with this if you wouldn't mind posting it. Cheers Tony

Copied from Here http://www.access-programmers.co.uk/...d.php?t=136037

[dglienna]

That sounds right.

[WeePecky]

Hey thanks everyone - I have got the application running now.

But I have a new problem! Can anyone advise of an alternative way to open the target database? Some of the Access databases have been programmed to run in a specified directory, and the OpenCurrentDatabse method fires the opening code which results in an error.

Any help would be appreciated.

    '...

    Set App = New Access.Application
    App.OpenCurrentDatabase strMDBPath & strMDB, False
   
    Set dbModules = App.Modules
    Dim x As Long
    For x = 0 To dbModules.Count - 1
        Set dbModule = dbModules(x)
        StartLine = 0
        StartCol = 0
        EndLine = 0
        EndCol = 0
        If dbModule.Find("[string to search for]", StartLine, StartCol, EndLine, EndCol, False, True) Then
            Debug.Print dbModule.Name
        End If
    Next
    App.CloseCurrentDatabase

    '...

[dglienna]

Is it a text based file? Can you open it with Notepad and read it all? If so, you can read it. If it is binary, you don't know the format, so it's not possible.

[WeePecky]

Should have stated, they are all Microsoft Access mdb's.

[WeePecky]

I have solved my problem - with a few mods which I will document here for anyone who may need a solution...

I found that there is no method to get to the Module code in access from VB except via the Application object of Access. As I was in VB6 this did not work very well for Access Applications that needed to run in a particular directory but were resident in another. Each time the OpenDatabase method was invoked it raised a dialog and terminated the application.

After trying to resolve this by sending a shift key event to prevent the opening code from fireing I decided to log these databases and allow the user to then re-run the application manually holding the shift key down.

I was therefore left with an application that would stall when it experienced a dialog box. To resolve this I wrote a vb.net (2005) class which ran two threads. One to analyse each database module passed to it and one to check for a dialog window. If a dialog window was found by the second thread it would send an enter key to the window and close it. The thread that was analysing the database modules then would silently error and the calling function could log the database details in the analysis database for later reference.

So the application is a VB6 application calling a VB.NET COM object searching MS Access Database Modules for a specified string in the code.

"Impossible is nothing" - except maybe finding a SHIFTDOWN sendkeys parameter. :-)

Wee!

[WeePecky]

For those who are interested in this, it is possible to do this, and I found the solution out there on the net (see links below) after a bit of effort.

My solution was in VB.NET which is easier to multi-thread, but can be applied to VB6 as well. Did not need the second thread in the end.

    Private Structure OSVERSIONINFO
        Dim dwOSVersionInfoSize As Integer
        Dim dwMajorVersion As Integer
        Dim dwMinorVersion As Integer
        Dim dwBuildNumber As Integer
        Dim dwPlatformId As Integer
        <VBFixedString(128), System.Runtime.InteropServices.MarshalAs(System.Runtime.InteropServices.UnmanagedType.ByValTStr, SizeConst:=128)> Public szCSDVersion As String '  Maintenance string for PSS usage
    End Structure

    ' API declarations:
    'UPGRADE_WARNING: Structure OSVERSIONINFO may require marshalling attributes to be passed as an argument in this Declare statement. Click for more: 'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1050"'
    Private Declare Function GetVersionEx Lib "kernel32" Alias "GetVersionExA" (ByRef lpVersionInformation As OSVERSIONINFO) As Integer
    Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Integer, ByVal dwExtraInfo As Integer)
    Private Declare Function GetKeyboardState Lib "user32" (ByRef pbKeyState As Byte) As Integer
    Private Declare Function SetKeyboardState Lib "user32" (ByRef lppbKeyState As Byte) As Integer
    ' Constant declarations:

    Private Const VK_SHIFT As Short = &H10S

    Private Const KEYEVENTF_EXTENDEDKEY As Short = &H1S
    Private Const KEYEVENTF_KEYUP As Short = &H2S
    Private Const VER_PLATFORM_WIN32_NT As Short = 2
    Private Const VER_PLATFORM_WIN32_WINDOWS As Short = 1

    Public Sub ToggleShiftKey(ByVal PressedState As Boolean)
        'PressedState is true if we want the shift key pressed
        'PressedState is false if we want the shift key not pressed.
        Dim o As OSVERSIONINFO
        Dim ShiftPressedState As Boolean

        o.dwOSVersionInfoSize = Len(o)
        GetVersionEx(o)
        Dim keys(255) As Byte
        GetKeyboardState(keys(0))

        ' Shiftkey handling:
        ShiftPressedState = keys(VK_SHIFT)

        If o.dwPlatformId = VER_PLATFORM_WIN32_WINDOWS Then '=== Win95/98
            If PressedState Then
                keys(VK_SHIFT) = 1
                SetKeyboardState(keys(0))
            Else
                keys(VK_SHIFT) = 0
                SetKeyboardState(keys(0))
            End If
        ElseIf o.dwPlatformId = VER_PLATFORM_WIN32_NT Then  '=== WinNT
            If PressedState Then
                'Simulate Key Press
                keybd_event(VK_SHIFT, &H10S, KEYEVENTF_EXTENDEDKEY Or 0, 0)
                Debug.Print("Shift key is pressed...")
            Else
                'Simulate Key Release
                keybd_event(VK_SHIFT, &H10S, KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0)
                Debug.Print("Shift key is released...")
            End If
        End If

    End Sub


Private Sub AnalyseModules()

        Try

            Dim App As Microsoft.Office.Interop.Access.Application                                 'Gets an application object so modules may be accessed
            Dim dbModules As Microsoft.Office.Interop.Access.Modules
            Dim dbModule As Microsoft.Office.Interop.Access.Module

            Dim StartLine As Long                                         'Used in Module.Find method - Start line for search
            Dim StartCol As Long                                          'Used in Module.Find method - Start column for search
            Dim EndLine As Long                                           'Used in Module.Find method - End line of search (after match found)
            Dim EndCol As Long                                            'Used in Module.Find method - End column of search (after match found)
            Dim x As Long

            App = New Microsoft.Office.Interop.Access.Application

            Call ToggleShiftKey(True) 'on

            App.OpenCurrentDatabase(Me.clsStrMDBPath & Me.clsStrMDB, False)
            dbModules = App.Modules

            For x = 0 To dbModules.Count - 1
                dbModule = dbModules(x)
                StartLine = 0
                StartCol = 0
                EndLine = 0
                EndCol = 0

                If dbModule.Find(Me.clsSearchString, StartLine, StartCol, EndLine, EndCol, False, True) Then
                    If Me.clsModulesList = "" Then
                        Me.clsModulesList = dbModule.Name
                    Else
                        Me.clsModulesList = Me.clsModulesList & ", " & dbModule.Name
                    End If
                    Debug.Print(Me.clsModulesList)
                End If
            Next

            App.CloseCurrentDatabase()
            Call ToggleShiftKey(False) 'off


        Catch ex As Exception
            'Some databases will not be able to be analysed because they need to run in a specific location.
            'When this error occurs we will continue processing and report the database was not checked for connection strings
            If Me.clsModulesList = "" Then
                Me.clsErrorMessage = ex.Message
            Else
                Me.clsError = 5000 ' any non 0 number
            End If
            Debug.Print(ex.Message & " in SAM Analyse Modules")
        Finally

        End Try
    End Sub

For more information go see the master at:
http://blogs.msdn.com/robgruen/archi...15/114020.aspx
I stole his code - mostly.