SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to avoid opening a password-protected mdb


How to avoid opening a password-protected mdb

Author
Message
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8526 Visits: 3718
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’! **
grovelli-262555
grovelli-262555
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 888
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
Attachments
NorthSecure.zip (6 views, 446.00 KB)
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8526 Visits: 3718
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’! **
grovelli-262555
grovelli-262555
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 888
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?
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8526 Visits: 3718
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’! **
grovelli-262555
grovelli-262555
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 888
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

grovelli-262555
grovelli-262555
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 888
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?
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8526 Visits: 3718
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’! **
maximo121
maximo121
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 10
This is a good tip, I didn't know this was possible.

Max
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search