Solved Create Access Table Data Backup in Excel

[JithuAccess]

Hello,

Can we create backup of Access Tables data in to an Excel file by using VBA code?

Thanks

 

[theDBguy]

Does it have to be Excel?

 

[JithuAccess]

Does it have to be Excel?

Yes. Because just in case if Access is not working and we are not able to access any of the data in the tables, we must have a Backup in Excel. Currently I am taking daily Backup of my Access database. But if Access is not working we would not able to get any of the data in the Access database.

Thanks

 

[theDBguy]

Yes. Because just in case if Access is not working and we are not able to access any of the data in the tables, we must have a Backup in Excel. Currently I am taking daily Backup of my Access database. But if Access is not working we would not able to get any of the data in the Access database.

Thanks

In that case, have you tried using the TransferSpreadsheet method?

 

[JithuAccess]

In that case, have you tried using the TransferSpreadsheet method?

No I will try. Let me google or if you have any links that would be highly appreciate

We have 5 main tables and what I want is while taking the Backup daily (I am taking daily Backup in a button click event) I want the data of these 5 tables to paste in to an Excel sheet with 5 different Tabs or in 5 different Excel spreadsheets.

I am not so sure whether I can do this with VBA code.

Thank You so much

 

[theDBguy]

No I will try. Let me google or if you have any links that would be highly appreciate

We have 5 main tables and what I want is while taking the Backup daily (I am taking daily Backup in a button click event) I want the data of these 5 tables to paste in to an Excel sheet with 5 different Tabs or in 5 different Excel spreadsheets.

I am not so sure whether I can do this with VBA code.

Thank You so much

Here are two links for you...

DoCmd.TransferSpreadsheet method (Access)

Office VBA reference topic

docs.microsoft.com

Access VBA DoCmd.TransferSpreadSheet Method

This Access tutorial explains how to use DoCmd.TransferSpreadSheet Method to export and import files.

access-excel.tips

Hope they help...

 

[JithuAccess]

Thank you so much.

But I am getting

This is my code

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "[tblMaster Table]", "\\ABC\shared\ABCD\ABCDE\ABCDEF\Database Backup\Test.xlsx", True

Thanks

 

[JithuAccess]

Here are two links for you...

DoCmd.TransferSpreadsheet method (Access)

Office VBA reference topic

docs.microsoft.com

Access VBA DoCmd.TransferSpreadSheet Method

This Access tutorial explains how to use DoCmd.TransferSpreadSheet Method to export and import files.

access-excel.tips

Hope they help...

Sorry. This is what happened when I blindly copy paste the code without knowing what does it means

Changed my code

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tblMaster Table", "\\ABC\shared\ABCD\ABCDE\ABCDEF\Database Backup\Test.xlsx", True

and it worked

Thank you so much

 

[theDBguy]

Sorry. This is what happened when I blindly copy paste the code without knowing what does it means

Changed my code

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tblMaster Table", "\\ABC\shared\ABCD\ABCDE\ABCDEF\Database Backup\Test.xlsx", True

and it worked

Thank you so much

Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[JithuAccess]

Hello,

I have created a spreadsheet and now all my data from 5 main tables are saving in it.

My Backup of database are saving like this on a button click:

Is there any way I could apply this same method for Excel?

What I mean to say is, when a user click on a command button, I want to create a Backup of Access database and also an Excel spreadsheet. So this should be something like:

Is that possible?

Thanks

 

[Gasman]

Use the same logic you use for the access backup.
They are just filenames after all?
I would expect you would need to prefix the Excel name with the table name?

 

[JithuAccess]

Use the same logic you use for the access backup.
They are just filenames after all?
I would expect you would need to prefix the Excel name with the table name?

Thanks a lot it Worked