Macro Permission denied

Posted on 2008-11-07
Last Modified: 2012-05-05
I'm trying to run a function using a Macro. When I try to run the macro, I receive a Run-time error "70" Permission denied.

The error occurs at line :Open FileNameAndPath For Output Access Write Lock Write As FileNum

The Marco is basically is run code:

Function Name: Export_Tab_Delimited ( "tblAGRSalesProjection", "F:\Supply Chain\Team Folders\Metrics\AGR Sales Projection\tblAGRSalesProjection.txt")

Option Compare Database

Option Explicit

Public Function Export_Tab_Delimited(TableOrQueryName As String, FileNameAndPath As String)

'This uses the DAO database reference. IN the VB window (Ctrl+G) go to _

Tools --> References and select the Microsoft DAO 3.6 Library


Dim DB As DAO.Database

Dim RS As DAO.Recordset

Dim I As Integer

Dim FileNum As Integer

'Dim FileNameAndPath As String

Dim OutputLine As String

FileNum = FreeFile()

'FileNameAndPath = "F:\Supply Chain\Team Folders\Metrics\AGR Sales Projection\tblAGRSalesProjection1.txt"

Set DB = CurrentDb()                              'Use the current database

Set RS = DB.OpenRecordset("qryAGRSalesProjection")      'actually open the recordset

If RS.EOF = False Then



MsgBox "No Data", vbExclamation, "Exiting Fuction"

Set RS = Nothing

Set DB = Nothing

Exit Function

End If

'Open the file for output

Open FileNameAndPath For Output Access Write Lock Write As FileNum

I = 0

OutputLine = ""

'Output the field names as a header

For I = 0 To RS.Fields.Count - 1

If I > 0 Then

OutputLine = OutputLine & Chr(9) & RS.Fields(I).Name


OutputLine = RS.Fields(I).Name

End If

Next I

Print #FileNum, OutputLine

Debug.Print OutputLine

I = 0

OutputLine = ""

'start outputting the data

Do Until RS.EOF

For I = 0 To RS.Fields.Count - 1

If I > 0 Then

OutputLine = OutputLine & Chr(9) & RS.Fields(I).Value


OutputLine = RS.Fields(I).Value

End If

Next I

Print #FileNum, OutputLine

Debug.Print OutputLine

OutputLine = ""



Close #FileNum

Set RS = Nothing

Set DB = Nothing

End Function

Open in new window

Question by:Buckeye4Life
    LVL 10

    Accepted Solution

    Your code looks ok, I'd suggest you try to changing

    Open FileNameAndPath For Output Access Write Lock Write As FileNum


    Open FileNameAndPath For append As FileNum

    If this doesn't work it may either be a permissions error on you server (try a temp file on the c drive instead to see if the code works) If it is permissions you need to check write permissions for your user name.

    The other possibility is that your original code locks the file for output to - if you've crashed a procedure when you have been testing, you may still havea locked file on the server - try calling

     ( "tblAGRSalesProjection", "F:\Supply Chain\Team Folders\Metrics\AGR Sales Projection\tblAGRSalesProjection_Test.txt")

    "F:\Supply Chain\Team Folders\Metrics\AGR Sales Projection\tblAGRSalesProjection.txt"

    Is probably locked which is why your code isn't working....


    Author Closing Comment

    I found out that my boss had the txt file open on the server. Thanks for you're input, it was helpful.
    LVL 10

    Expert Comment

    Lol - no probs, glad it helped - (bosses - always trying to be helpful... tssk!!)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now