How to pass the password?

[trueharted]

I am attempting to utilize a compacting database I found online. The code loops through two fields in a table. One field is the path and the other field is the name of the database to be compacted. There are multiple databases to be compacted. The code works great on databases that are not password protected. All of my databases are password protected with the same password. Can anyone tell me how to pass the password in this code? I know how to do it with OpenDatabase, but not with OpenRecordset. Please let me know if you need more detail. Thanks ahead of time.

Public Function Compact_Database()
'===========================================================
'The Timer event runs this code every minute.
'It compares your system time with the StartTime
'variable. When they match, it begins compacting
'all databases in the DBNames table.
'===========================================================
Dim StartTime As String
' Set this variable for the time you want compacting to begin.
StartTime = "10:00 PM"
' If StartTime is now, open the DBNames table and start compacting.
If Format(Now(), "medium time&quot = Format(StartTime, _
"medium time&quot Then
Dim RS As DAO.Recordset, DB As DAO.Database
Dim NewDBName As String, DBName As String
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("DBNames&quot
On Error Resume Next
RS.MoveFirst
Do Until RS.EOF
DBName = RS("DBFolder&quot & "\" & RS("DBName&quot
' Create a new name for the compacted database.
' This example uses the old name plus the
' current date.
NewDBName = DBName 'Left(DBName, Len(DBName) - 4)
NewDBName = NewDBName & " " & Format(Date, "MMDDYY&quot& ".mdb"
DBEngine.CompactDatabase DBName, NewDBName
RS.MoveNext
Loop
' Close the form, and then close Microsoft Access
DoCmd.Close acForm, "CompactDB", acSaveYes
RS.Close
DoCmd.Quit acSaveYes

End If
End Function

-----------
Sincerely,
gricks

 

[kevinclark]

Good article with sample code at

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q170961

 

[trueharted]

kevinclark,

Thanks! That's exactly what I needed.

Next question: Is there any way to force it to compact to the same name instead of a different name? I get the error "Database Already Exists."

 

[kevinclark]

Hi gricks

I havn't played around with it too much but just a couple ideas might be to check and see if you can open the databases Non-Exclusively (in your code). However I am not sure that is the cure. A quick work around might be to add a couple lines of code right after the line --> DBEngine.CompactDatabase DBName, NewDBName to kill the original file DBName and then rename NewDBName to the original DBName. I know it sounds kind of scarry deleting the original file (In case something goes wrong with the new compacted one) but then I guess overwritting it would have the same affect.

 

[trueharted]

kevinclark,

Great ideas! Thank you. Not too worried about deleting the file, as it's backed up every night and I'd do the compacting after the backup. Worse case scenario, I have to ask the IS Manager to restore from backup. Thanks again.

 

[trueharted]

kevinclark,

I added the following two lines to my code after the line
--> DBEngine.CompactDatabase DBName, NewDBName:

Kill DBName
Name NewDBName As DBName

Works like a charm! Thanks so much. Very grateful.