Help using NewPassword method

[Tim Smith]

I'm trying to create a form that lets you change the database password using the NewPassword method on a Database object. It works when that database object is set to my 'back end' file containing all my data, but for the front end I set the database object to CurrentDb and it produces error 3188: "Could not update; currently locked by another session on this machine". As far as I'm aware, I have no other sessions open. I have the database open exclusively. And I'm using DAO. See code below:

Dim db As Database

Set db = CurrentDb
db.NewPassword Old_Password, New_Password

Set db = OpenDatabase({Back End Path}, True, False, "MS Access;PWD=" & Old_Password)
db.NewPassword Old_Password, New_Password
db.Close

Set db = Nothing

The code to change the password of the back end works fine. I've also tried using the NewPassword method on the front end from a new temporary database, while the front end is closed, and it works. I've even tried setting a password on that temporary database and using CurrentDb.Newpa ssword from within it and that works too, so it's a problem specific to my database front end.

Bizzarely, I had this working fine the first few times I did it yesterday! Any help would be much appreciated.

[MMcCarthy]

I think the problem is that you have the database opened exclusively. The opendatabase command is essentially trying to open another instance of the backend.

[NeoPa]

Mary's right. If anyone has the database open exclusively then no-one can change the password (as it requires opening from within code, even if it is already open to you the operator). Does that make sense?

[Tim Smith]

Thanks for the replies. Actually, when I use the OpenDatabase command to open an instance of the back end and change its password it works fine, despite the front end I'm doing this from having linked tables with the back end. The problem is with the front end that I am working in, I can't change the password of that.

According to this article you need to have the database opened exclusively:

http://msdn.microsoft.com/en-us/library/aa141813(office.10).aspx

And I've tried doing it with the database not opened exclusively and I get error 3621, "Cannot change password on a shared open database."

[NeoPa]

Tim Smith:
Actually, when I use the OpenDatabase command to open an instance of the back end and change its password it works fine, ...

Of course it does. You sound as if you think one of us has said otherwise. In fact, your whole posts indicates perhaps you haven't really grasped what was said. Let me see if I can make it clearer.

The database needs to be opened exclusively in order to change the password. It doesn't need to be opened by an operator to do this, unless that operator uses the menu structure to effect the change. If the change is made by the code, then the code needs to open the database exclusively itself. This is hardly possible if the database is already opened by the operator, exclusively or otherwise.

If that's still not clear then in practical terms, to change the password in code, you need to run the code from somewhere other than the database you are trying to change, ensure the database is opened exclusively in the code and ensure that the database is not opened at the time the code is run. If you do that you should find it works perfectly for you.

[Tim Smith]

If the change is made by the code, then the code needs to open the database exclusively itself.

But doesn't CurrentDb just mean you're using the same instance as the operator rather than opening a new one in the code?

to change the password in code, you need to run the code from somewhere other than the database you are trying to change

This isn't the case, as I've tested this and it can be done. I created a new database with a single form with two text boxes (old & new password) and one button with a single line of code: CurrentDb.NewPa ssword Old, New. This works.

[NeoPa]

Tim Smith:
But doesn't CurrentDb just mean you're using the same instance as the operator rather than opening a new one in the code?

I don't believe so no. I would be happy for anyone to clarify this. I cannot claim supreme expert status on this, and the help files are not too conclusive, but that is my understanding.

Tim Smith:
I created a new database with a single form with two text boxes (old & new password) and one button with a single line of code: CurrentDb.NewPa ssword Old, New. This works.

I was unable to confirm this. I tried a simpler test with literal strings of the values "Old" & "New", but the result I got was the error message :
Cannot change the password on a shared open database.

I don't know what to say. My reading and experience (not that I can claim to much in this area prior to looking it up for you) indicate it won't work. I'm not saying you must be wrong, as there are many variables out there to bite us when we're not looking, but I must say what I've found, which is what I've been saying.

I'm using Access 2003 for my research in case that throws any light. I can't say if things have changed possibly for more recent versions.

[Tim Smith]

I've only ever used Access 2007, I've only been using Access for a couple of years, so I don't if that makes a difference. Obviously, as you imply, there must be some variable that is allowing me to this on one database but not another. I just wish I could work out what it was!

Many thanks for your help anyway. I think I'm going to just create a separate 'utility' database for changing the password.

By the way, the error you got is the one I get when I try to use NewPassword inside a database not opened exclusively, and not the error that is befuddling me – don't know if that was to blame.

[NeoPa]

Tim Smith:
By the way, the error you got is the one I get when I try to use NewPassword inside a database not opened exclusively, and not the error that is befuddling me – don't know if that was to blame.

I was trying to illustrate that even without the database being opened exclusively, the code could not run (IE. Even when it is opened elsewhere at all - and elsewhere in this scenario includes by the operator who is causing the code to execute).

I really can't say I have the answers here, but something I'd try before I gave up on the issue would be to see if, in the same circumstances where the code fails for you (IE. The database opened exclusively - but no code active of course - the project must be cleared and all variables reset), the manual password change process works. If it does, and I would expect it to, it would tend to indicate that the code does indeed open a separate instance of the database as far as Access is concerned, and your solution of providing a Utility database, the only viable one to be found. Does that make sense?