Error Handle "unable to append all the data to the table" Prompt

[Pisteuo]

I am running VBA to append tables with spreadsheet data.

When appended non-unique IDs, Access prompts "unable to append all the data to the table". The prompt asks if the user would like to proceed anyway - Yes or No. For my database, this prompt means an error occurred and the append procedure should never proceed, so the answer to the prompt is always "No".

Is there anyway to interact with this prompt through VBA so that I can handle it as an error?

Thank you.

 

[gemma-the-husky]

try this - use currentdb.execute instead of runsql

on error goto fail
currentdb.execute "queryname", dbfailonerror
exit sub
 
fail:
msgbox "Error adding records. " & vbcrlf & " Error: " & err & " Desc: " & err.description

 

[Pisteuo]

Thank you Gemma.

I used:

CurrentDb.CreateQueryDef.Execute dbErrHandleronError
Exit Sub

ErrHandler:
{Delete Queries}
msgbox "Error adding records. " & vbcrlf & " Error: " & err & " Desc: " & err.description

The "unable to append" prompt is still occurring. Err.Description within my message box simply says "Operation cancelled by user", as if the user caused the error by choosing "No" and interrupting the operation.

My main priority is to remove the possibility of the user choosing "Yes" when prompted by the "unable to append".

Thanks again.

 

[the jesse]

I'm having a similar issue with appending tables, were you able to resolve yours?

 

[Pisteuo]

This issue has not been resolved. I still would like to override Access' prompt with my own procedure when duplicated IDs are being appended.

 

[smig]

in most cases "Operation cancelled by user" will be caused by an error in the query
Make sure the query run in the query grid.

Why don't you use the code gemma gave you? It will run either for a query built in the query grid, or one written as SQL string

 

[Pisteuo]

I tried the error handler both ways. The problem - I believe - is that existence of a duplicated ID is not picked up as an error, rather Access' canned handler (prompt) executes. I want to do away with the canned handler. The "operation cancelled by user" occurs only after the MS Access prompt asks if the user wants to proceed despite the existence of duplicates, and the user chooses "no". In my case, the user should not proceed, so the user should click "no". Only after this does my error handler finally execute.

 

[spikepl]

In #3 you do not show the code that Dave provided in #2 but something else. Your "something else" does NOT work like Dave's code. Did you try Dave's code as is? Or was it rewritten into something else? Show the actual code you have run. dbfailonerror has a predefined value, and you cannot just replace it freely with your own creation.