0

I am having some trouble exporting out to a file from VBA. Some things to be aware of are that JobDetail is a valid export SpecName and 2_JobDetail is a query that combines some info with stuff from other fields and tables. When I try to run I get the error like below on my DoCmd.TransferText method call. Advice? am I doing something wrong? code below.

enter image description here

Public Function exportJobDetailRecs(dateStr As String)
    'Docmd.TransferText(acexport,specName,TableName, FileName,HasfieldNames,HTMLTableName)   
    DoCmd.TransferText acExport, _
                        "JobDetail", _
                        "2_JobDetail", _
                        "P:\Folder1\Folder2\Tracker\" + CStr(dateStr + "_OrderStatus_jobdets.txt")   
    exportJobDetailRecs = CStr(dateStr + "_OrderStatus_jobdets.txt")
End Function

Some questions people have asked:

  1. Yes - Do you have full permission to write to the file (i.e. full permission to the folder as well as the text file if you are not creating it with code)
  2. Yes - Check path (folder name with space/etc) for mistakes.
    • and I'm copying with Shift-Right click, then copy as Path, and adding a trailing \ manually
  3. Yes - Have you try exporting manually and overwriting your existing spec and see if that works? If so, try runs the code again afterward
  4. Yes - Have you confirmed JobDetail is an export spec instead of an import spec?
    • and manual exports work fine
  5. 3-50 - How many records does 2_JobDetail currently return?
  6. Same - Try the export (temporarily) to "P:\Folder1\Folder2\Tracker\a.txt"
    • It doesn't care what folder (that exists) I point it to, it wont go
  7. None - Also make sure that there are no punctuation marks in strDate
Erik A
  • 31,639
  • 12
  • 42
  • 67
PsychoData
  • 1,198
  • 16
  • 32
  • Hi PsychoData, Start with something simple: 1) Do you have full permission to write to the file (i.e. full permission to the folder as well as the text file if you are not creating it with code) 2)Check path (folder name with space/etc) – Alex Feb 11 '14 at 21:39
  • Ok, have you try exporting manually and overwriting your existing spec and see if that works? If so, try runs the code again afterward – Alex Feb 11 '14 at 23:09
  • Same results as listed – PsychoData Feb 11 '14 at 23:45
  • Yes, and it works fine when I export manually. – PsychoData Feb 12 '14 at 13:28
  • I would suggest you to create a new table and export it to your c:\. That should work and you could further testing bit by bit (changing your destination folder, then file name, etc). If exporting to C:\ won't work there might be different access right level between your user and the MS access you are using – Alex Feb 12 '14 at 18:54
  • Not working. I can make directories and files with other parts of the code. Specifically later on I read two files in and write out a third combination file, no problems. It's only the export line that has problems. – PsychoData Feb 13 '14 at 05:38

3 Answers3

0

I figured this out. It wanted me to create the destination files too, not just the directories before I could copy to it.

PsychoData
  • 1,198
  • 16
  • 32
0

I ran into the same problem. Solution: I changed all the column names and it worked. Something about the column names MS Access didn't like. I named them f1, f2, f3...

Marc Guvenc
  • 53
  • 5
0

I just ran into the same problem; I changed the encoding from 'Westeuropean (Windows)' to 'Unicode (UTF-8)' and that did the trick. The problem probably had to do with this invisible 'character' in one of the fieldnames: "observer".

Karen
  • 1