Re: Updating SQL Server Linked Server

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

From: david epsom dot com dot au (david_at_epsomdotcomdotau)
Date: 09/09/04 

Date: Thu, 9 Sep 2004 12:59:17 +1000

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)

"Supi" <Supi@discussions.microsoft.com> wrote in message
news:9A408E8E-8837-45AC-A922-C3344A5798F1@microsoft.com...
> 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: SQL Express Edition Network Deployment Error
    ... You might also want to look into using one of the open source database ... The issue of "The file *.mdf is on a network path that is not supported ... SQL Server 2005 Express Edition Overview ... Microsoft Online Community Support ...
    (microsoft.public.dotnet.framework.adonet)