• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

TransferText fails when file name looks like "rad23859.tmp.txt"

When I run this command,
   DoCmd.TransferText acImportDelim, "Mergetemp Import Specification", _
        "471Merge", "c:\aaatmp\mergetemp\radC7555.tmp.txt"", False, ""

I get this error message.
      "The Microsoft jet database engine could not find the object 'radc7555.tmp.txt'….."

If I rename the file and change the command so they both reference "radc7555.tmp" it works fine.

I want my VB utility to work no matter what the filename.
0
rberke
Asked:
rberke
  • 6
  • 4
  • 3
  • +1
1 Solution
 
Mike77Commented:
why do you have a double " at the end of your file name?
0
 
nico5038Commented:
When you want also filenames with a trailing double quote, use a string variable like:
strFile = "c:\aaatmp\mergetemp\radC7555.tmp.txt" & chr(34)

DoCmd.TransferText acImportDelim, "Mergetemp Import Specification", _
       "471Merge", strFile , False, ""

Nic;o)
0
 
rberkeAuthor Commented:
Oops. The double quote was a typo in the question, and was never in the Visual Basic program. The following code also fails.  

s$ = "c:\aaatmp\mergetemp\radC7555.tmp.txt"
DoCmd.TransferText acImportDelim, "Mergetemp Import Specification", "471Merge", s$, False, ""
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
nico5038Commented:
Hmm, looks familiar. Have seen this error before.
Looks like the "double extension" (two separation dots) isn't used properly when looking for the file.

Can't recall that I solved this.

Some options however come to mind:
1) Has the latest service pack been installed?
   (e.g. for A2000 SR1a/2:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q276367
2) Is using a Dir command to find a file "walking through a map" an option ?

Nic;o)
0
 
shanesuebsahakarnCommented:
All versions of Jet after 3.5sp2 (and possibly earlier) will not let you import a file with more than one . character in the file name.

What you may want to do is rename the file programmatically, import and then rename it back:
Dim strFile As String

strFile="c:\aaatmp\mergetemp\radC7555.tmp.txt"
Name strFileName As "c:\temp\myimportfile.txt"
DoCmd.TransferText acImportDelim, "Mergetemp Import Specification", "471Merge", "c:\temp\myimportfile.txt", False, ""
Name "c:\temp\myimportfile.txt" As strFileName

Bear in mind that this will error if the file is already in use. The above code will work though regardless of the file name - just put the path of the file you want to import into strFile. You might also consider using FileCopy to copy the file rather than renaming (moving) it but this will slow down your import process if the file is very large.
0
 
rberkeAuthor Commented:
Good suggestions.

Shanesuebsahakam's renaming suggestion seems best so far.

I'm windows XP at SP-2, so I don't think old software is problem.

Nico, I am getting file names by doing a loop with
s$= Dir("C:\aaatmp\merge\*.*")

Is that what you mean by "walking through a map"?

Bob

P.S. I'm new to EE. Is how long should I leave this open before awarding points?  Is there a FAQ that answer these newbe questions.
0
 
shanesuebsahakarnCommented:
rbeke, it's up to you how long you want to keep it open, but basically close it when you feel you've got an answer that satisfies your needs, and no different suggestions have been made for a while (2-3 or so days, possibly ? I'm not sure).

I had the same problem you did (along with a few others like filenames being too long). I think it's an inherent limitation in the text ISAM - I worked around it in the manner I describe, I didn't manage to find a way to make Access import a file with more than one . in it (in my case the dots were used to seperate date parts).
0
 
nico5038Commented:
Hmm, another option might be to use the DOS filename format.
The old unique 8 characters with extension should work always...

For general info about EE you can use:
http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Nic;o)
0
 
rberkeAuthor Commented:
Nic,

8.3 name is an interesting idea.  I think I'll put the following in my loop  

s$=dir("c:\aaatmp\*.*",vbNormal)
DoCmd.TransferText acImportDelim, "Mergetemp Import Specification", "471Merge", ShowShortName(s$), False, "" 

This will workaround my problems nicely without the messy rename problems.

I'm still hoping someone can explain how to make Access honor a long filename with name.tmp.txt.

I'll leave the problem open till monday, then award the points.

Bob

PS this function I am stealing from an Access Help on "ShortName":

Function ShowShortName(filespec)
    Dim fs, f, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    ShowShortName = f.ShortName
End Function
0
 
rberkeAuthor Commented:
Ooops, left a line out in my stolen function.  It should say:

Function ShowShortName(filespec)
   Dim fs, f, s
   Set fs = CreateObject("Scripting.FileSystemObject")
   Set f = fs.GetFile(filespec)
   ShowShortName = f.ShortName
End Function
0
 
nico5038Commented:
Great work Bob !

I see you know your way around in Access ;-)

Success and I hope to find some day a real solution for this irritating problem.

Nic;o)
0
 
rberkeAuthor Commented:
Nic,

Well, I want to close this. Being new to EE I need guidance. I'm told I can goto community support and split the points.  For instance 100 to shanesuebsahakarn for his good suggestion and 400 to nic5038 for a solution that was significantly better.  I know its completely up to me, but what is the prevailing practice?  Is it common to split points?  Or is a simple THANK YOU SHANESUEBSAHAKARN in a comment considered more appropriate?  (its certainly simpler for me.)

Also, does it matter which of Nic's comments I accept as the final answer?  In truth, no single one is very complete.

Let me know, what you think, and we will wrap this up.

For posterity, here is the final solution:


Function merge_to_471_merge()
On Error GoTo merge_to_471_merge_Err
    DoCmd.SetWarnings False
    DoCmd.CopyObject "", "471Merge", acTable, "471 download template"
   
     s$ = Dir("c:\aaatmp\mergetemp\*.*")
     
    While s$ <> ""
        s$ = myShortPath("c:\aaatmp\mergetemp\" & s$)
        Rem s2 = InputBox("", "", s$)
        DoCmd.TransferText acImportDelim, "Mergetemp Import Specification", _
        "471Merge", s$, False, ""
        s$ = Dir()
    Wend
    DoCmd.OpenQuery "471Merge Scrub1", acViewNormal, acEdit
    DoCmd.SetWarnings True
merge_to_471_merge_Exit:
    Exit Function
merge_to_471_merge_Err:
    MsgBox Error$ & s$
    Resume merge_to_471_merge_Exit
End Function


Function myShortPath(filespec)
  Dim fs, f, s
  Set fs = CreateObject("Scripting.FileSystemObject")
  Set f = fs.GetFile(filespec)
  s = f.shortpath
  myShortPath = s
End Function
0
 
shanesuebsahakarnCommented:
rbeke, a thank you is fine, no need to make life any more complicated than it already is :)

It doesn't really matter which coment you accept as an answer (it's quite rare that a single comment in itself forms a full answer), but someone viewing the question can see your final result at the end, or read through the thread to see how you arrived at it.
0
 
rberkeAuthor Commented:
To everyone, thanks a lot.  My routine seems to work fine.

to Shanesuebsahakarn - I agree simple is better. I've just read the procedure on splitting points and it looks pretty cumbersome.  If it were easier, split points would probably occur more often.



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

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now