[Rollin_Again]

Change this line

fs.CopyFile dbPath & "\" & OldDbName, dbPath & "\" & DbBackup

to

fs.CopyFile dbPath & "\" & OldDbName, dbPath & "\" & NewDbName

You can also ommit the following line since it is not needed at all

DbBackup = Mid(OldDbName, 1, Len(OldDbName) - 4) & "_" & Format(Date, "mmddyy") & ".mdb"

Regards,
Rollin

[computerman29642]

I changed the code, and took out the code that was not needed.

I am getting a Run-Time Error '53': File not found

[Rollin_Again]

Click debug and the line with the error should be highlighted. Once highlighted you should be able to hover your cursor over each of the variable names (dbpath, OldDbName, and NewDbName)

Make sure these values are correct

Regards,
Rollin

[computerman29642]

I had to fix the nam eof the database in the following two lines of teh code:

OldDbName = "ReorderMonitoring" & ".mdb"
NewDbName = "ReorderMonitoringNew" & ".mdb"

However, the back database does not appear to have been created.

NewDbName = "ReorderMonitoringNew" & ".mdb"

Instead of new at the end of the database name, can today's date be added?

[Rollin_Again]

So you are saying that the backup did not get created? That is strange because I tested using the same code after changing my DB name to match yours and it worked. You can append the date very easily by using the following line

]NewDbName = "ReorderMonitoring_" & Replace(Date, "/", "_") & ".mdb"

Regards,
Rollin

[computerman29642]

Okay, I have it creating the backup now. It does not appear to be compacting and reparing the database.

[computerman29642]

I tried adding the following code:

DBEngine.CompactDatabase dbPath & "\" & OldDbName, dbPath & "\" & NewDbName

to get it to compact, but I get an error message. I want the main database to be compacted and repaired, not the backup.

[Rollin_Again]

Why don't you just set the DB to compact on close? This will also ensure that the DB is optimized when you go to open it.

Tools >> Options >> General Tab >> Compact On Close

I don't think you can compact the database using code while it is open and in use. The only other way you could do it is to have your code close the database and then call a seperate script to compact and repair it and then re-open it. It would probably be easier to compact on close don't you think??

Regards,
Rollin

[computerman29642]

I did not know you could do that. That works for me thanks.

Will the code create a backup each time the database is opened or just on a new day?

[Rollin_Again]

Originally Posted by gamecockfan

Will the code create a backup each time the database is opened or just on a new day?

It should work each time the DB is opened. If the DB is opened multiple times in the same day it will keep overwriting the existing backup with a newer backup unless you modify the script to append the current time as well.

Regards,
Rollin

[computerman29642]

Perfect. Thanks so much Rollin' for all of your help.

[Rollin_Again]

You are most welcome. Have a great weekend!

Regards,
Rollin

[computerman29642]

You have a great weekend as well.

I just found that when you open the backup database, it does not allow you to open any of the tables. It gives you an permissions error. Why?

[Rollin_Again]

What is the exact error message? I am unable to duplicate this error.

Regards,
Rollin

[computerman29642]

There is two error message...

"Could not read definitions; no read definitions permission for table or query"

"You do not have the necessary permissions to use the object. Contact your system administrator."