Copying Open Database?

[AlexCuse]

Hello All,

I am currently trying to get an Access database to save a copy of itself with the date appended to the filename after the daily processing that it is used for is complete. I am, of course, running into issues with not being able to access the file that I want to copy. I have a few ideas at this point but they are rather vague. Before I really dive into this I wanted to see if anyone else had dealt with this problem before, and if so what their approach was.

Thanks in advance for your help!

Alex



It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[Remou]

How about:

strFile = CurrentProject.FullName
strNew = Mid(strFile, 1, InStrRev(strFile, ".")) & "bak"
Set fs = CreateObject("scripting.filesystemobject")
fs.CopyFile strFile, strNew, False

 

[AlexCuse]

Remou-

I was going to use FSO, but this is much cleaner method. I will test and let you know how it goes.

Thanks,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[AlexCuse]

* much cleaner method of using FSO than I had in mind.

I have implemented your suggestion and it worked like a charm. The only problem is that the backup db has the open (non compacted) db's file size until I open and close it, but what is a few megs between friends? Next step is to automatically put the db into a zip archive anyway, so I don't think it will make a difference.

Thanks a lot Remou,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[JerryKlmns]

Alex


Be aware that zipping an encrypted mdb has no gain in size! So if you compact and repair using code (JRO engine) do not encrypt.

 

[Remou]

A few more notes, seeing you intend to zip.

Sub Zip_File()
'Modified from
'[URL unfurl="true"]http://www.rondebruin.nl/windowsxpzip.htm[/URL]
'Zip file or files with the default [b]Windows XP zip program[/b] (VBA)
'Ron de Bruin (last update 24 Sept 2005)

Dim strDate As String, strPath As String
Dim oApp As Object
Dim strFile, strZipName
 
    strPath = CurrentProject.Path & "\"
 
    strDate = Format(Now, " dd-mmm-yy h-mm-ss")
    strZipName = strPath & "MDB " & strDate & ".zip"
 
    strFile = CurrentProject.FullName
 
    If strFile = "" Then
        'do nothing
    Else
        'Create empty Zip File
        NewZip (strZipName)
 
        Set oApp = CreateObject("Shell.Application")
 
        oApp.NameSpace(strZipName).CopyHere strFile
        
        MsgBox "You find the zipfile here: " & strZipName
        Set oApp = Nothing
    End If
End Sub

Sub NewZip(sPath)
'Create empty Zip File
    Dim oFSO, arrHex, sBin, i, Zip
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    arrHex = Array(80, 75, 5, 6, 0, 0, 0, _
                   0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
    For i = 0 To UBound(arrHex)
        sBin = sBin & Chr(arrHex(i))
    Next
    With oFSO.CreateTextFile(sPath, True)
        .Write sBin
        .Close
    End With
End Sub

 

[AlexCuse]

That is pretty cool!

I am currently using the winzip command line utility, but I will definitely archive this one to refer to if the need arises.

Jerry- The archived .mdb's are not encrypted, so I don't think I will have a problem. They are not really very useful to have because they are only front ends for a SQL Server database. Why they were saved in the first place is beyond me, but once a process is in place I'm sure you know what a pain it is to get it changed. I just don't have time for it right now, but I've at least saved the user from making a manual copy.... (thanks to you guys' help!)

Thanks again guys!

ALex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.