I just tested this out on SQL Server 2000 and SQL Server 7.0, works fine on both. However it fails on both if you do the INSERT within a transaction or have implicit_transactions on. Also I defined my Link Server to MSAccess with the OLE DB Provider for Jet, I believe you could use OLE DB Provider for ODBC Drivers.
The provider must support ITransactionJoin to work with MTS or distributed queries, Jet 4.0 does not, some ODBC drivers do, which I haven't gone into to investigate which ones.
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable
I have a SQL server 2000 where I created a linked server with the ODBC provider to an access 2000 database. then in the SQL server I associated an trigger to a table, which at any insert, inserts some data into the access table.
But when the trigger is executed the following error is fired :
"The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction."
If instead of the ODBC provider I use a linked server through jet 4.0
when the trigger is executed I get the following message:
"The current transaction could not be exported to the remote provider. It has been rolled back."