Solved

Auto backup data

Posted on 2009-07-10
14
750 Views
Last Modified: 2013-12-01
Hi all,
I have a MS access database runtime application.It is a split database and my client wants an auto backup for the backend in a regular interval. Its should be an auto backup so that my client need not worry about backing it up manually. Is there any way I could have a Vb script to include into my database so that it performs backup at a regular intervals without asking the user.

I'm pretty new to Access please help out
0
Comment
Question by:4rajiv
  • 7
  • 4
  • 2
  • +1
14 Comments
 
LVL 1

Expert Comment

by:oakridgeschools
Comment Utility
Hi there.

Just a quick suggestion - Have a look at the following link as it has some good suggestions for what you're looking for. Lots of ways to do this!  :-)

http://forums.techguy.org/business-applications/694131-any-auto-backup-access-2003-a.html
0
 

Author Comment

by:4rajiv
Comment Utility
the link wasnt helpful
0
 
LVL 84
Comment Utility
You can use INSERT INTO statements to make backups of your tables ...

Currentdb.Execute "INSERT INTO SomeTable IN 'c:\somefolder\somedatabase.mdb' SELECT * FROM YourTable"
0
 
LVL 84
Comment Utility
If you're "pretty new to Access", how do you already have clients, and have you told those clients that they've hired you to do a job using a tool you aren't really familiar with? Be sorta like hiring a plumber as an auto mechanic. I've seen a rash of this lately on EE, most of them from the same time zone ... makes me wonder what, exactly, is going on ...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
There are dozens of way to do this.

The most simple and pain free is what LSD posted.

Another alternative (If you or your client has money to burn) might be to buy a utility that will do this.
http://www.fmsinc.com/Products/agent/ver2.html#Features
http://www.fmsinc.com/Products/agent/ver2.html

It has a lot of built in feature that might be usefull for you if you are truly "pretty new to Access"

Just bear in mind that any commercial application to do things like this is probably just doing things that any Access developer can do.
All the commecial App does is probably wrap it up in a fancy interface.
Just another alternative...

JeffCoachman
0
 
LVL 1

Accepted Solution

by:
oakridgeschools earned 500 total points
Comment Utility
Another way to do this is to use good 'ol Microsoft backup. It is already installed on Windows, and can backup your databases automatically for as many different days as you want.

Want 7 different backups per week? Just setup Microsoft Backup to do it for you. That software doesn't get enough "press", but it does a good job. I've re-imaged entire servers (including Exchange databases) with it.

Here's a link on how to do setup Microsoft Backup to backup Access Databases. No need to write scripts, install extra software, or buy anything.

This will help if you're interested in this method: http://support.microsoft.com/kb/30842

Note, the above link tells you to install the backup software from the Windows CD, but most installs already have it listed in Start>Program>Accessories. I think it's in System Tools. I'd look for you, but I'm on a Ubuntu box at the moment.
0
 
LVL 84
Comment Utility
Your link is bad.

Any backup software will work if the file isn't being accessed. The trouble with trying to copy an in-use file is that it's almost certain to be corrupt or invalid (i.e. partial records, etc), which is why IF you must copy in-use files, the only safe method is via code.

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Expert Comment

by:oakridgeschools
Comment Utility
Egad! So it is... My apologies. I don't know what happened. I thought I double-checked that before I posted.

You can actually do what's called a volume shadow copy for databases... Here, since I can't seem to post a simple helpful link... I'll post this directly here:

****
Volume Shadow Copy  
The unique feature of Windows Backup (Microsoft Backup) software is an ability to back up locked files. Optionally, Windows Backup creates a volume shadow copy of your data to create an accurate point-in-time copy of the contents of your hard drive, including any open files or files that are being used by the system. For example, databases that are held open exclusively and files that are open due to operator or system activity are backed up during a volume shadow copy backup. Users can continue to access the system while the backup utility is running without risking loss of data. Shadow copy backups ensure that:  Applications can continue to write data to the volume during a backup.
Files that are open are no longer omitted during a backup.
Backups can be performed at any time, without locking out users.
****

I'll see if I can post some detailed instructions here on how to use volume shadow copy. You are correct that a normal backup will not back your files up correctly, however, I've backed up multiple databases for years using volume shadow copy. More to come...

0
 
LVL 1

Expert Comment

by:oakridgeschools
Comment Utility
OK, from what I've been reading, it looks like Microsoft Backup uses Volume Shadow Copy by default. This backs your data up on the "block" level, so this doesn't interfere with user programs. Unless you would rather go with somebody else's suggestion (you need to do what works best for you), I'd test this out real quick just to see if it works as you want it to. You need to test anything out before you rely on it, but this appears to be a viable, automatic solution for you.

If anybody can contradict what I said here about volume shadow copy, please feel free. It would benefit myself, LSMConsulting, and the community in general. :-)
0
 
LVL 1

Expert Comment

by:oakridgeschools
Comment Utility
I'm sorry, 4rajiv was the poster, not LSMConsulting.

@ LSM for your second post about him being new to Access. I do things all the time that are new territory for people. It's no big deal if you're smart enough to work your way through it. I just can't stand it when somebody asks for help, then somebody else reams them out about what they're doing or how they're doing it. It's not your place or mine. It is a plague in the tech community. Not enough respect goes out to those that are trying to learn something new. No offense meant, but please,  lighten up.
0
 
LVL 84
Comment Utility
Well "pretty new to <insert your technology>" + "my client" are, in my opinion, two phrases which should never go together. I'm not against anyone getting into this field - there's plenty to go around - but this isn't Excel or Word. We're dealing with data, and IMO you're doing your client a disservice is you represent yourself as something you're not. I'm not saying that the poster has done this, as I have no idea whether this is the case or not. I'm elbow deep in cleaning up a project that was the result of exactly this scenario, hence my somewhat flamish response. If I offended you or the poster then please accept my apologies, but I stand by my comments.

I have no specific knowledge of VSS, but I'll stick to my internal backups via SQL, as I know they work, even for high usage applications. Of course, you and others may have excellent results with backup software. I just know from my own personal experience that backup programs + Jet databases can = corrupt/invalid backups.
0
 
LVL 1

Expert Comment

by:oakridgeschools
Comment Utility
LSM, thanks. I just see a lot of junk go to "fresh" people, even if these people are otherwise very capable.  You are correct that people should not essentially lie about their abilities, but I have also seen a lot of techs be up front about their abilities, and that's OK with with clients or employees that have good relationships with each other. I have actually hired techs like this. Enough said. I agree with you about misrepresenting yourself. I just don't like to see people get "flamed" unless the community knows that is the case. Since we don't know, that's not a judgment we should make. I think we can agree on these points. :-) Enough said...

Alright,

I guess I'm drawing on my experience with BackupExec, which worked very well for us. :-)

Well, guess it's up to you 4rajiv. Lots of options. Do what works best for you. I hope all this discussion has enriched your post. It certainly hasn't been boring!
0
 

Author Comment

by:4rajiv
Comment Utility
Hi all,
Thanks for the response, Windows backup up utility does the the thing but i wanted to have a vba script in my runtime database to backup the tables regularly. Can anyone provide me with  a sample code or suggest some ideas???
0
 
LVL 1

Expert Comment

by:oakridgeschools
Comment Utility
Just another quick thought... You might want to post that same question over in the VBA section here in EE. Even if you have to post your question a couple of times, you can probably have a couple of the gurus on here start putting code together for you. It's probably a better "hangout" for guys that know VBA well vs here, even though you're working with a database. Whatever VBA script(s) they give you will probably work just fine.

Just a thought... :-)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

VM backups can be lost due to a number of reasons: accidental backup deletion, backup file corruption, disk failure, lost or stolen hardware, malicious attack, or due to some other undesired and unpredicted event. Thus, having more than one copy of …
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now