99.99999999999% of FM developers use a primary key in the form a a field named "ID" (or a variation thereof) that is set to either an auto-enter serial # or an auto-enter UUID. And thats good enough for ODBC.
I've never had ODBC fail on me for lack of a primary key. But then I don't think I've ever tried it from Access. To troubleshoot, try the same DSN you have and use Excel to see if you can connect. Just to rule out that it is access complaining. When in doubt, get the Advanced FTS book from the FMI website. Chapter 9 has a step-by-step on using ODBC to connect to a FM file, and it uses Excel as the example.
Sorry for stating obvious, but Is the field indexed? It could be one of the things Access if looking to determine Primary keys.
Works flawlessly on excel, I suspect I am experiencing what this programmer had trouble with:
The access database can see all tables in the odbc connection, but as I progress to the next step in the application- mapping primary key, it simply can't detect one. Programmatically, I wonder if filemaker declares a primary key explicitly in its odbc implementation. It could be implicitly only.
This may not be access related, could be the 3rd party application.
Will try with a pure access database tomorrow, will report results.
Obvious things are good to hear too, as we all make mistakes every now and again - sadly not in this case.
Have tried even with non-indexed fields in desperation.
Programmatically, I wonder if filemaker declares a primary key explicitly in its odbc implementation. It could be implicitly only.
You're over-thinking it. Since it works from Excel, the ODBC driver works without issues. Anything else is due to the other application whatever it is.
Make sure you have the latest ODBC driver installed. I believe it is in the install package with FM Server. I have never seen the problem you are seeing.
The xDBC driver will classify as a primary key any field whose validation options have all of the following:
Validate data in this field "Always"
Require "Not empty"
Require "Unique value"
This will satisfy Access' need for primary keys. It may have other needs, though - Access has much more complicated ODBC interactions than Excel.
Actual Technologies - ODBC for OS X
You might verify that all the data in the FileMaker Key field is in-fact not blank and not a duplicate. It could be that the other system is verifying this. Not sure that is possible but it would be easy enough to verify. FileMaker does not enforce unique values at all times and legacy data or imports could bypass this.
Sadly, that was the first thing I tried.
All data in the "Key" field is unique and not empty.
Have tried with every single ODBC driver available, none help any more than the other.
You're right, and can connect to a host of other DBMS.
Seems to be the third-party application that has some strange requirements.