Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Tampa, Florida, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transfer Text using Access 97 (Access 97)

    I have a GUI created using Excel 97 VBA. What is created by the end user is a delaminated text file. I can import the text file to my database using

    File>Get External Data>Import> and selecting the appropriate text file.

    There is no issue. When it try to set up VBA code to import this text file I get runtime error 2391 saying that field F1 is not available in my database. Here is the code I am using.

    Docmd.TransferText acimportdelim, "", "Recurrent Table", recurpath & fName,0

    I have told it to import delaminated, there is no specification name, the table name is correct, the path name and file name are string variables that reach the correct file, and there is no header row. I need to import this using VBA because the file names will change depending on the user and will never be the same and there will be multiple files being imported. Am I missing something? Dose anyone have any advice. I am new to using VBA for Access, but I am experienced using VBA in Excel. Please help.


    Thank you.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Transfer Text using Access 97 (Access 97)

    Your import command
    Docmd.TransferText acimportdelim, "", "Recurrent Table", recurpath & fName,0
    should probably look like:
    Docmd.TransferText acimportdelim, , "Recurrent Table", recurpath & fName,False

    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Transfer Text using Access 97 (Access 97)

    According to my experiments you will definitely need to use an import specification.

    To create this you go through the manual import up to the point where you are about to click 'Finish'. Then you click 'advanced'. You then need to alter all the field names to the actual names of the destination table. Then you click 'Save specification' and give it a name.

    Someone else might know a better way than this but this one works.
    David Grugeon
    Brisbane Australia

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfer Text using Access 97 (Access 97)

    Since you say you're experienced in XL VBA...

    Why not use ADO to update the data to Access directly?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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