Solved: Access 2003 &amp;amp; Batch File - Page 2

Rollin_Again · 10-Jul-2008, 05:46 PM **#16**

Change this line

Code:

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

to

Code:

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

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

Code:

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

Regards,
Rollin

computerman29642 · 11-Jul-2008, 08:27 AM **#17**

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 · 11-Jul-2008, 01:26 PM **#18**

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 · 11-Jul-2008, 01:33 PM **#19**

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

Code:

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

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

Code:

NewDbName = "ReorderMonitoringNew" & ".mdb"

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

Rollin_Again · 11-Jul-2008, 01:39 PM **#20**

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

Code:

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

Regards,
Rollin

computerman29642 · 11-Jul-2008, 01:45 PM **#21**

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

computerman29642 · 11-Jul-2008, 01:52 PM **#22**

I tried adding the following code:

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 · 02:27 PM

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 · 11-Jul-2008, 02:24 PM **#24**

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 · 11-Jul-2008, 02:29 PM **#25**

Quote:

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 · 11-Jul-2008, 02:31 PM **#26**

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

Rollin_Again · 11-Jul-2008, 02:43 PM **#27**

You are most welcome. Have a great weekend!

Regards,
Rollin

computerman29642 · 11-Jul-2008, 02:48 PM **#28**

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 · 11-Jul-2008, 02:56 PM **#29**

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

Regards,
Rollin

computerman29642 · 11-Jul-2008, 02:59 PM **#30**

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."

Search
Search in:
Show Threads Show Posts
Advanced Search

Tag Cloud
acer black screen boot computer connection crash css dell display driver drivers email error ethernet excel explorer firefox firefox 3 game hard drive internet internet explorer itunes laptop lcd linux malware monitor network networking nvidia outlook outlook 2003 outlook express partition password printer problem router slow software sound trojan usb video virus vista windows windows xp wireless

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)