TransferText appears to delete the file instead of export data to it

JoanneJames

Registered User.
Local time
Today, 16:57
Joined
Feb 19, 2008
Messages
59
Could someone please help me with this problem? :confused: I have the following stmt:
DoCmd.TransferText acExportMerge, , SelectedQuery, DataDocFileName, True. Whenever I run it, I get an error msg that the Access engine could not find the DataDocFileName. I've checked it and know it is correct. In addition, the file gets deleted, even though it is there just before I run the TransferText. Also, it was actually running fine before. The only thing I changed was the data that went into the file - so I made sure the fields and number of them matched with what I was trying to put into the file. Also, the documentation on TransferText says that if it can't find a file it will re-create it, which it is not doing either.

The one thing which I have no control over, is that I am now testing under terminal services (used to do this in Citrix). However, like I mentioned, it WAS working at first (under terminal services), but now isn't. Anyone have ideas? I really need some help on this one!

Here's whats in the file:
"letterid" "patientid" "PtName" "MDLast" "Letterdate" "ActionDate" "RecipientName1" "Address1" "Address2" "AddressRoomNumb" "AddressLocation" "AddressLocMod"
748 24052 "TestName" "Makkar" 11/12/2009 0:00:00 11/12/2009 0:00:00 "Makkar" "TestAddress1" "TestAddress2" "342" "HH"


Here's the query that generates the data to go into the file:

SELECT Userletter.patientid, Userletter.Letterdate, Userletter.RecipientName1, Userletter.Address1, Userletter.Address2, Userletter.PtName, Userletter.MDLast, Userletter.ActionDate, Userletter.AddressLocation, IIf(nz([AddressLocation]) Not In ('H House',' '),'the ' & [AddressLocation],[AddressLocation]) AS AddressLocMod
FROM Userletter
WHERE (((Userletter.letterid) In (790)));
 
What is the code you have which assigns the value to DataDocFileName?
 
What is the code you have which assigns the value to DataDocFileName?

FileFolderName = "J:\Remote\Files_OtherUsed" 'Test folder
DataDocFileName = FileFolderName & "\" & DocumentName & "_Data.txt"
Document Name is passed to the routine that processes this. I've checked the name and it appears correct.


I'm also finding that the following stmt isn't working either.

With wDoc.MailMerge
.OpenDataSource DataDocFileName


For some reason, Access is not seeing this file (I do have to map this location when going into terminal services, but like I said, it was working B4 in terminal services - unless it is being affected by something within terminal services itself?)
 
Sounds like something may have things mapped wrong in TS. You might check with your system admin to see if the J: drive is being mapped properly.
 
Sounds like something may have things mapped wrong in TS. You might check with your system admin to see if the J: drive is being mapped properly.

What's strange is that in Access, I'm having the problem, but I can see the mapped drive just fine via the Op Sys.
 
What's strange is that in Access, I'm having the problem, but I can see the mapped drive just fine via the Op Sys.
Are you saying you can, while logged into TS, that you see the J: drive just fine? If so, can you add or edit a file manually?
 
Are you saying you can, while logged into TS, that you see the J: drive just fine? If so, can you add or edit a file manually?

Yes- which is what I had to do to match up the file with the new query output, since I thought that might be the source of the problem.
 
Yes- which is what I had to do to match up the file with the new query output, since I thought that might be the source of the problem.

Actually, I'm IN the J: drive, doing all my processing from there.
 
Sorry for all of the questions but it can be a pain to diagnose some of these issues.

So, does the text export work fine if you use the database outside of TS (just on your local computer, for example)?
 
Sorry for all of the questions but it can be a pain to diagnose some of these issues.

So, does the text export work fine if you use the database outside of TS (just on your local computer, for example)?

I haven't tried that yet --- am taking a breather. This is very frustrating!
 
Well, looks like accessing it from another mapped drive isn't working either!
 
What version of Access are you using?

Its 2003: 11.8166.8221 SP3

Our network guy couldn't find any recent updates (past 2 days), to terminal services (although I know we had a major upgrade not too long ago). Also, no updates for Access
 
Since you are on Office 2003 SP3, you might need to use one of the hotfixes that are out there as well.

Check into these:

HotFix 1

Hotfix 2


Hotfix 3

Hotfix 4

Just check them out as it is possible that one, or more, may help.
 
Since you are on Office 2003 SP3, you might need to use one of the hotfixes that are out there as well.

Check into these:

HotFix 1

Hotfix 2


Hotfix 3

Hotfix 4

Just check them out as it is possible that one, or more, may help.

Thanks - I will look at them. In the meantime, I found a connection: I changed the query back and now transfertext is 'seeing' the file. Strange. I'll have to play around with it from that angle.
 
Good luck with it all. Troubleshooting problems can be a pain sometimes.
 
Thanks - I will look at them. In the meantime, I found a connection: I changed the query back and now transfertext is 'seeing' the file. Strange. I'll have to play around with it from that angle.
REASON FOR PROBLEM: I tried posting this once before and it didn't 'take', so will try again...
It appears that the Access documentation about transfertext is wrong - unless I read it wrong. It indicates you can use a query to generate the data to be moved to a file. From my experience with this problem, it appears that all fields within the table being used must be present and although you can add extra fields (like I had in my query), they will NOT appear in the data file.
 
REASON FOR PROBLEM: I tried posting this once before and it didn't 'take', so will try again...
It appears that the Access documentation about transfertext is wrong - unless I read it wrong. It indicates you can use a query to generate the data to be moved to a file. From my experience with this problem, it appears that all fields within the table being used must be present and although you can add extra fields (like I had in my query), they will NOT appear in the data file.

:cool: Well....the above is NOT accurate --- I managed to totally not see that the data was NOT getting entered for two fields into the table that was the source for the query for the datamerge file. I changed the code that was preventing this so the data would get added --- and the problem has been fixed. ARGHH!!!
 

Users who are viewing this thread

Back
Top Bottom