automated back up questions

[penfold1992]

I was taking a look at this thread:

http://www.access-programmers.co.uk/forums/showthread.php?p=1206150#post1206150

and I had a few questions...

Would this be possible to do even with people on the database?
If not I will have to deploy a method of removing people from the database.

Also can i make a vbs script run from the "task schedular" program that comes with windows 7? so that I can automatically make a database back up every day or so.

Thirdly, Is there a way in which I can keep a certain number of backups... a check to see how many files are in a folder, if there is more than 10, delete the earliest one and then save a new database back up.

 

[DavidAtWork]

you could achieve this by using another simple database which would copy the required database using the FSO method to a dedicated folder. The code could also log the copy in a log file table which can later be queried to determine which backup(s) require deletion. This code should be written in a function which can be called using the RunCode in an autoexec macro.
All you then need to do is create a scheduled task that will open this simple database file, this can be scheduled to run during the night when no users will be connected.
I think the FSO can also be used to get the file count in the dedicated folder and if more than the maximum allowed, you can delete using a query based on the log table

David

 

[penfold1992]

would it matter if the database was open on another computer though? if someone left their pc on overnight with the database still up, that may cause problems correct?

Also, I think I understand what you are getting at...

create another access database that you run which just contains a macro which will create a back up of the REAL database... also I could copy the table of the REAL database into this database as well.

at least... i think thats what you meant?

 

[DavidAtWork]

How is the database setup, is it split FE/BE, if so it won't matter at all. If the users are sharing a database file, it would still be ok, not sure about a situation where a user had been editing a record and left it in that state.

"... also I could copy the table of the REAL database into this database as well"
No, this is abosulutely not necessary, the only table I suggest you have is a log table with an autonumbered PK field to record each time a back-up is created with fields to record the backup filename and backup date. Use a simple:

Docmd.RunSQL("INSERT INTO tblBackupLOG(BU_Date, BU_filename) " _
" & VALUES('" + format(Date) + "', """ & varBUname & """)"

where varBUname is a string variable set to the backup file name

The log table can be queried to select the most recent top 10 (if you want to limit the number of backups to 10) using:

SELECT TOP 10 [BU_ID] ,[BU_filename], [BU_Date]
FROM tblBackupLOG
order by [BU_Date] desc

You can use the FSO to return each filename in the backup folder and for each filename you can use DLookup or DCount against the above query and if it doesn't exist, delete it

David

 

[penfold1992]

yes the database is split up front end / back end.

the normal set up of:
all the data and tables are in the BACK END
all the forms and queries are in the front end with the normal SQL commands to send the data from the forms into the table.

However I thought once you enter the front end the back end also gets locked to that record? im not too sure how it would handle that either but... it doesnt really matter, its a back up so if it doesnt contain the latest 2 minutes of data it really is better than losing a year or more's worth of data!!!

Also I think what I could do is program Access to autoclose after the backup has been made. this would make it easy to put inside a task scheduler.

Im sure I will get stuck somewhere along the line so if I do, ill post back!
thanks a lot david

 

[DavidAtWork]

"Also I think what I could do is program Access to autoclose after the backup has been made."
Just add another (final) step line to the autoexec macro to Quit, that will close the application

David