How to import a fixed width text file into access using Form/VBA/Macro RRS feed

  • Question

  • I was able to come across a possible solution for this and I don't know if anyone else has suggestions on this. Anyway I'll tell you what I did and the problem I'm still having

    I want to be able to create a form in Access 2016 and put a command button on the form. When the user clicks on that form I want to import a fixed width text file into an access database/table. Below is what I did to solve this.

    I clicked on the External Data tab and selected Text File. I went through the wizard and towards the end there is an advanced button on the wizard form. On that dialog box there is a save as button to save the Import specification. 

    From there I created my form and command button and for the onClick event I put the following:

    DoCmd.TransferText acImportDelim, "Sold Items_07_26_2016_04_53_PM Import Specification", "SoldItems1", fName, True

    As you can see from the above I used the import specification file to execute this task

    Below is what I am now experiencing:

    As you can see from the image attached when you do the import wizard it give you multiple options. The first and second option tells you what it will do if the table exists or doesn't exist.

    When I run this myself through the click event of the command button, if the table exists it appends to the table. In other words it adds a second copied of the data. In my case my file has 98 records. The second time I run this it has 196 records

    Now the table that it created it didn't create a primary key. Is this why it appended to the table?

    What if I put a primary key on the table and run it again. What will happen to the data? will it update the existing records and add new records for the new data? Will an error get thrown for duplicates?

    I don't know if the above is the best possible solution to this issue. But I would like to know what I can do about the above problems I'm facing, but also is there another way to perform this task?

    Also do you know of any tutorials/books, etc that would help me better understand how to use the the 'Create' tab, Macro button in the 'macros & code' group? I would like to know if this would be another way to perform the above.

    My main issues with trying to do the above is when the text file gets updated with new records and existing records are update, when I click the button on my form I want to put in all the code necessary, including error messages, to check for duplicates, blank fields, etc.

    Would it be best to make sure the table has the proper primary key, and the field types are the right ones so they don't accept blank values? That way when the import runs there will be less errors and coding to do


    Keith Aul

    Thursday, July 28, 2016 5:11 PM


All replies

  • Hi Keith. If you want a clean copy of the data from the text file each time you run the TransferText code, then you can try either deleting the data or the table first. Just a thought...
    Thursday, July 28, 2016 6:17 PM
  • Selecting the necessary fields for a primary key is a must. Either that or you have to have some way to check if the data has already been imported. Without knowing what constitutes a unique row we can't be of much use to you.

    Bill Mosca

    Thursday, July 28, 2016 9:07 PM
  • Hi KeithAul,

    please visit link below will give you a detail explanation regarding your question regarding to append the data, using of primary key, generate error for duplicate data and violation of primary key etc.

    Import or link to data in a text file



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 29, 2016 2:09 AM