Using Adaptive Server Anywhere (ASA) driver version 7 and the ODBC version 3, in a heterogeneous query between Oracle and ASA 7, the concatenate function fails with the following error message:
'[Microsoft][ODBC Microsoft Access Driver] Invalid use of vertical bars in query expression 'Left([Report Pack MPG 400 Main Range Basic View].[PackSeason ID], 1) || [Report Pack MPG 400 Main Range Basic View].[Pack ID]'.'
The ASA support for Version 7 ended some time ago. Therefore connecting via ODBC won't work as long as the ODBC driver is v3 or newer.
You can confirm by noting the ASA driver version in the Microsoft ODBC Administrator.
For heterogeneous queries, you must switch to Access syntax for the SQL statements.
To concatenate fields in Access you must use the & "" & syntax.
This syntax is not in the calculated field editor, so it will need to be typed in manually but it should help to concatenate.