Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / May 2008

Tip: Looking for answers? Try searching our database.

FileCopy Working 80% of Time

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Sash - 13 May 2008 14:40 GMT
I've created a database that reformats client files into a universal import
format.  Basically, the user selects a file and based on the client I run a
few queries to parse names and then run the file through a program to formats
the data.  At the end, I do a file copy to archive the file and then kill to
delete the original from the import directory.  This works 80% of the time,
but I get "Run-time error 70" Permission Denied the other 10%.  Oddly enough,
when I set breakpoints it never errors.  

One thing I did notice is that this only seems to be occuring for the files
that I run through Monarch via a batch program.  Again, the odd thing is that
sometimes it even works for these files.  Is there a way to make sure the
file is closed before I try to kill it?  Below is the code that I'm using and
I also tried to use Name, also below.

Of course, I need this fixed by Thursday so any suggestions or help would be
greatly appreciated.

Regards,
Sash

***NewNameFSN and strOrigFile are delared earlier in program****

'Move File to Archive
   Dim strFile As String
   Dim strMove As String
   strFile = "u:\interfaces\import\FSHN.txt"
   strMove = "u:\interfaces\import\archive\FSH\" & Format(Time, "HHMMSS") &
"_" & strOrigFile
   Name strFile As strMove
   Kill "u:\interfaces\import\FSHN.txt"
   MsgBox "FSH moved to Archive."

** Also tried ***
FileCopy NewNameFSN, "u:\interfaces\import\archive\FSH\" & Format(Time,
"HHMMSS") & "_" & strOrigFile
Kill "u:\interfaces\import\FSHN.txt"
Tom van Stiphout - 13 May 2008 15:30 GMT
Are you saying Monarch may still have the files open? That's a recipe
for disaster.
You may want to try to get exclusive access to the file before
proceeding. Try the Open statement with the Lock Read Write value for
the lock argument. If it fails, some other process has the file open.

-Tom.

>I've created a database that reformats client files into a universal import
>format.  Basically, the user selects a file and based on the client I run a
[quoted text clipped - 32 lines]
>"HHMMSS") & "_" & strOrigFile
>Kill "u:\interfaces\import\FSHN.txt"
Sash - 13 May 2008 15:40 GMT
Monarch is closed and done with the file, but it was just a guess.  Can you
tell me what is the syntax for opening with read write value.

> Are you saying Monarch may still have the files open? That's a recipe
> for disaster.
[quoted text clipped - 40 lines]
> >"HHMMSS") & "_" & strOrigFile
> >Kill "u:\interfaces\import\FSHN.txt"
Sash - 13 May 2008 18:43 GMT
Me again.  I moved the MsgBox to the line between FileCopy and Kill and it
seems to work.  Does this make sense??

> Monarch is closed and done with the file, but it was just a guess.  Can you
> tell me what is the syntax for opening with read write value.
[quoted text clipped - 43 lines]
> > >"HHMMSS") & "_" & strOrigFile
> > >Kill "u:\interfaces\import\FSHN.txt"
Douglas J. Steele - 13 May 2008 19:11 GMT
Since MsgBox requires the user to respond, you're introducing a delay which
may be enough for the operating system to complete whatever it's doing. What
I don't understand, though, is why your code works at all! The Name
statement renames a file. That means file "u:\interfaces\import\FSHN.txt"
should no longer exist: you're renamed it! That means the Kill statement
should raise an error.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Me again.  I moved the MsgBox to the line between FileCopy and Kill and it
> seems to work.  Does this make sense??
[quoted text clipped - 60 lines]
>> > >"HHMMSS") & "_" & strOrigFile
>> > >Kill "u:\interfaces\import\FSHN.txt"
Sash - 13 May 2008 19:23 GMT
Thanks Doug.  Sorry about that I wasn't clear in my original post.  I was
trying both FileCopy in which I then Kill the file, but when this wasn't
working correctly I tried Name instead.  Which would you recommend to use in
my situation??  Name?

> Since MsgBox requires the user to respond, you're introducing a delay which
> may be enough for the operating system to complete whatever it's doing. What
[quoted text clipped - 67 lines]
> >> > >"HHMMSS") & "_" & strOrigFile
> >> > >Kill "u:\interfaces\import\FSHN.txt"
Douglas J. Steele - 13 May 2008 20:34 GMT
Since you're trying to rename a file, using the Name statement would appear
to be the appropriate way.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks Doug.  Sorry about that I wasn't clear in my original post.  I was
> trying both FileCopy in which I then Kill the file, but when this wasn't
[quoted text clipped - 89 lines]
>> >> > >"HHMMSS") & "_" & strOrigFile
>> >> > >Kill "u:\interfaces\import\FSHN.txt"
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.