Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Compare Import Field Names (Access2003)

    This may have been asked and answered, and if so please accept my apology.

    I have a user who imports a data file periodically into a temporary table. The data, after importing is then appended to a table where update queries massage the data. The problem is sometimes, for different reasons, the incoming data file has a missing column, or the column heading has been changed. This results in errors when running the update and/or append queries.

    Is there an easy, quick down and dirty way to compare the incoming data column headings to a list of the correct field names? For example, have a lookup table with the correct column headings, then loop through the lookup table and loop through the field names in the temporary table to ensure all fields exist and are correctly spelled. If there is a difference it can be identified before starting to run through the queries.

    Thanks in advance for your ideas.

    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare Import Field Names (Access2003)

    Does the thread beginning at <post:=467,755>post 467,755</post:> help? (The attachment to one of the replies is lost, but that shouldn't matter).

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Compare Import Field Names (Access2003)

    Hans,
    Thanks! I think that will work nicely. However, I do have a question relating to importing data using the following:
    DoCmd.TransferSpreadsheet acImport, 8, "Data", strPath, True, ""

    Is there a way to modify the above line to create the table when importing, instead of having to have the "Data" table exist prior to importing?

    Thanks for your help!

    Ken

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Compare Import Field Names (Access2003)

    The table data does not need to exist before the import. If it does not exist, it will be created.

    This is from Help
    Attached Images Attached Images
    Regards
    John



  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare Import Field Names (Access2003)

    If Data doesn't exist, TransferSpreadsheet will create it automatically. You don't have to do anything special.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Compare Import Field Names (Access2003)

    John and Hans,

    Thanks. I thought that was the case, but everytime I tried to run the routine I kept getting an error when table didn't exist. Then I looked a few lines above the TransferSpreadsheet, and saw "DoCmd.RunSQL "Delete * from Data". Duh! ! !

    Next time I will do my homework!

    Thank you both.

Posting Permissions

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