Results 1 to 6 of 6
  1. #1
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591

    Export to Text Fails

    The first 3 lines of code work fine. The next 3, actually the last line, reports this error.



    Run-time error 3127
    Cannot Update. Database or object is read only

    The only difference in the two is the path and the filename. I know the path is correct in both cases. The filename on the 2nd file is peculiar, but required by the customer.

    Code:
        
        SSAFilePath = "\\Dmhmrsas01\FIMSData\SSA_Reporting\SSA_Data"
        SSAFilePathName = SSAFilePath & "\EVSPRSN" & Format(Me.RptBegDate, "yyyymmdd") & ".txt"
    
        DoCmd.TransferText acExportFixed, "EVSPRSN_Export", "EVSPRSN", SSAFilePathName, False, ""
    
        SSAFilePath = SSAFilePath & "\SSA_WebDataFormat"
        SSAFilePathName = SSAFilePath & "\PVA." & Format(Me.RptBegDate, "mmddyyyy") & ".PRS.R8452"
    
        DoCmd.TransferText acExportFixed, "EVSPRSN_Export", "EVSPRSN", SSAFilePathName, False, ""

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Check the permissions on the target folder, or see if you can create a file there through your START>WINDOWS EXPLORER you can get that error message if you don't have write permission to the directory.
    Also check to see if you can export to the same file name, but to the directory that doesn't cause problems, just leave the SSAFilePath the same for both and see what happens

    If you do have permission check for the existence of the file before you write to it and delete it if it's there.

  3. #3
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591

    Exclamation

    Interesting, it was the filename. I put a .txt at the end and the code ran normally except for this twist. It converted all the dots "." to hashes "#", e.g. PVA#02012016#PRS#R8452.txt instead of PVA.02012016.PRS.R8452.txt which may be why this step was never automated. Here's my solution which uses file naming functionality, something I hardly ever use, mostly because I don't know how, or didn't.

    Code:
     
        strSSAFilePath = "\\xxxxxxxxxx\FIMSData\SSA_Reporting\SSA_Data"    
        strSSAFilePathName = strSSAFilePath & "\EVSPRSN" & Format(Me.RptBegDate, "yyyymmdd") & ".txt"
        DoCmd.TransferText acExportFixed, "EVSPRSN_Export", "EVSPRSN", strSSAFilePathName, False, ""
        
        strSSAFilePath = strSSAFilePath & "\SSA_Format"
        strSSAFilePathName = strSSAFilePath & "\PVA." & Format(Me.RptBegDate, "mmddyyyy") & ".PRS.R8452.txt"
        DoCmd.TransferText acExportFixed, "EVSPRSN_Export", "EVSPRSN", strSSAFilePathName, False, ""
    
        strFileName = "\PVA#" & Format(Me.RptBegDate, "mmddyyyy") & "#PRS#R8452.txt"
    
        Name strSSAFilePath & strFileName As Left(strSSAFilePathName, Len(strSSAFilePathName) - 4)
            
        MsgBox "Your data has been exported to " & strSSAFilePathName & "."
            
        Application.FollowHyperlink strSSAFilePath
    The surprising part is that it works! Hooray for me.

  4. #4
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    Oh, I should say thanks for nudging me in the right direction. It's always helpful to lay out the problem on paper so to speak.

    Paul

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I don't see you renaming without the hashes after the export which you could do easily enough with filesystemobject commands if that's an issue for you.

  6. #6
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    The hashes are removed by Name(ing) the file to the original intended strSSAFilePathName, which did not include the hashes. Tested and works.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 10-03-2014, 11:19 AM
  2. Access fails export query to XML
    By vinz in forum Access
    Replies: 1
    Last Post: 09-16-2014, 07:31 AM
  3. Access 2010 InfoPath Data Collection Export Fails Due to Date Format
    By raghureddy02 in forum Import/Export Data
    Replies: 3
    Last Post: 01-22-2014, 10:40 AM
  4. Export Whole table to text file with UTF-8
    By Auto in forum Import/Export Data
    Replies: 3
    Last Post: 08-29-2013, 05:09 PM
  5. Formatting text of an export
    By Sarge, USMC in forum Import/Export Data
    Replies: 11
    Last Post: 02-27-2013, 09:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums