It can be really hard to track these down - the error messages from MS Jet aren't very helpful. Here's how I'd go about trying to find the issue.
1) Copy and paste each of the three SQL statements EXACTLY as they are in the union query and make sure that they work as individual custom SQL connections. That would rule out a simple typo in one of the clauses.
2) Carefully compare the names and data types of all the fields created in the three connections. I'm not sure, but I suspect JET may complain if if thinks it has detected different data types for the same field. (I'm not sure what to do if that's the case, though - I can't think how to control that with a schema.ini file - maybe that's not what's happening. Report back if that does look the problem and I'll think again.)
The only thing I noticed on a quick read of the SQL was that you have this field in the third clause:
[CYCLETIME$].[OMT CT] AS [OMR CT],
Is that supposed to be [OMT CT] or should it be [OMR CT]?
Thank you Richard your suggestion was very helpful in tracking down which query wasn't working.