Create Access.Application after setting reference with Excel VBA,
I'm writing the process of executing DoCmd.TransferSpreadsheet and importing xlsx into accdb.

At this time, it basically operated normally,
I tested the table with duplicate primary key data and it passed without any error.

If i do the same process on Access VBA, a dialog saying "Could not add all data" is displayed,
Is there any way to display the dialog in the same way or cause an error?

  • Answer # 1

    Isn't the Application.DisplayAlerts property set to output a message?

    Create Access.Application

    If so, please check Set Warnings.

  • Answer # 2

    When Access is executed from Excel, the warning message and information message are not displayed, which is by design.
    Even if I intentionally made an error, it ended as if nothing happened.

    In VBA, when I use the on error statement to avoid an error, the content of the error is stored in the McroError property, but it was not.
    (In the case where a file name that does not exist is specified, an error always occurs)

    On the Access side, count the number of cases and check the number of registered cases and the number of original data, or once import into a table that stores anything, then register and detect inconsistency. I have no choice but to do it.

    Somehow, it seems good to check the data on the Access side and display a message box or create a log file.
    (The message box could be displayed.)

  • Answer # 3

    The reason why it is unknown but it is probably a specification