Results 1 to 4 of 4

Thread: TransferText()

  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: TransferText()

    Here's one for all you dbForum'ers out there:

    This company gets sent a *.ORD file every week (basically a *.txt with a funny extension) that needs importing into an access database table.

    But as you may be aware - Access dislikes anything that's not a proper text file.

    So here is my problem: Without manually renaming the extension of the file in question - can I run this import?

    Can I force access to accept this file as a deliminated text file?
    OR
    Can I have access copy the file >> rename it >> run import >> delete the copied file (change to a.txt so acecss is happy).?

    Example text file:
    Code:
    Item Code, Description, Price
    1, item1 desciption, 10.00
    2, item2 description, 11.00
    3, item3 description, 9.50
    4, item4 description, 10.00
    Import code:
    Code:
    DoCmd.TransferText acImportDelim, , "Products", <full file path>, True
    Please note that when I manually rename the file to .txt and run the above code, Access is happy.

    Sadly, I cannot ask the users to rename the file for selection - because that would be far too easy!

    Any help/suggestions/comments welcomed!

    Cheres

    - GeorgeV
    George
    Home | Blog

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Perhaps something like this

    Code:
        Dim FName As String
        Dim TempFName
        
        FName = "Your Path & File Name"
        
        TempFName = Left(FName, Len(FName) - 4) & ".txt"
        
        Name FName As TempFName
        
        DoCmd.TransferText acImportDelim, , "Products", TempFName, True
        
        Name TempFName As FName


    Any good ?


    MTB

    One after thought, you may need a delay while the name change is performed !!??

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You are astud muffin!
    That does exactly what I wanted

    I might have to ask a few more questions in a minute when the project goes tits up though :P

    CHEERS!

    - GeorgeV
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Told you I'd be back...

    To run my code I have to turn on Microsoft Office 10.0 Object Library (or 11.0) I.e. Tools >> References >> Microsoft Office 10.0 Object Library.

    Now, when it comes to give this to the customer I can almost guarentee the code will fall over because MSO 10.0 Object library is not turned on.

    Is there anyway in VB code that I can turn the object library on? Or if I simply send them the database will my settings still apply?

    Hope this makes sense!

    - GeorgeV
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •