none
Access password protected files

    Question

  • I've created a VBA process that will open each database in a directory, extract a table, then close the database. As there are hundreds of files in the target directory the process needs to run after hours. When it encouters a password-protected file, the process stops and prompts for the password. We're willing to live without the data from those files, but we need a way to detect a file as password-protected without trying to open it. If the file is password-protected we'll just have the process skip over it. Is there a way to determine if a file is password-protected without actually opening it? TIA.
    Wednesday, September 12, 2012 7:00 PM

Answers

  • Tom, we've been using TransferDatabase. Van, we don't know the passwords-- which is why we discourage people from using them. Fortunately we can do what we need to do without them.

    One of my colleagues did come up with a solution---

    If not isPwProtected(strPathAndFileName) then

                                    ‘open & process access dbase

                    End if

    This way you won’t open a p-word protected Access dbase.

    Sub Test()

        Debug.Print isPwProtected("L:\Operate1\jhollifield\test.accdb")

        Debug.Print isPwProtected("L:\Operate1\jhollifield\tmp.accdb")

    End Sub

    Function isPwProtected(strFile As String) As Boolean

        Dim cn As ADODB.Connection, strConnect As String

        Set cn = New ADODB.Connection

        On Error GoTo errHand

        strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Persist Security Info=False; "

        'Debug.Print strConnect

        cn.ConnectionString = strConnect

        cn.Open

        cn.Close

        Set cn = Nothing

        Exit Function

    errHand:

        isPwProtected = True

         Set cn = Nothing

    End Function

    Thanx for your help.

    Thursday, September 13, 2012 7:23 PM
  • THat would probably work. I think this untested pseudocode would too:

    on error goto ErrHander
    strFile = dir$("...")
    while strFile <> ""
    100  docmd.transferdatabase strFile, ...
      strFile = dir$()
    wend

    ExitHandler
    exit sub
    ErrHandler
    if erl=100 and err.number=??? then
      debug.print "The table in file " & strFile & " failed to transfer. Does it have a password?"
      resume next
    else
      'todo: standard error handler
    end if


    -Tom. Microsoft Access MVP

    Friday, September 14, 2012 4:00 AM

All replies

  • How are you extracting the table? There may be alternatives that would simply create a runtime error which you can trap. For example I would think the TransferDatabase call would fall in this category.

    -Tom. Microsoft Access MVP

    Thursday, September 13, 2012 1:30 AM
  • I can't think of any way detecting whether a database file is an (Acess)-password-protected database without opening it since it is internal in the database itself and not a file attribute you can check.

    Do you have a list of database file names and passwords for the relevant files?

    If yes, you can create a Table storing file names and passwords.  In your code, you can create a Recordset and then traverse the Recordset opening each file with or without password as appropriate and then do your processing before navigating to the next row in your Recordset to repeat the same process.


    Van Dinh


    • Edited by Van DinhMVP Thursday, September 13, 2012 3:03 AM
    Thursday, September 13, 2012 3:02 AM
  • Tom, we've been using TransferDatabase. Van, we don't know the passwords-- which is why we discourage people from using them. Fortunately we can do what we need to do without them.

    One of my colleagues did come up with a solution---

    If not isPwProtected(strPathAndFileName) then

                                    ‘open & process access dbase

                    End if

    This way you won’t open a p-word protected Access dbase.

    Sub Test()

        Debug.Print isPwProtected("L:\Operate1\jhollifield\test.accdb")

        Debug.Print isPwProtected("L:\Operate1\jhollifield\tmp.accdb")

    End Sub

    Function isPwProtected(strFile As String) As Boolean

        Dim cn As ADODB.Connection, strConnect As String

        Set cn = New ADODB.Connection

        On Error GoTo errHand

        strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Persist Security Info=False; "

        'Debug.Print strConnect

        cn.ConnectionString = strConnect

        cn.Open

        cn.Close

        Set cn = Nothing

        Exit Function

    errHand:

        isPwProtected = True

         Set cn = Nothing

    End Function

    Thanx for your help.

    Thursday, September 13, 2012 7:23 PM
  • THat would probably work. I think this untested pseudocode would too:

    on error goto ErrHander
    strFile = dir$("...")
    while strFile <> ""
    100  docmd.transferdatabase strFile, ...
      strFile = dir$()
    wend

    ExitHandler
    exit sub
    ErrHandler
    if erl=100 and err.number=??? then
      debug.print "The table in file " & strFile & " failed to transfer. Does it have a password?"
      resume next
    else
      'todo: standard error handler
    end if


    -Tom. Microsoft Access MVP

    Friday, September 14, 2012 4:00 AM