Results 1 to 4 of 4
  1. #1
    dumbledown is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    46

    Smile Link to txt file, but specify formatting

    Hi,



    I'm linking to a txt file which works well. The second column contains mainly numbers, but now and again a bit of text as well.
    When linking, the numbers all appear, but the text records produce a #Num! reading.
    Does anyone know how to specify the second column as being a text column in a newly linked txt file.
    The same problem happens if it is a csv file as well.

    This is the code I've used:

    DoCmd.TransferText acLinkDelim, , "nodesready", _
    Forms![runselection]![SourceDirectory] _
    & "\nodesready.txt", True, ""


    Many thanks
    Hugh

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I have read that TransferText takes its cue for the field type based on first line (or several first lines). Have you noticed if the linking behavior is different if the first lines have alpha characters in the value? The import/link wizard allows you to set the datatype.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    dumbledown is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    46
    Thanks June7 for the reply:
    I inserted the first 2 lines with text at the relevant column and the text still is imported as #NUM!
    The import/link wizard is great, but I would like to automate the process.
    Do you know of a way around this, or if there is a way of instructing the wizard by VBA?

  4. #4
    dumbledown is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    46
    Cracked it - I used an import specification formed by manually importing

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-26-2011, 05:18 PM
  2. Link Excel file to MS Access and generate rep
    By Priceless in forum Import/Export Data
    Replies: 14
    Last Post: 02-21-2011, 02:08 PM
  3. Access link to excel file
    By delkath in forum Access
    Replies: 3
    Last Post: 09-13-2010, 12:28 PM
  4. Text File Link
    By pkell658 in forum Import/Export Data
    Replies: 1
    Last Post: 03-23-2010, 04:46 PM
  5. Link table using MAC PC file sharing problem
    By TaiYipStreet in forum Access
    Replies: 0
    Last Post: 07-19-2006, 08:02 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums