I've used the FileCopy method on VBA on an Access 97 form that copies a .MDB file from and to specific locations. i am successful in doing this when the file is unused.
so far so good.
but, when there is someone using the file at the same time as copying it (it's not like i'm moving it away or anything) Access throws up an error saying "Permission Denied". I mean, you can copy a .MDB file normaly from Windows when ther are users on it but not with this function.
Is there anyway around this?
here is the code:
FileCopy "P:\LiveData\Live.mdb", "C:\application\Live.mdb"
07-22-2001, 05:45 PM
Try this, it should work.
Open "c:\temp\tmp.bat" For Output As #1
Print #1, "copy P:\LiveData\Live.mdb C:\application\Live.mdb"
Unfortunately, when i used this code, it broke on this line:
Open "c:\temp\tmp.bat" For Output As #1
saying "Path Not Found".
probabely trying to find tmp.bat which i don't have.
07-23-2001, 08:42 AM
If I'm not mistaken, Open should create the file if it doesn't exist. Does C:\temp exist on your machine?
that did the trick. but, as usual with programming in general and VBA specificaly, once you solve a problem, 2 come up to haunt you after.
this is where it gets complicated. since the file is working under a DOS Prompt, it cannot accept names like "\\Acc Control\c\application\live.mdb".
there is a way around it of course: copy it to a location that will accept the name like "C:\Temp\live.mdb". then, use the filecopy command to copy it to the correct location.
now we're going to get our socks in a real twist: since VBA doesn't wait for the file to be copied, and since it is a big file, the file is not fully there when the FileCopy Command tries to copy it and it comes up with (you guessed it) "Permission Denied".
how do i close the DOS Prompt once the copy is finished without having the user clicking on the "X" (yeah i know, marks the spot and all).
07-23-2001, 05:56 PM
I have encountered the same problems you mentioned when playing with automation projects. After you know the solution, you said to yourself 'pretty simple'. When you have the same experience you would agree. A words of caution: my environment is WinNT. Other problems will need solutions when it is non-winnt.
1/ problem with names like \\..\, spaces between filename, etc.
The problem is solved by enclosing the name in quotes. See one of my earlier answers in reply to the post titled 'populate combo with dir'
2/pressing the 'x': easy as long as you are running winnt workstation, but not with w95 or w98. The solution is: add the command 'exit' in your batch file. Also, you can run the batch file in the background by putting the second argument as vbHide when calling shell.
thx jerry. i am indeed running win 95-98 and these sound like realy good suggestions. could you be so kind as to write the code down for me for these 2 functions?
07-26-2001, 12:31 AM
Sorry to come back here so late. I've been busy.
IF I am not mistaken, you'd said the problem with "permission denied" has been solved.
For your new problem no. 1 associated with \\, or filenames with separated by spaces etc. the solution is to enclose the filename in quotes. This works for both winNT, win95 or win98
For your problem No. 2, the solution has appeared in this forum so many times that I'd rather not to do it here again. Please refer to the post by JDT about shellAndWait. JDT had already been very kind to write the source codes in one of his post. I do not want to break the copyright rule. Do a search (last month of vb help forum) for a user vb function named ShellAndClose(..) by JDT. The function can run a dos command file and close the dos windows started under window 95, after the dos command file runs to an end.
I hope you'd work out very soon.
07-26-2001, 01:05 AM
One more remark:
Someone had suggested to put the @cls instruction as the last statement in the batch file. This will close the dos windows under Win95. But somehow later we had JDT's putting up the API call to terminate the DOS windows. I couldn't comment which solution we should use in different circumstances. To me both work well.
If you still have problem with the search function of this forum, look for posts under the subject "VB6 and .bat files" in May this year.
WOW that code is amazing. and it works.
the only thing i have a problem with now is the file name on quotes.
which quote marks do i use? these ' ? or " ? the file path i am using is
where do i put the quotes?
please help. this would be the last step needed and then everything will click into place.
07-26-2001, 07:29 PM
ok, last one. These codes should work unless there are typo errors.
Private Sub copyMDB() 'note this is for Win95 only
Dim src As String, dst as string, tmp As String
src = "\\direct link\c\application\live.mdb"
dst = "C:\application\Live.mdb"
tmp = "copy " + chr$(34) + src + chr$(34) + " " + chr$(34) + dst + chr$(34)
rem msgbox tmp
open "c:\tmp.bat" for output as #1
print #1, tmp
print #1, "@cls"
call shell("c:\tmp.bat", vbHide)
it works. the lot. thank you Jerry and thank you everyone that contributed.
your advice was priceless