Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Macro Permission denied

Posted on 2008-11-07
3
Medium Priority
?
516 Views
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
RS.MoveFirst
Else
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
Else
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
Else
OutputLine = RS.Fields(I).Value
End If
Next I
Print #FileNum, OutputLine
Debug.Print OutputLine
OutputLine = ""
RS.MoveNext
Loop
Close #FileNum
Set RS = Nothing
Set DB = Nothing
End Function

Open in new window

0
Comment
Question by:Buckeye4Life
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
therealmongoose earned 1000 total points
ID: 22906313
Your code looks ok, I'd suggest you try to changing

Open FileNameAndPath For Output Access Write Lock Write As FileNum

to

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....

0
 

Author Closing Comment

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

Expert Comment

by:therealmongoose
ID: 22912881
Lol - no probs, glad it helped - (bosses - always trying to be helpful... tssk!!)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

810 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