Copying a non-shared back-end mdb while opened

[GraeagleBill]

Nope! Neither "/Y" or "/y" will copy without my having to press the "f" key. I still get the prompt to specify "f" for file, "d" for directory. (I guess Windows command code doesn't know how to parse strings?)

[GraeagleBill]

Nope! Neither "/Y" or "/y" make any difference. I still have to enter "f".

Ooops! hadn't noticed thread had increased to 3 pages.

[Micron]

Will look later to see what I might have/can find about this; gotta run now

[GraeagleBill]

I just tested a file copy using Microsoft's VBA FileSystemObject. So far it seems to do the job. One has to add Microsoft Scripting Runtime to their Reference, not a big deal. I'll integrate that scheme into my app's code and post back with the results........... hopefully to close out this caper.

[isladogs]

I just tested a file copy using Microsoft's VBA FileSystemObject. So far it seems to do the job. One has to add Microsoft Scripting Runtime to their Reference, not a big deal. I'll integrate that scheme into my app's code and post back with the results........... hopefully to close out this caper.

Not true. The backup code I posted in #20 uses FSO and does NOT require any additional references

[GraeagleBill]

A mystery to me, as I had looked at your code and concluded the same thing. Yet, when I attempted to compile my app it became obvious that I needed it.

[GraeagleBill]

HALLELUJAH! Between Micron's & Colin's suggestions I finally got a solution. The snippet of code below gathers what is needed and uses FSO to do the copy. The error handler (not shown) takes care of runtime error 58 if the target file already exists, (XCOPY has a switch for that). You'll notice I take no chances pertaining to an un-reported FSO failure by checking the file lengths after the copy.

Dim FSO As Scripting.FileSystemObject  'Requires a reference to Obj Lib "Microsoft Scripting Runtime.

'*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
'                C R E A T E  A R C H I V E  F I L E  N A M E
'*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

strArchName = Replace(curBackEnd, "\eBud\", "\eBud\DB-Archives\")     'Change the folder

If InStr(strArchName, "(Personal)") > 0 Then
    strArchName = Replace(strArchName, "eBudData(Personal)", "Personal-Data")
Else
    strArchName = Replace(strArchName, "eBudData(Business)", "Business-Data")
End If

strFirstDate = Replace(curFirstDate, "/", "-")
strEndDate = Replace(curEndDate, "/", "-")
strArchName = Replace(strArchName, ".mdb", "(" & strFirstDate & " To " & strEndDate & ").mdb")
strArchive = curBackEnd & ";" & strArchName

Set FSO = New Scripting.FileSystemObject

Call FSO.CopyFile(curBackEnd, strArchName, False)

Set FSO = Nothing

If FileLen(curBackEnd) <> FileLen(strArchName) Then
    msgbox "Any additional archiving options are being" & vbnewline & _
           "suppressed, as the size of the DB copy DOES" & vbnewline & _
           "NOT equal that of the original."

[isladogs]

Praise be!

Now if you compare your code and mine, its obvious why you need the scripting reference.
You're using early binding whereas my code uses late binding.

Your code

Dim FSO As Scripting.FileSystemObject  'Requires a reference to Obj Lib "Microsoft Scripting Runtime.
...
Set FSO = New Scripting.FileSystemObject

My code:

Dim fso As Object 'no reference needed
...
Set fso = CreateObject("Scripting.FileSystemObject")

Swop the 2 lines and you no longer need the reference (at least not for this code)

[GraeagleBill]

Swop the 2 lines and you no longer need the reference

Done! As usual Colin, you're among the best.

Thanks,
Bill

[isladogs]

Thanks for your kind words .... Not sure I deserve them though!

[Micron]

Sorry I couldn't get back sooner. Been gone out all day and replaced my router when I got home, so have been off line most of the day. Late or not, I try to keep my promises, so what I used is below. I can't swear it will copy an open db because I'm retired a few years now and don't exactly recall the scenario for its use. What you might find useful is the line that gets around the issue of users having different drive letters for the same drive/volume label where the db is accessed from: set MyDir=%~dp0
The only caveat is that for as written, the bat file has to be in the same location as the db.

rem to copy OSCRbe to backup database
rem must be run from this folder

cls
@echo off

rem next line sets directory to current location, 
rem but uses the assigned drive letter (e.g. F, L, etc.)

set MyDir=%~dp0

rem now use that value by reference
cd %MyDir%

:CopyFiles
rem use quotes around paths for cases where uniformed used spaces in path

copy "%MyDir%OSCRbe.mdb" "%MyDir%OSCRbe_Bak.mdb" /y

:quit
exit

[moke123]

Late to the game but I figured I'd add this link to a procedure I have used in the past to Backup and compact a db.

http://accessmvp.com/thedbguy/code.php?title=backup

[isladogs]

Hi Moke
Just to say Brent's code in that link is very similar to the backup and compact code I provided in post #20.
My version can also handle password protected files