none
Capture Err.Number from Generic Access 'Unable to Append' Dialog RRS feed

  • Question

  • I have an Access VBA procedure that appends all Excel files in a given folder to a specified table. There will almost always be duplicate records, and so the standard dialog will pop up saying, 'Microsoft Access was unable to append all the data to the table.' and then a generic list of reasons why. I can disable the dialog using

    DoCmd.SetWarnings False

    but that disables ALL warning messages. 

    Is there a way to capture the error number/description and do my own error handling? Then I could Resume Next if it's a duplicate or otherwise do some other processing.

    tod

    Here's a mock up of my code (I always indent code between turning application level things on and off. Just a thing I do):

                            

    Public Sub importExcelSheets()
        Dim strDir As String
        Dim strFile As String
        Dim I As Long
        Dim Directory As String
        Dim TableName As String

        TableName = "tblName"
        Directory = "C:\MyPath\FileFolder"

         I = 0
        strFile = Dir(strDir & "*.XLSX")

        While strFile <> ""
            I = I + 1
            strFile = strDir & strFile
            Debug.Print strFile
            DoCmd.SetWarnings False
                DoCmd.TransferSpreadsheet acImport, , TableName, strFile, True
            DoCmd.SetWarnings True
            strFile = Dir()
        Wend

    End Sub

    Wednesday, November 18, 2015 7:03 PM

Answers

  • Hi. Unfortunately, you don't have "complete" control by using the TransferSpreadsheet method. When that method runs, you are leaving all the process or importing the data into your table to Access. Also, I am not aware of any place where the error for duplicate records can be trapped when using the TransferSpreadsheet method. Hope someone else could give us the answer. Barring that, all I could suggest is to not use the TransferSpreadsheet method if you need to catch the duplicate records going into your table. Just my 2 cents...
    • Marked as answer by todtown Wednesday, November 18, 2015 7:58 PM
    Wednesday, November 18, 2015 7:43 PM

All replies

  • Hi,

    If you want complete control over the duplicate values, you might import the data into a temporary table first, so you can clean it up before appending. Or, move the records from the temp table to the permanent table one at a time, which could take longer, but you can definitely handle any error that comes up.

    Just a thought...

    Wednesday, November 18, 2015 7:25 PM
  • I already have complete control over duplicates with a primary key. If there are err.numbers I can capture, that means there is a defined list of errors that would prompt the dialog, and that means I can choose to address or not address each of them.  
    Wednesday, November 18, 2015 7:31 PM
  • Hi. Unfortunately, you don't have "complete" control by using the TransferSpreadsheet method. When that method runs, you are leaving all the process or importing the data into your table to Access. Also, I am not aware of any place where the error for duplicate records can be trapped when using the TransferSpreadsheet method. Hope someone else could give us the answer. Barring that, all I could suggest is to not use the TransferSpreadsheet method if you need to catch the duplicate records going into your table. Just my 2 cents...
    • Marked as answer by todtown Wednesday, November 18, 2015 7:58 PM
    Wednesday, November 18, 2015 7:43 PM
  • Thanx DB Guy. I have a feeling you and I are going to be having a lot of conversations over the next several months. Hopefully with each question I'll be able to demonstrate increased knowledge. That's the point of the forums for me. 

    HOWEVER, I have the duplicate records issue covered. That's done by the primary key. I've tested it. It works. My post was never about duplicates, only about all the OTHER reasons for the dialog. I'm sure you've seen the one I'm talking about. It starts with the very vague title of 'Microsoft Access was unable to append all the data to the table.' It then lists three possible reasons why, and suggests that there may be more reasons. i wanted to capture the err.number to find out what those other reasons may be. 

    Anyway, you've answered it. TransferSpreadsheet encapsulates the stuff I'm looking for. So my choices are to use that method with it's shortcomings, or use an alternate process that gives me more control. 

    tod

    Wednesday, November 18, 2015 7:58 PM
  • Hi tod,

    I have a feeling even if you did add each record from Excel to your table, one at a time, and was able to trap each error as it happens, that the error number and description you'll get back from Access might still be the same "vague" message that the TransferSpreadsheet method was giving you. In any case, the only way to find out is to give it a try. I'd say take a couple of sample records with different problems but you know will produce errors when added to the table. Then, add them one at a time to the table to see if you do get a different error each time.

    Just a thought... Good luck!

    Wednesday, November 18, 2015 8:07 PM
  • Are you the only guy in here? HAHA! 

    Yeah, iterating through the records is out. Too much extra time for not enough payback. However I may consider the temp table suggestion. Then I could send back the records that were not appended, still use my easy cheesy transferspreadsheet method and leave it up to the user to examine the records that failed. 


    Wednesday, November 18, 2015 8:20 PM
  • Hi tod,

    That sounds like a good plan! All the best! Cheers!

    Wednesday, November 18, 2015 8:27 PM