Backing up an MSAccess Database

[PierreD]

I have this data acquisition program, writing to an Access database every minute. The link between the two is through an ODBC User data source.

The database gets bigger and bigger.

I would like to copy the database to a dir named after the year and month... Ex: .../2007_march AND THEN clear the actual database to make room for the newer data.

My first problem comes from my poor very English vocabulary. (Now you know, I'm French)

I can't even seem to be able to Google the darn thing to find help in solving how to do this.

Can anybody suggest what words I should use to Google this?

What I want is:

Have the data in a database in a directory created and named after the Date value of the Data inside it.

I thought of writing a very small VB app. that could run in the background and while monitoring the time, would trigger a backup copy and after proof of creation and integrity, would erase whatever is in the "main" base. (That is why I did not post this in the Access Forum)

Thanks in advance.

[debasisdas]

refere microsoft scripting runtime from Project--> References

use this to create a directory

[code=vb]MkDir "C:\" & Day(Now) & MonthName(Month (Now)) & Year(Now)[/code]


to copy the file
[code=vb]
Dim FSO As FileSystemObjec t
Set FSO = CreateObject("S cripting.FileSy stemObject")
FSO.CopyFile "E:\DEBASIS\VB\ DATA.TXT", "E:\DEBASIS\VB\ DATA1.TXT"
[/code]
then delete the desired data from the database table.

[debasisdas]

Try this sample code for backup and restore of Access Database.
Please remove the extra code including textboxes.
[code=vb]
Sub MBackup()

Set FSO = CreateObject("S cripting.FileSy stemObject")
On Error GoTo Errors
If OptBackup Then
TxtRemarks = "Backup Started at " & Time
TxtRemarks = TxtRemarks & vbCrLf & "Closing Connection ..."
GCnnGeneral.Clo se
TxtRemarks = TxtRemarks & vbCrLf & "Checking Destination ..."
If GFileExists(Txt Destination) Then
Kill TxtDestination
End If

TxtRemarks = TxtRemarks & vbCrLf & "Compacting Source ..."
DBEngine.Compac tDatabase TxtSource, TxtDestination, , , ";pwd=MDrea ms"
TxtRemarks = TxtRemarks & vbCrLf & "Destinatio n Created ..."
TxtRemarks = TxtRemarks & vbCrLf & "Connecting Database ..."
With GCnnGeneral
.Provider = "Microsoft.Jet. OLEDB.4.0"
.Properties("Je t OLEDB:Database Password") = "MDreams"
.Mode = adModeReadWrite
.Open App.Path & "\" & Trim(GFileName) & ".MDB"
End With
'GFileName = Trim(LstDatabas e.Text)
TxtRemarks = TxtRemarks & vbCrLf & "Backup Created at " & Time
MsgBox "Backup Created."
TxtSource = GEmptyStr
TxtDestination = GEmptyStr
ElseIf OptRestore Then
'GCnnAccts.Clos e
TxtRemarks = "Restoring Data Started at " & Time
GCnnGeneral.Clo se
TxtRemarks = TxtRemarks & vbCrLf & "Connection Closed ..."
Kill TxtDestination
TxtRemarks = TxtRemarks & vbCrLf & "Destinatio n Checked ..."
Call FSO.CopyFile(Tx tSource, TxtDestination, True)
TxtRemarks = TxtRemarks & vbCrLf & "Data Restored ..."
With GCnnGeneral
.Provider = "Microsoft.Jet. OLEDB.4.0"
.Properties("Je t OLEDB:Database Password") = "MDreams"
.Mode = adModeReadWrite
.Open App.Path & "\" & Trim(GFileName) & ".MDB"
End With
TxtRemarks = TxtRemarks & vbCrLf & "Connection Complete ..."
TxtRemarks = TxtRemarks & vbCrLf & "Data Restored at " & Time
MsgBox "Data Restored."
End If

Exit Sub
Errors:
MsgBox "[ErrNo.: " & Err.Number & "] " & Err.Description
End Sub
[/code]

[PierreD]

Thank you debasisdas,

But, I don't seem to be reading your suggested code correctly.

1. Say the database is named DATA.mdb, and has 2 Tables in which we write values for every 30 seconds but we write them every 5 minutes.

2. The db could have some data from last July up to now

3. I want to have the data from July written or copied or moved to a db named Data.mdb in the sub-directory named July2007.
------------------------------------
What is the best way to do this?
------------------------------------
Should I have a VB6 application running that monitors the db?

Could I have code/macros written to the db that would trigger these "export" although the db is not opened and I will not have MSAccess installed in this PC?

Should I trigger a VB application say every hours doing this export/move task once?
------------------------------------
The code you have written, where must it be placed?

[debasisdas]

The code will take backup of the entire database into another file. After that you can keep the data that you want and delete tnhe rest. The process can be repeated at a particulat rime interval say once a month / week .