CodeItBetter Programming Another VB Programming Blog

'Database - How to export data from the current database to a back-up database using Access Application
Option Explicit
 
Dim appAccess As Access.Application
Dim dat As String
 
Sub BackUpDatabase()
    Set appAccess = New Access.Application
    dat = App.Path & "\BackUpDb.mdb"
 
    'Open and set the path for the current database
    appAccess.OpenCurrentDatabase App.Path & "\CurrentDb.mdb"
 
    'Creates a new table in the current database
    appAccess.DoCmd.RunSQL ("CREATE TABLE new_table (column1_name varchar, column2_name varchar)")
 
    'Populate the new table with the data selected from the old table in the same database
    appAccess.DoCmd.RunSQL ("INSERT INTO new_table SELECT column1_name, column2_name FROM old_table")
 
    'Export the new table from current database to Arhi table in the back-up database
    appAccess.DoCmd.TransferDatabase acExport, "Microsoft Access", dat, acTable, "new_table", "Test"
 
    MsgBox "Database created !"
 
    'Close the current database
    appAccess.CloseCurrentDatabase
 
    appAccess.Quit
End Sub