November 4, 2013 at 3:02 am
Thank you so much, you were right, now everything works and here's the complete code below, a couple of considerations: at the end of the whole scan I get the code line
app.Quit acQuitSaveNone
highlighted with the message: run-time error 462: The remote server machine does not exist or is unavailable.
I don't know how to trap this error as well as the run-time error 52: bad file name or number I get when the ProcessFolder sub tries to access a folder which access to is denied such as C:\Documents and Settings\All Users\Application Data\Symantec\SRTSP\Quarantine\ (I overcame this by using On Error Resume Next at the start of the ProcessFolder sub)
Option Compare Database
Option Explicit
' Top folder to search
Const strBaseFolder = "C:\"
' Text to look for
Const strSearch = "DeleteTextRows"
Dim fso As Scripting.FileSystemObject
Dim app As Access.Application
Private Function GetConnectionstring(ByVal strDatabase As String) As String
Dim strProvider As String
On Error Resume Next
strProvider = "Microsoft.Jet.OLEDB.4.0"
GetConnectionstring = "Provider=" & strProvider & ";Data Source=" & strDatabase & ";Jet OLEDB"
End Function
Sub FileSearch()
Dim fld As Scripting.Folder
Set app = New Access.Application
Set fso = New Scripting.FileSystemObject
Set fld = fso.GetFolder(strBaseFolder)
ProcessFolder fld
Set fso = Nothing
app.Quit acQuitSaveNone 'here I get a run-time error 462: The remote server machine does not exist or is unavailable.
Set app = Nothing
End Sub
Sub ProcessFolder(ByVal fld As Scripting.Folder)
Dim strPath As String
Dim strFile As String
Dim sfl As Scripting.Folder
On Error Resume Next
strPath = fld.Path
If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If
'If Err.Number = 52 Then Resume Next ' bad file name or number which happens when the ProcessFolder sub tries
'to access a folder which access to is denied such as C:\Documents and Settings\All Users\Application Data\Symantec\SRTSP\Quarantine strFile = Dir(strPath & "*.mdb")
Do While strFile <> ""
ProcessDatabase strPath & strFile
strFile = Dir
Loop
For Each sfl In fld.SubFolders
ProcessFolder sfl
Next sfl
End Sub
Sub ProcessDatabase(ByVal strPath As String)
Dim vbc As VBIDE.VBComponent, objConnection As ADODB.Connection, strConnection As String
On Error Resume Next
Set objConnection = New ADODB.Connection
strConnection = GetConnectionstring(strPath)
objConnection.Open strConnection
If Err.Number = 0 Then ' no error: replace the line below with the code you need to perform on an accessible database
app.OpenCurrentDatabase strPath, , ""
For Each vbc In app.VBE.ActiveVBProject.VBComponents
ProcessModule vbc.CodeModule, strPath
Next vbc
Else ' error occurred: remove this ELSE block if you don't want any messages regarding inaccessible databases
MsgBox "Database " & _
strPath & " is inaccessible." & vbCrLf & Err.Description & vbCrLf, vbInformation
End If
' reset error
Err.Clear
objConnection.Close
ExitHere:
' If Err = 2467 Then
' app.CloseCurrentDatabase
' Exit Sub
' End If
app.CloseCurrentDatabase
End Sub
Sub ProcessModule(ByVal mdl As VBIDE.CodeModule, ByVal strPath As String)
Dim lngStartLine As Long, lngEndLine As Long, lngStartCol As Long, lngEndCol As Long
If mdl.Find(Target:=strSearch, StartLine:=lngStartLine, StartColumn:=lngStartCol, _
EndLine:=lngEndLine, EndColumn:=lngEndCol, WholeWord:=True) Then
MsgBox "Text found in module '" & mdl.Name & "' in database '" & _
strPath & "'", vbInformation
'End
End If
End Sub
November 4, 2013 at 12:31 pm
I have to partially take back what I said about everything working because for some reason the scan doesn't go through all the folders. I know this because I couldn't find the sub I'm looking for so I set the strSearch constant to a sub I know is in at least one of the unprotected mdb's on the hard disk and yet the scan couldn't locate it so I think probably it's because of the presence of system folders; is there any way to prevent scanning system folders?
November 4, 2013 at 12:48 pm
I'm sorry, this goes a little beyond my current knowledge. Because your current issue is regarding the file folders, I suggest you post this problem on a VB-script / VBA forum. It has little to do with databases so the expertise on this forum will probably not be enough.
Also take a look at scriptcenter on the Microsoft Technet site (http://gallery.technet.microsoft.com/ScriptCenter/). There you can find a lot of samples that you can use.
August 21, 2014 at 11:05 am
This is a good tip, I didn't know this was possible.
Max
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply