Question Create backup file with vba

[Milad Roohi]

Hi everyone, have nice day
I want to export my tables data in a backup file and use it later.
how can i do this with vba codes??

Please note that i don't want use copyfile method in vba but i want to create simple backup from data in my tables.

Help me pls, thanks

 

[Isskint]

Have a look at the DoCmd.TransferSpreadsheet method.

Just create a sub routine to be called before closing the database (on prompt from a command button OR On_Close event of a form OR On_Timer interval event etc). Loop through each table in your DB (excluding the SYS_xxxx tables), create a filename and voila!

Can I ask why you do not want the copyFile method?

 

[Milad Roohi]

Have a look at the DoCmd.TransferSpreadsheet method.

Just create a sub routine to be called before closing the database (on prompt from a command button OR On_Close event of a form OR On_Timer interval event etc). Loop through each table in your DB (excluding the SYS_xxxx tables), create a filename and voila!

Can I ask why you do not want the copyFile method?

Thanks...
because that method just create a copy of my file and I can't copy my tables data from it because as i mentioned i can't access to my tables.

can you explain a little more about DoCmd.TransferSpreadsheet ??

 

[Isskint]

You can get a full explanation of this at https://msdn.microsoft.com/en-us/library/office/ff844793.aspx

In short, TransferSpreadsheet has 4 parameters for exporting
TransferType - acExport
SpreadsheetType - variable to identify the version of Excel
TableName - the table or query to export
FileName - file name and path of the spreadsheet