Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

How to avoid opening a password-protected mdb Expand / Collapse
Author
Message
Posted Thursday, October 31, 2013 7:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 2,305, Visits: 2,783
Take a look at this site: http://allenbrowne.com/func-ado.html. You can use the sample to query the database for a list of tables. Hopefully this will give you an error when executing against a password protected database, so you know you'll need to skip the rest of the function.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1510187
Posted Thursday, October 31, 2013 10:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:43 AM
Points: 100, Visits: 795
Thank you, still no can do. Following your latest advice, the code is now
Sub ProcessDatabase(ByVal strPath As String)
Dim vbc As VBIDE.VBComponent, objConnection As ADODB.Connection, strConnection As String

On Error GoTo ExitHere
Set objConnection = New ADODB.Connection
strConnection = GetConnectionstring(strPath)
objConnection.Open strConnection
ShowSchema
objConnection.Close
app.OpenCurrentDatabase strPath, , ""
For Each vbc In app.VBE.ActiveVBProject.VBComponents
ProcessModule vbc.CodeModule, strPath
Next vbc
ExitHere:
On Error Resume Next
' If Err = 2467 Then
' app.CloseCurrentDatabase
' Exit Sub
' End If
app.CloseCurrentDatabase
End Sub
but it just glides through the ShowSchema function without any error when it encounters a password-protected mdb(which I've attached to this post); for your info, the content of the immediate window, after it does that is
tblEvents
tblEvents
tblEvents
tblEvents
tblEvents
tblEvents
tblEvents
tblEvents
tblEvents
tblEvents
tblEvents
tblEvents
tblEvents
tblEvents


  Post Attachments 
NorthSecure.zip (1 view, 446.55 KB)
Post #1510322
Posted Saturday, November 2, 2013 12:14 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 2,305, Visits: 2,783
The code below will give you a working example. It will loop through all files within the given folder. When the file is of a type: "Microsoft Access Database" then an ADODB connection is setup to that file. When an errors occurres a message is displayed stating the filename and the error message. Else the message is displayed stating the file is accessible.
strPath = "{your folder path here}"

Set objConn = CreateObject("ADODB.Connection")

' create objects to the file system
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(strPath)
Set colItems = objFolder.Items

' ignore errors and proceed to the next line
On Error Resume Next

' loop through each file
For Each objItem in colItems
' check the file type
if objItem.type = "Microsoft Access Database" then
' build the connection string and try to open the file
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & "\" & objItem.name & ";"
objConn.Open strConnect
' check for errors
If Err.Number = 0 Then
' no error: replace the line below with the code you need to perform on an accessible database
wscript.echo objItem.name & " can be opened." & vbCrLf
else
' error occurred: remove this ELSE block if you don't want any messages regarding inaccessible databases
wscript.echo objItem.name & " is inaccessible." & vbCrLf & err.description & vbCrLf
end if
' reset error
Err.Clear
end if
Next
wscript.echo "End script"


Replace the messages (ECHO commands) with the applicable code suitible to your needs.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1510863
Posted Sunday, November 3, 2013 3:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:43 AM
Points: 100, Visits: 795
Hi again, thank you, I'll only be able to test your code tomorrow on a password-protected mdb. I hope I'm wrong but don't you think that since your latest code mimics your previous code:
strConnection = GetConnectionstring(strPath)
objConnection.Open strConnection
no error will be raised even when a password-protected mdb is encountered?
Post #1510881
Posted Sunday, November 3, 2013 1:25 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 2,305, Visits: 2,783
Yesterday I was finally able to create a test case of my own. The code I provided in the previous post is tested by me.
Initially I just used the code "objConn.Open strConnect". This did indeed default pop-up with the password-mismatch message. But when I also used the code "On Error Resume Next" it didn't pop-up anymore and the process continued to the "if err.number" block, so I could display my own message box.
Please let me know your results...


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1510928
Posted Monday, November 4, 2013 3:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:43 AM
Points: 100, Visits: 795
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
Post #1511029
Posted Monday, November 4, 2013 12:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:43 AM
Points: 100, Visits: 795
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?
Post #1511246
Posted Monday, November 4, 2013 12:48 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 2,305, Visits: 2,783
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.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1511256
Posted Thursday, August 21, 2014 11:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:18 PM
Points: 4, Visits: 6
This is a good tip, I didn't know this was possible.

AccessFIX
Post #1605930
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse