[RESOLVED] Find string in a MS Access Module using DAO
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
Re: Find string in a MS Access Module using DAO
I don't think you can expose those properties.
Re: Find string in a MS Access Module using DAO
Added reference to Microsoft Access Objects and used the Module datatype.
Re: Find string in a MS Access Module using DAO
So you got the properties of the Docs that were in the Containers in a Module of Access?
Re: Find string in a MS Access Module using DAO
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.
Re: Find string in a MS Access Module using DAO
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
Re: Find string in a MS Access Module using DAO
Re: Find string in a MS Access Module using DAO
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.
Code:
'...
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
'...
Re: Find string in a MS Access Module using DAO
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.
Re: Find string in a MS Access Module using DAO
Should have stated, they are all Microsoft Access mdb's.
Re: Find string in a MS Access Module using DAO
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!
Re: Find string in a MS Access Module using DAO
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.
Code:
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.
