Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error trapping (Access 2003)

    Hello Access Gurus,

    I am importing some Excel data into an Access database table (tblImportData) using the docmd statement. However this doesn't enable me to check errors if there are data type errors etc. The statement I am using is:

    DoCmd.TransferSpreadsheet acImport, 8, "tblImportData", gFileName, True, ""

    where gFileName is the name of the selected file.

    I would like to be able to avoid the Access error message that appears if there are data type errors (it also gives the user the option to click Yes or No) and provide my own error trapping. I have the OnError sub set up but am not sure how to capture the error here.

    I look forward to any ideas.

    Kind regards

    Jocelyn

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

    Re: Error trapping (Access 2003)

    If the method outlined by John Hutchison in <post:=645,671>post 645,671</post:> doesn't help, the only thing you can do is ensure that the worksheet has the correct structure before trying to import it.

    You may be able to suppress the error message by inserting

    DoCmd.SetWarnings False

    before importing, and

    DoCmd.SetWarnings True

    after it.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error trapping (Access 2003)

    Thanks Hans,

    I have tried all of these methods.
    Firstly there is no error number when you get the general data type error message after import. I suppose it isn't really an error message as such.
    Secondly, if I suppress this message altogether with the SetWarnings property of the DoCmd then it turns off all messaging and you aren't sure whether you have had a successful import without checking the Database window. I guess I will just have to live with it.

    Thanks again for your assistance.

    Regards

    Jocelyn

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

    Re: Error trapping (Access 2003)

    You could create a temporary table with as many text fields as needed, and import the worksheet into the temp table. This should succeed without problems. Then use code to loop through the records of the temporary table, check the field values for validity, then convert the values to the correct data type and transfer them to the target table. You could report any problems that occur.
    But that's a lot of work, I don't know if it's worth the trouble.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error trapping (Access 2003)

    Thanks for the idea but I think it is too much trouble. I suppose the message itself is enough to warn you to check your data before trying to import into Access.

    Regards

    Jocelyn

Posting Permissions

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