Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    fail to rename file (97)

    In this Access 97 application, customers send updated information via email as Excel files. Each file is stored in a "received" folder. The Access application links to each file then opens a form for additional processing. Once the processing is completed, the Excel file is moved from the "received" folder into the "processed" folder.

    It's the last step causing grief -- Access fails with Err#75 - file/path access error when trying to rename the file.

    If I comment-out the OpenForm line, then the Name command works properly. Also, if I put a breakpoint on the Name command, and single-step through it, it works properly. It's as if the OpenForm line keeps a lock on the file, preventing it from being moved, but only when running "properly". I've tried deleting the linked table ("XLTankUpdates") before renaming the file, without success. I've tried individual FileCopy and Kill commands instead of the Name command, with the same results.

    Without the acDialog parameter, processing continues without stopping at the OpenForm command, so the Name command fails under those conditions, which is understandable.


    On Error Resume Next
    DoCmd.DeleteObject acTable, "XLTankUpdates"
    DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel97, "XLTankUpdates", sFileFullName, , sScratch
    DoCmd.OpenForm "frmImportCustomerExcelTankUpdates", , , , , acDialog
    ' move the file into the Processed folder -- overwrite any previously-existing of the same name
    Kill sPath & "Processed" & sFileName
    Err.Clear
    Name sFileFullName As sPath & "Processed" & sFileName
    If Err.Number <> 0 Then
    MsgBox "Error occured while moving " & sFileName & " into the Processed folder. Move the file manually." & vbCrLf & Err.Description
    End If


    Can anyone suggest a fix?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fail to rename file (97)

    It's installed on a local drive.

    I will try your suggestions about DoEvents and the Timer. Thanks for the ideas.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: fail to rename file (97)

    The code works without problems in Access 2002. Somehow I doubt that it's an Access 97 problem. Is sPath a folder on a local hard disk or on a network? Some networks are slow to release file locks.

    Does it help if you inserts a DoEvents instruction before trying to rename the file?

    If you run this code from a form, you might try to get around the timing problem as follows:
    - Create an On Timer event for the form that renames the file, then sets the Timer Interval of the form to 0 to deactivate the form timer.
    - Remove the instruction to rename the file from the code you posted; instead, set the Timer Interval to 500 (milliseconds) to activate the form timer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •