change password via vba

[sramey00]

I am on the right track here, however i can't get the database to open exclusively to allow password changes.

can any point in the right direction?

On Error GoTo errorhandler

Dim strDbName As String
Dim strOLDDB As String
Dim strNewDb As String
Dim y As Integer

Dim acc As New Access.Application
Dim db As DAO.Database


strDbName = Dir(oldDbPath & "\*.MDB")   ' Retrieve the first MDB file entry.

'Loop though the files in the folder to find MDB files.
Do While strDbName <> ""
' Ignore the current folder and the encompassing folder.
  If strDbName <> "." And strDbName <> ".." Then

    If Right(strDbName, 3) = "mdb" Then

       Set acc = New Access.Application

        acc.Visible = False
    
        Set db = acc.DBEngine.OpenDatabase(oldDbPath & "\" & strDbName, False, False, "MS ACCESS;PWD=level1") '
        'Error message for previous line: -2147221163 Method 'DBEngine' of object '_Application' failed
        
       
        acc.OpenCurrentDatabase oldDbPath & "\" & strDbName, True, "level1"
        'should open exlusively, but doesn't... (path, exclusive, bstrPassword)
        
     
        CurrentDb.NewPassword "level1", "" 'change database password. can't unless db opened as exclusive.

        CurrentDb.Close
        Set acc = Nothing
        acc.Quit

        
    End If
  End If
strDbName = Dir    ' Get next MDB.
Loop
MsgBox "errors: " & y

Exit Sub

errorhandler:
    Call writeError(strDbName, Err.Number, Err.Description)
    y = y + 1


Resume Next

thanks!

Mr. Steve

 

[CautionMP]

Exerpt from the help file:
In the DAO listing the second two parameters to the OpenDatabase method indicate exclusive and read-only access respectively.

Set db = acc.DBEngine.OpenDatabase(oldDbPath & "\" & strDbName, [b]True[/b], False, "MS ACCESS;PWD=level1")

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[sramey00]

i tried what changing it to true, but i still recieved the error of

'Error message for previous line: -2147221163 Method 'DBEngine' of object '_Application' failed




Mr. Steve

 

[CautionMP]

Just to make sure we are starting at step 1, is the database that you are trying to open when you get the error in use by anyone else when you are trying to open it?

If someone else has the database open you cannot open it exclusively. Check to see if there is a file in the same directory with the same name and an .ldb extension.

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[sramey00]

no one has it open and there is no ldb file in the directory.

this problem has been troubling me all day!!

when i comment out the code line:

Set db = acc.DBEngine.OpenDatabase(oldDbPath & "\" & strDbName, False, False, "MS ACCESS;PWD=level1")

the only error i receive is the
"can't change password on shared db"

i am passing the correct parameters to the function, correct?

Mr. Steve

 

[CautionMP]

Yes as far as I can tell.

(GMT-07:00) Mountain Time (US & Canada)

 

[sramey00]

found this on a different message board as to why i was getting the dbengine error...

manually registered the dll and it stopped the error.

http://forums.oracle.com/forums/thread.jspa?threadID=316255&tstart=1

Hi,

I believe you are getting the "Method 'DBEngine' of object '_Application' failed" error because there is a problem with your DAO installation. If you manually register the dao360.dll, you should no longer experience this issue when you attempt to use the Exporter tool.

Execute :
Regsvr32 "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"

Please refer to the following article for detailed steps:

http://support.microsoft.com/?scid=kb;en-us;887033&spid=2509&sid=global

If this does not resolve your issue, please let me know.

I hope this helps.
Regards,
Hilary

 

[sramey00]

figured it out!!!

these two lines needed to be changed...

CurrentDb.NewPassword "level1", "" 'change database password. can't unless db opened as exclusive.

        CurrentDb.Close

needs to be changed to:

ACC.CurrentDb.NewPassword "level1", "" 'change database password. can't unless db opened as exclusive.

        ACC.CurrentDb.Close

hope this script helps!