More digging, tried using sp_configure to allow ad hoc queries, got a little closer...

Now I'm just getting a generic error:

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.15.0" for linked server "(null)".

Here are the 2 queries I've tried (trying oledb and jet):

select top 10 * from opendatasource('Microsoft.ACE.OLEDB.15.0', 'Data Source=C:\Databases\Access\myfile.mdb;OLE DB SERVICES=0;Microsoft.ACE.OLEDB.15.0;Persist Security Info=False;')...MyTable

select top 10 * from opendatasource('Microsoft.ACE.OLEDB.15.0', 'Data Source=C:\Databases\Access\myfile.mdb;OLE DB SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;')...MyTable

I know the OLEDB is the correct version as I can use the connection string in a C# console app, no problem. I can't seem to get any more error detail from SSMS, so it appears I'm stuck.

Any thoughts?