Transfer text error

[mikew71]

I'm running the following code as part of a greater function:

DoCmd.TransferText acExportDelim, StOutputSpec, StOutputTable, Stfilepath & StOutputFile, -1

When I get to this line I get the following error;

"Cannot Update. Database or Object is Read Only"

This makes no sense as thee is no existing file of this name in the directory.

 

[Thenolos]

Might be an obvious suggestion... but is the folder checked as read-only? Could you post the full code segment?

 

[mikew71]

This is the full code, everything runs until the export stage. The destination directory isn't read only as I could export the file manually from Access.

Cheers



Function RunInteractionsBaseData()

DoCmd.SetWarnings True

Dim Stfilepath As String
Dim StCostFile As String
Dim StTableSpec As String
Dim StOutputTable As String
Dim StOutputSpec As String
Dim StOutputFile As String

Stfilepath = "xxx\ffff\eeee"
StImportFile = "xxxx.txt"
StDestTable = "Partial Interactions Calculations"
StOutputTable = "Partial Interactions Calculations Combined"
StTableSpec = "Partial Interactions Calculations Specification"
StOutputFile = "Partial Interactions Calculations_Combined"
StOutputSpec = "Partial Interactions Calculations_Combined Specification"

On Error GoTo ErrorManager


SysCmd acSysCmdSetStatus, "Deleting Existing Interactions Costs..."


DoCmd.RunSQL "DELETE [Partial Interactions Calculations].*" & _
"FROM [Partial Interactions Calculations];"


SysCmd acSysCmdClearStatus
SysCmd acSysCmdSetStatus, "Importing New Interactions Data..."


DoCmd.TransferText acImportDelim, StTableSpec, StDestTable, Stfilepath & StImportFile, True

SysCmd acSysCmdClearStatus

SysCmd acSysCmdSetStatus, "Calculating Interactions Data..."

DoCmd.RunSQL "SELECT [Partial Interactions Calculations].[Parent Client ID], [Partial Interactions Calculations].[Parent level Client], [Partial Interactions Calculations].[Activity Type] AS [Activity Type Name], [Partial Interactions Calculations].[UBS Attendee Gpn], [Partial Interactions Calculations].[UBS Attendee Full Name], Sum([Partial Interactions Calculations].[Partial Interaction]) AS [No Of Activity] INTO [Partial Interactions Calculations Combined]" & _
"FROM [Partial Interactions Calculations]" & _
"GROUP BY [Partial Interactions Calculations].[Parent Client ID], [Partial Interactions Calculations].[Parent level Client], [Partial Interactions Calculations].[Activity Type], [Partial Interactions Calculations].[UBS Attendee Gpn], [Partial Interactions Calculations].[UBS Attendee Full Name];"

SysCmd acSysCmdClearStatus

SysCmd acSysCmdSetStatus, "Exporting Interactions Data..."

DoCmd.TransferText acExportDelim, StOutputSpec, StOutputTable, Stfilepath & StOutputFile, -1

SysCmd acSysCmdClearStatus

MsgBox "Partial Interactions Calculations and Export Complete" & _
"Refresh Links In Business Objects"
SysCmd acSysCmdClearStatus
Exit Function

ErrorManager:
MsgBox Error & ". Update was not completed!", , "Error in process.."
SysCmd acSysCmdRemoveMeter
SysCmd acSysCmdClearStatus
Exit Function


End Function

 

[Thenolos]

Hmmm... Is your actual string name for the file location longer than 64 characters? Longer file extensions have been known to cause this error... try testing it by sending it to a directory with a shorter file extension?

 

[mikew71]

Tried your suggestion with no effect, there are no obvoius errors or conflicts in the code that I can see so I'm at a comlpete loss as to why this is happening.

Could the export spec have something to do with this?

 

[jebry]

Hi!

Are you absolutely sure that the error occurs on that line? I see that in your stFilePath you don't have a / at the end which would normally be there (assuming only the path is in this variable) but I don't see how that would produce this particular error even if it is a mistake. I would think you would get a folder not found error or something like that instead.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[mikew71]

Thanks, that file path is only there as an example, on the actual code it's correct.

 

[Thenolos]

You said you've exported it manually from access. If you used the same specs to export that way then I don't see how that could be an issue. The only other thing I could think of is some kind of relational issue in StOutputTable.

Is that table clear when you run this code?

DoCmd.RunSQL "SELECT [Partial Interactions Calculations].[Parent Client ID], [Partial Interactions Calculations].[Parent level Client], [Partial Interactions Calculations].[Activity Type] AS [Activity Type Name], [Partial Interactions Calculations].[UBS Attendee Gpn], [Partial Interactions Calculations].[UBS Attendee Full Name], Sum([Partial Interactions Calculations].[Partial Interaction]) AS [No Of Activity] INTO [Partial Interactions Calculations Combined]" & _
    "FROM [Partial Interactions Calculations]" & _
    "GROUP BY [Partial Interactions Calculations].[Parent Client ID], [Partial Interactions Calculations].[Parent level Client], [Partial Interactions Calculations].[Activity Type], [Partial Interactions Calculations].[UBS Attendee Gpn], [Partial Interactions Calculations].[UBS Attendee Full Name];"

I've seen this error pop up under different circumstances because of insert/append issues.... just a longshot though... That's all I got

 

[mikew71]

thanks for all the help, but someone here pointed out that I had forgotten the .txt on the output file name..

 

[Thenolos]

That's what I hate about altering the code you show to find the source of an error... every now and then it's something so obvious you correct the error for the example without realising it... Happens to the best of us.... or maybe just me and you... Have fun.