Auto or user friendly split Database backups

[Barry A&P]

I was suprized to find out that there is almost no post info on automatically
backing up databases. the one post i found had code for SOON (shut one open
another) that i couldnt quite figure out. i am working on a 2007 runtime
database and i have a split database. i have found some vague explinations
that create a .bat file to copy the back end elsewhere but i understand there
cant be any users using the database. i have seen professional databases that
have a backup button like the Granite fleet manager.mdb and there is great
code out there like the J street re-linker is anybody have the time to
explain an Idiots guide to database backups..

any help would be greatly appreciated

Barry

 

[Dale_Fye via AccessMonster.com]

Barry,

You can do this several ways.

1. A method that generally works best when there are no users in the
application is to use the following method. But with this method, the
database has to be closed. You can accomplish this by forcing all of the
other users off and ensuring the the form that you have no forms, queries,
reports or recordsets open that use data from the backend database.

dbengine.CompactDatabase SourceName, DestinationName

2. Another method which works, even when others are active in the database
is to use the CreateDatabase method to create a new database. Then, loop
through the tables in the source database and using either of the following
methods to transfer each of the tables to the new database.

a. the Transferdatabase method, or
b. a Maketable query similar to:

SELECT * INTO TableName IN "path\NewDatabase.mdb"
FROM TableName

If you use this method, you may have to add some error handling to handle
instances where tables are locked.

3. A third technique, which I have not attempted, but which I have read
about in the various Access newsgroups is to use the Microsoft Task Scheduler
to create a task that will backup the data during down hours.

HTH
Dale

 

[Barry A&P]

Dale

Thanks for your input..
1. am i looking in the wrong places why cant i find more info on this??

2. i am interested in the transfer database method as i hear it is sometimes
good to just create a new database to get rid of little glitches ect that
access supposedly hangs on to?? I also cannot find specifics on this do you
have time for a slightly more in depth explination or example?

Thanks
Barry

 

[Armen Stein]

On Tue, 7 Jul 2009 20:19:01 -0700, Barry A&P


Hi Barry,

[Thanks for your earlier comment on our J Street Access Relinker - I
appreciate it.]

1. am i looking in the wrong places why cant i find more info on this??

I think it's because most people just make sure their back-end
database is in a folder that is backed up nightly, and they try to
make sure that users exit the database at the end of the day. So it
really isn't seen as an Access-specific problem, but rather a network
support issue.

There are techniques on auto-logout after a period of inactivity.
Search for Access auto logout.

For databases where more frequent and reliable backups are necessary,
you can switch to a SQL Server back-end database, which can be backed
up while in active use. But there are guidelines you must follow to
do it properly. I've written a PowerPoint presentation on techniques
for using Access as a client-server front-end to SQL Server databases.
It's called "Best of Both Worlds" at www.JStreetTech.com/Downloads. It
includes some thoughts on when to use SQL Server, performance and
security considerations, concurrency approaches, and techniques to
help everything run smoothly.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Barry A&P]

Armen

If you are the person to Blame, then yes i am very greatfull for your
generosity with the Re-Linker.. It is a wonderful bit of code..

My Mother started A small "Addiction counseling Center" she was looking for
some help tracking her clients and the sessions they attended. so and as a
school project i made her a small database. this is The database that is
giving me grief.

The database is access 2007 and i am doing it in a runtime package. there is
a desktop computer that has the backend and a copy of the front end. as well
as a laptop that also has a front end. the network is a simple local file
sharing network between the two, no servers no auto backups and unfortunately
no money. as of now they are digging into the programfiles/arcdatabase/
and copying the backend but they dont feel comfortable going inside and
copying stuff..

I am pretty new at this but am getting good at hunting stuff down if theres
good leads.. but so far nothing to go by..

I am hoping for help adding a backup link to my maintenance form.
code to check for database users ??? > Msg box "Users logged on try again
later"
code to copy the arcdatabaseBE from C:/ProgramFiles/arcdatabase to
F:/Backups with new filename arcdatabaseBE_Backup_(todaysDate)

Thanks for any more help

On Tue, 7 Jul 2009 20:19:01 -0700, Barry A&P


Hi Barry,

[Thanks for your earlier comment on our J Street Access Relinker - I
appreciate it.]

1. am i looking in the wrong places why cant i find more info on this??

I think it's because most people just make sure their back-end
database is in a folder that is backed up nightly, and they try to
make sure that users exit the database at the end of the day. So it
really isn't seen as an Access-specific problem, but rather a network
support issue.

There are techniques on auto-logout after a period of inactivity.
Search for Access auto logout.

For databases where more frequent and reliable backups are necessary,
you can switch to a SQL Server back-end database, which can be backed
up while in active use. But there are guidelines you must follow to
do it properly. I've written a PowerPoint presentation on techniques
for using Access as a client-server front-end to SQL Server databases.
It's called "Best of Both Worlds" at www.JStreetTech.com/Downloads. It
includes some thoughts on when to use SQL Server, performance and
security considerations, concurrency approaches, and techniques to
help everything run smoothly.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Armen Stein]

If you are the person to Blame, then yes i am very greatfull for your
generosity with the Re-Linker.. It is a wonderful bit of code..

Yes, that's me. Thanks, I'm glad you like it.

I am hoping for help adding a backup link to my maintenance form.
code to check for database users ??? > Msg box "Users logged on try again
later"
code to copy the arcdatabaseBE from C:/ProgramFiles/arcdatabase to
F:/Backups with new filename arcdatabaseBE_Backup_(todaysDate)

I don't have any specific links handy, but the two things you need are
1) checking for logged in users and 2) copying the backend file. Here
are some searches you could try:

Access logged in users
Access file system object

Armen Stein
Microsoft Access MVP
www.JStreetTech.com