The SQL error indicates that a comparision is being done and there is a collation mismatch in the fields being compared. Running these SQL statements will tell you the collation of the SQL Server and the MDB
SELECT CONVERT (varchar, SERVERPROPERTY('collation'));
SELECT name, collation_name FROM sys.databases;
These need to be the same and in this case they were not.
SELECT CONVERT (varchar, SERVERPROPERTY('collation'));
This returned "SQL_Latin1_General_CP1_CI_AS".
SELECT name, collation_name FROM sys.databases;
This returned "Finnish_Swedish_CI_AS".
The database was taken from a SQL Server whose collation was "Finnish_Swedish_CI_AS" and restored onto a system where the collation was "SQL_Latin1_General_CP1_CI_AS" and that difference was the cause of this problem. The solution is to change the collation of the SQL Server to be the same as the collation of the MDB. There are many articles on how to do this, one can be found here:
https://www.mssqltips.com/sqlservertip/2901/how-to-change-server-level-collation-for-a-sql-server-instance/