Updating SQL Server Linked Server

[Guest]

I desperately need some help here....

I initially created a blank front-end Access .mdb database from which I am
using the linked tables feature (which uses the SQL Server ODBC driver) to
connect to an SQL Server database. However, I am actually accessing another
back-end database via the SQL Server Linked Server mechanism (which uses OLE
DB). Thus the LINKED TABLE in Access refers to a VIEW in SQL Server which in
turn refers to the remote table in SQL Linked Server. But, when I try to
update the linked table, Access does not allow me and I get the following
error message:

The requested operation could not be performed because the OLE DB provider
'XXXXXXXXX" does not support the required transaction interface.

The SQL Profiler trace shows that Access is setting the IMPLICIT
TRANSACTIONS setting to ON even though SQL Books Online says that the SQL
Server ODBC driver automatically sets IMPLICIT_TRANSACTIONS to OFF when
connecting. The starting of an explicit SQL transaction causes SQL Server to
escalate the local transaction into a distributed transaction which is not
supported by all of our databases. Is there some Access/ODBC setting that can
be made to avoid the IMPLICIT_TRANSACTION? In other words, can Access be made
to work in automatic transactions mode?

 

[david epsom dot com dot au]

1) When you open a table in table view, or run a query from a macro
or from the database window, you should normally be in an implicit
transaction.

2) Other than that, the rules do seem to have shifted around a bit.
Are you using FailOnError? ADO or DAO?

3) You can use pass through queries instead of linked tables.

(david)

 

[Guest]

Thanks for the reply:

1) Well it doen't seem to be. The Profiler trace reveals that SET IMPLICIT
TRANSACTIONS ON is being executed.

2) What rules are you talking about? the failure happens when I am designing
a form using the the Linked Table as a record source as well as when using
ADO.

3) Yes, that definitely works. But, that defeats the ability to use
recordsets for manipulating the Linked Table.

Actuall, I am looking for a way to prevent a transaction from being started
on the Linked Table. That would fix my problem. Is there some ODBC connection
or Driver setting that I can use?

Supi

 

[david epsom dot com dot au]

There is no Linked Table setting or Driver setting or Form setting
that can be used directly. There were possibly some DAO settings,
(like dao.dbFailOnError) and some lock settings (like Snapshot),
but these may not work anymore.

There may be some ADO setting: you should ask in an ADO group, and
you may have different options using OLEDB instead of ODBC.

(david)