ansaurus

Question

How to backup ms access database in vb.net?

Answer 1

+5 A:

System.IO.File.Copy("C:\Your\original\database.mdb", String.Format("D:\BackUps\{0:yyyyMMdd}.mdb", Date.Today))

Using openDialog As New OpenFileDialog()
    openDialog.CheckFileExists = True
    openDialog.CheckPathExists = True
    openDialog.Filter = "Microsoft Access Database (*.mdb)|*.mdb"

    Using saveDialog As New SaveFileDialog()
        saveDialog.CheckFileExists = False
        saveDialog.CheckPathExists = True
        saveDialog.FileName = Date.Now.ToString("yyyyMMdd") & ".mdb"
        saveDialog.Filter = "Microsoft Access Database (*.mdb)|*.mdb"

        If openDialog.ShowDialog() = Windows.Forms.DialogResult.OK AndAlso saveDialog.ShowDialog() = Windows.Forms.DialogResult.OK Then
            If File.Exists(openDialog.FileName) Then File.Copy(openDialog.FileName, saveDialog.FileName)
        End If
    End Using
End Using

Bobby 2009-12-28 11:57:22

That's it. An Access database is just a Windows file, to do a backup just copy the file (either to a CD, DVD, external drive, or on a backup server).

Meta-Knight 2009-12-28 15:19:16

A file-system copy of a Jet/ACE data file may or may not be valid. It depends on whether or not users have it open and in a state that won't be properly copyable. The only reliable way to back it up is via the Jet/ACE database engine, which will never create an inconsistent copy.

David-W-Fenton 2009-12-28 22:00:27

@David: The question was, how to back it up...well, that's one way of doing it (at least in my eyes). He never specified any conditions to match.

Bobby 2009-12-28 22:59:05

thanks! maybe I could slightly change the code that you gave so that I could make it work with an open file dialog box?

2009-12-29 23:03:09

I would assume that if you want to backup a file, you want that backup to be usable. Doing it with a file system copy may work 99% of the time, but it's that other 1% that's going to be an issue.

David-W-Fenton 2009-12-30 01:05:52

@David: What if the 99% is the case here? I still think we're talking about generic solutions, and not solutions to a problem he did not mention. Though, if you know an answer for the 1% then please feel free to post it, I'm sure somebody will find it helpful.

Bobby 2009-12-30 08:01:30

Absolutely agree with David on both accounts. Another "workaround" is to check if the .LDB file associated with the MDB exists, although this is not always a true indication of the database's state as a crashed MDB will sometimes leave an LDB on the file system.

Jason Snelders 2009-12-30 08:04:36

@Bobby - Guaranteed that 1% situation *will* occur, especially when it comes to Access (that's hard experience talking). And just because it wasn't mentioned doesn't mean it's not part of the problem - or should not be considered. The 1% is what often evolves into bugs. It's our responsibility as developers to consider and solve for them in the solution. It's also important - in fact, I'd say our 'responsibility' - to pass this knowledge on to new developers.

Jason Snelders 2009-12-30 08:12:37

@Jason: In that case I'll have to pass...I had the luck to never _really_ work with Access. But if you have another solution, please post it.

Bobby 2009-12-30 08:18:13

@Bobby asks: "if you you know an answer for the 1% then please feel free to post it." I refer you to my first comment, which concludes: "The only reliable way to back it up is via the Jet/ACE database engine, which will never create an inconsistent copy." From where I sit, it seems to me that I've already done what you asked for.

David-W-Fenton 2009-12-30 22:47:12

@David: I thought along the lines of an example...I mean, I, f.e., have no idea how to do that without breaking my fingers. And, I would be interested to hear how to do that (I'm always open to learn new things, even if I'll never need them).

Bobby 2009-12-30 23:45:14

thanks for your help,

2009-12-31 02:34:29

How to backup via Jet depends on your programming environment. In Access, you'd use DoCmd.TransferDatabase for the data tables. For a full backup with all properties (including, e.g., RI) it gets more complicated, and you might instead use a shell template database and append the data. The former will be faster, but the latter will get you a copy that could be dropped into replace a corrupted main file. Outside of Access, you'd have to do either of these things through DAO or ADO or OLEDB. I could provide code for DAO, but not for the others, as I'm an Access developer and don't use them.

David-W-Fenton 2009-12-31 22:03:35

And, BTW, if you're using a Jet data store from VB.NET and don't actually know how to use Jet, maybe you should choose a different data store.

David-W-Fenton 2009-12-31 22:04:10

Thanks David, good to know. :)

Bobby 2010-01-01 21:31:42

ansaurus

tags:

views:

answers:

How to backup ms access database in vb.net?

related questions