I have a point feature class in a SQL Express ("Personal ArcSDE") geodatabase. I also get the same error when I attempt to create an ODBC linked table to said feature class from within Access . I'm able to link to all non-spatial tables, even those that are registered with the geodatabase from within the ESRI realm. The two point feature classes in my database have both a clustered index and a spatial index. I suspect that one of those indexes is breaking the ODBC connection. Unlike you, I am able to see the feature class base tables in SSMS.
My work around was to create a view of the feature class in SSMS and then create the ODBC link to the view and not the table. This may not be possibly in your situation since you can't even see the base table in SSMS. However, I first tried creating the view using something to effect of:
CREATE VIEW [dbo].[vw_MyView]
AS
SELECT dbo.MyFeatureClassBaseTable.*
FROM dbo.MyFeatureClassBaseTable
I was able to create the ODBC link from Access to this view. But when I attempted to edit a cell's value in Access, I was getting some sort of error. I deleted the view and the link and then created a new view that lists all of the columns that I need, such as
CREATE VIEW [dbo].[vw_MyView]
AS
SELECT dbo.Col1, Col2, Col3
FROM dbo.MyFeatureClassBaseTable
Now I can edit cell values in Access.
ESRI constantly warns users about monkeying with geodatabase tables outside of the ArcGIS environment. I agree that it comes with risks of corruption. But at the same time, SQL is often a much, much more efficient way of managing data. I've been using Access and SSMA to manipulate geodatabase data for over a decade. I have never encountered a problem using Access or SQL as long as:
-- you don't monkey with any versioned tables or feature classes in any way
-- you don't delete features in a feature class (sometimes the spatial indexes are in separate tables and the corresponding row would also need to be deleted)
-- you don't add a row to a feature class (for the same reason as above)
-- you don't add a row to a non-spatial table that is registered with the geodb and therefore has an OBJECTID field that ArcGIS libraries should manage
-- you don't delete any tables or FCs that are registered with the geodatabase
