Passing on the password (A2K SP3)

Topic

New Reply

I have set up a single password to my back-end database by opening it exclusively and then entering the password by menu Tools->Security->Set Database Password. In my codes, I have a line to compact the back-end which worked before implementation of the password:
DBEngine.CompactDatabase “Backend Database”, “Compact Database”

Now I am asked to key in the password whenever the code is run. How could I pass on the password to CompactDatabase without keying it in like in the code:
Set db = OpenDatabase(BackendDatabase, False, False, “;PWD=” & PASSWORD)

But I can’t open a database and then compact it.

Another alternative I am thinking of is to temporarily delete the password, run the CompactDatabase line, and then put the password back. But I don’t know how to do it. I tried using

Set db = OpenDatabase(BackendDatabase, False, False, “;PWD=” & PASSWORD)
db.NewPassword DBPASSWORD, “”

but I was told I couldn’t change the password of a shared open database. How can I access the back-end exclusively then?

Can any one help me? Thanks in anticipation.

Reply | Quote

Replies

What you could do is to use the Shell command and use the /compact and /pwd option on the command line, eg:

“c:Program FilesMicrosoft OfficeOfficeMSAccess.exe” “pathnameyourdatabasename.mdb” /compact “target database” /pwd “password”

Here is some help from A97 help:
The following table lists the Microsoft Access command-line options.

Option Effect
/pwd password Starts Microsoft Access using the specified password.
/compact target database Compacts the database specified before the /compact option and then closes Microsoft Access. If you omit a target database name following the /compact option, the database is compacted to the original database name and folder. To compact to a different name, specify a target database. If you don’t include a path in target database, the database is created in your My Documents folder by default.

Pat

Reply | Quote

Pat has given you some suggestions, but it appears there is a basic limitation with Access 2000 that you are not aware of. Trying to do this in code from a database that already has the back-end database open is going to give you trouble. You need to run that code from a separate database that has no connections (linked tables) to the back-end. If this doesn’t make sense, please post back and I’ll try to clarify.

Reply | Quote

Sorry for getting back to you late. I am aware of the problems when one tries to compact an open back-end. In my codes, I made sure the back-end was closed before compacting it. I could do this all along with my Access application. The problem arose because I wanted to put a password to my back-end and I wanted to compact the back-end by supplying the in-built password without user’s intervention. When I tried to look for help in the on-line help file, I couldn’t get any display of help when I double-click on the index CompactDatabase in my WinXP desktop. Nothing happened. That was when I sent my SOS to Woody’s Lounge. After reading your replies in my Win-Me notebook, I started Access in it and I could get the on-line help on CompactDatabase. I managed to compact the closed back-end in my codes as :

DBEngine.CompactDatabase “UncompactBackend”, “CompactBackend”, , , “;PWD=” & PASSWORD

Now my query is : why did the help on CompactDatabase suddenly disappeared from my desktop Access? I tried again, and discovered out of the several help indices displayed, some could be displayed the help content, some like CompactDatabase couldn’t. I remembered I could display all the help content before on my desktop. I suspect the help file(s) might have got corrupted. How would I install the Access on-line help again? Do I need to re-install Access or can I just copy the good help file(s) from my notebook to my desktop?

Thanks Pat and Wendell for your help. I really appreciate it.

Reply | Quote