backup table monthly 1

[budich]

Hi all,

I have plan to make backup process of my accounting program. Do you know how to backup the journal table in separate folder in hardisk from the code.
For instance, I have 12 folder says january, febuary, ... until Desember. So I plan after I process 1 month financial report I can copy to the current month folder.
Thank you

 

[TheInsider]

Hi,
Use the TransferDatabase method to copy the table(s) to a shell/empty database which resides in one of your folders. Am I on the right track?

Rob Marriott
rob@career-connections.net

 

[budich]

Thanks a lot Rob,
So I have to make code to export to a database in the specific directory, but it is still cannot work. My code is like below :

DoCmd.TransferDatabase acexport, "access", "c:\january\", acTable, "journal", "journal", False

Do you see any mistake for this code.

My next questions are :
- how to put some criteria variable so it link with this code for month (change c:\january\ to c:\february\ etc).

Thank you for your help.

 

[TheInsider]

Hi,
Here is the function to create a shell database and export the table:

'Sample calling of procedure
Sub Test()
Call Backup(Month(Date), Year(Date))
End Sub

'Actual procedure
Public Sub Backup(Month As Integer, Year As String)
Dim wsp As Workspace, dbs As Database, MonthName As String

Select Case Month
Case 1
MonthName = "January"
Case 2
MonthName = "February"
Case 3
MonthName = "March"
Case 4
MonthName = "April"
Case 5
MonthName = "May"
Case 6
MonthName = "June"
Case 7
MonthName = "July"
Case 8
MonthName = "August"
Case 9
MonthName = "September"
Case 10
MonthName = "October"
Case 11
MonthName = "Novemeber"
Case 12
MonthName = "December"
End Select

Set wsp = DBEngine.Workspaces(0)
Set dbs = wsp.CreateDatabase("C:\" & MonthName & "\" & MonthName & Year _
& ".mdb", dbLangGeneral)
Set dbs = Nothing

DoCmd.TransferDatabase acExport, "Microsoft Access", _
"C:\" & MonthName & Year & ".mdb", acTable, "journal", _
"journal"
End Sub

Rob Marriott
rob@career-connections.net

 

[TheInsider]

Oops! The last bit should have been like this to account for the directory:

DoCmd.TransferDatabase acExport, "Microsoft Access", _
"C:\" & MonthName & "\" & MonthName & Year & ".mdb", acTable, "journal", _
"journal"

Also, please note that I assumed the directories already exist. However, you could easily add to this procedure to create them with VBA code, if you wanted to.

Rob Marriott
rob@career-connections.net

 

[budich]

Thanks Rob,

your help is very clear and helpfull
It can solve my problem
Thank you,
regards
Budi