Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Backup & Restore Database

How to Backup & restore selected tables of access database using VB 6.0 ?

Comments

  • dokken2dokken2 Member Posts: 532
    I backup the entire MDB files, and manually restore a record or table if needed. see the uploaded project files - also use an open source zip compression to keep the files small and use the Windows "scheduled tasks" to run a daily backup
  • hemantsangoihemantsangoi Member Posts: 19
    Thanks for help. Can you helpme with SQL command to backup & Restore selected tables of a databasebecause the user is to Backup & Restore data who has access to database only through front end.
  • dokken2dokken2 Member Posts: 532
    here's 2 samples that will backup a table by inserting records into another MBD [backupMDB - uses a table with same name and definition]
    the first one is simple but a little dangerous in that if the sql INSERT fails, you've then lost the records in your backup file.

    the 2nd example uses ADO and performs the backup as a transaction. if the insert fails then a rollback is performed restoring the backup table to its previous state.

    [code]
    Sub SimpleBackup()
    Const BackupMDB = "'D:db4_BAK.mdb'"
    Dim SqlDelete As String
    Dim SqlInsert As String

    DoCmd.SetWarnings False

    SqlDelete = "DELETE Table1.* FROM Table1 IN " & BackupMDB
    DoCmd.RunSQL SqlDelete

    SqlInsert = "INSERT INTO Table1 IN " & BackupMDB & _
    "SELECT Table1.*" & _
    "FROM Table1;"
    DoCmd.RunSQL SqlInsert

    DoCmd.SetWarnings True
    End Sub


    'requires reference to ADO - "Microsoft ActiveX Data xx Library"
    Sub TransBackup()
    Const BackupMDB = "'D:db4_BAK.mdb'"
    Dim cn As ADODB.Connection
    Dim sCon As String
    Dim bTrans As Boolean

    On Error GoTo erh

    'TRANSACTION STATE, False=NOT BEGIN OR COMMIT, True=BEGIN
    bTrans = False

    'CONNECTION STRING
    sCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "User ID=Admin;Password=;" & _
    "Data Source=D:db4.mdb;" & _
    "Persist Security Info=False;"

    'CREATE NEW CONNECTION
    Set cn = New ADODB.Connection
    'OPEN CONNECTION WITH CONNECTION STRING
    If cn.State = adStateClosed Then cn.Open sCon

    'BEGIN TRANS
    cn.BeginTrans
    bTrans = True

    'SQL FOR BACKUP
    Dim SqlInsert As String
    Dim SqlDelete As String
    SqlDelete = "DELETE Table1.* FROM Table1 IN " & BackupMDB

    SqlInsert = "INSERT INTO Table1 IN " & BackupMDB & _
    "SELECT Table1.*" & _
    "FROM Table1;"

    'EXECUTE SQL
    cn.Execute SqlDelete
    cn.Execute SqlInsert

    'COMMIT TRANSACTION
    cn.CommitTrans
    bTrans = False

    'CLOSE/RELEASE RESOURCES
    xit:
    If cn.State <> adStateClosed Then cn.Close
    Set cn = Nothing
    Exit Sub
    erh:
    MsgBox Err.Description, vbExclamation, Err.Number
    'rollback if a transaction was begun
    If bTrans = True Then cn.RollbackTrans
    Resume xit
    End Sub
    [/code]
  • hemantsangoihemantsangoi Member Posts: 19
    Thanks for help
Sign In or Register to comment.