?
Solved

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

Posted on 2003-03-21
14
Medium Priority
?
291 Views
Last Modified: 2008-02-01
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
Comment
Question by:rberke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 5

Expert Comment

by:Mike77
ID: 8184167
why do you have a double " at the end of your file name?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 8184196
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
 
LVL 5

Author Comment

by:rberke
ID: 8184370
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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 54

Expert Comment

by:nico5038
ID: 8184434
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8184446
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
 
LVL 5

Author Comment

by:rberke
ID: 8185221
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8185240
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 2000 total points
ID: 8185809
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
 
LVL 5

Author Comment

by:rberke
ID: 8187032
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
 
LVL 5

Author Comment

by:rberke
ID: 8187043
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
 
LVL 54

Expert Comment

by:nico5038
ID: 8187057
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
 
LVL 5

Author Comment

by:rberke
ID: 8188616
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8188626
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
 
LVL 5

Author Comment

by:rberke
ID: 8189478
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

770 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