Updating SQL Server Linked Server

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Supi (Supi_at_discussions.microsoft.com)
Date: 09/08/04 

Date: Tue, 7 Sep 2004 18:17:02 -0700

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?

Relevant Pages

  • RE: Foreign Key Violations During Insert in Transaction
    ... parent row into the database, SQL Server generates a new identity value. ... that value does really exist even before you commit the transaction. ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: database access from VC++
    ... But my database already exist in SQL server 2005. ... Microsoft Online Community Support ... where an initial response from the community or a Microsoft Support ...
    (microsoft.public.vc.database)
  • Re: transfering ASPNETDB.MDF Authentiocation to SQLServer
    ... ** Since SQL Server Express's database(your current mdf data file based ... database) are just a standard SQL server 2005 data file, ... Microsoft MSDN Online Support Lead ... where an initial response from the community or a Microsoft Support ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: statement not allowed within multi-statement transaction (Long)
    ... So if I do have an open transaction I can't figure ... > 100 Database does not exist in sysdatabases can not dettach ... > --Verify database Exiists for detach ... >> Wayne Snyder, MCDBA, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Updating SQL Server Linked Server
    ... > I initially created a blank front-end Access .mdb database from which I am ... > connect to an SQL Server database. ... > 'XXXXXXXXX" does not support the required transaction interface. ...
    (microsoft.public.access.externaldata)