For Windows, have you tried the 32-bit ODBC driver?
I've tried using the 32-bit ODBC driver as a System DSN, it doesn't work. Same goes for the 64-bit ODBC driver.
Exactly how far in the process can you go? File>Manage Database> Manage External Data Sources = can you define your data source reference?
Yes, I'm able to define the data source. Testing the connection works. When I click on the Relationship tab, I can add the ESS table occurence, with the fields appearing. However, once I go back to Browse mode, where the errors occur, when I switch back to the Relationship tab, the ESS fields disappear and in its place is the error message that also appears in Browse mode. I'll try to get a screenshot of it tomorrow morning (I'm in UTC +8).
BTW I've also successfully added some system tables ("syscategories") from the same data source and the records appear fine.
I have no idea why this particular table is giving me problems.
Just out of curiousity, what do the name of the table and the fields in it look like?
I was wondering, since it worked fine in LibreOffice, and you can add some other table...if there is a field that is using a reserved word.
MightyData had posted a sample file/script that you can use to test the names to see if they match FM's list of reserved SQL words.
Or possibly if one of the fields uses a native FM reserved word: http://www.nightwing.com.au/FileMaker/Resources/ReservedWords.html
I just ran the MightyData script through a copy of the database that uses an ODBC import instead of ESS, and there were neither SQL- nor FM-reserved words.
Can you try deleting some of the fields in the table from the ESS Shadow table in blocks and see if you can narrow down the field(s) that is/are causing the error? Is this an actual table or is it possibly a view? Also, are you sure that your Primary Key is truly unique?
Deleting the fields do not help. I still get the same errors. The only time when there are no errors are if:
a) the table is empty
b) I use another data source pointing to the Microsoft default database's system tables
Given (a) and (b), it would seem to be some data issue on the source tables. But that doesn't explain why using a straight import works without problems. And that LibreOffice has no problems either.
General column properties:
Extended column properties:
I noticed that the Data Type is GUID. I searched the FMP 12 ODBC/JDBC Guide and on page 58 there isn't any equivalent mapping for SQL GUID. Take a look at these links:
I'm really surprised that you were not able to isolate a field or set of fields by systemattically deleting fields from the ESS Shadow table. There has to be some piece of data or data type in there that Filemaker is choking on. Especially since it doesn't bug out with not data in the table. I would suggest removing the GUID field. Did you delete that in the first round of attempts?
As a side question: Do you get anything returned using the Get(LastODBCError)?