Вот код:
On Error GoTo perevod_err

DBEngine.BeginTrans

DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
DoCmd.RunSQL strSQL3
DoCmd.RunSQL strSQL4

DBEngine.CommitTrans
Exit Sub
perevod_err:
MsgBox "Err"
DBEngine.rollback

1. в строке DoCmd.RunSQL strSQL2 добавляются записи в Таблицу1.
Причем в Таблице1 имеется обязательное поле.
2.Принудительно из источника удаляю одну запись, чтобы проверить.
3. В результате запрос выполняется, не добавляя этой строки где я
удалил принудительно запись.
4. Никакого сообщения я недождался.

Что я делаю не так?

Через ADO не получается, так как в запросах есть ссылки на
сохраненные запросы, он их не любит.

CurrentDb.Execute

никаких проблем.