Unable to begin a distributed transaction: Linked Server -> Access database

Question

Specs:

• Sql Server 2008
• Server 2008 R2 64bit
• MS Access database

What works:

• I have configured a Linked server connection to the Access database by installing office access data components and creating a system DSN and whatnot. which resides in a directory on the same server.

• I have succeeded in quering data through openquery.

  SELECT * FROM OPENQUERY(LINKEDHELL, 'SELECT * FROM [D:\path\mordor.mdb].Orcs')

• I can insert/update/delete through openquery (from inside the Sql Management Studio)

What does not work:

I have created a database which exposes a view that wraps the openquery expression. This allows my applications to remain oblivious to the fact thats its quering through an linked server connection and allows me to use an ORM. Case in point: Entity Framework.

However whilst retrieving data works, updating/inserting/delete wont. I have spend quite some time making sure that the MSDTC is configured correctly however no dice.

What i tried

MSDTC

• Enabled Network DTC Access
• Allow Inbound/outbound
• Configured Firewall
• Enabled XA Transactions
• Gave account under which DTC works access to the .mdb
• Restarted Sql Server after making these settings

Other

• Configured linked server security: Created mapping between sql user and local admin account, so that when i login with that sql user, it uses the local administrators account to connect to the access database.
• Enable Promotion of Distributed Transactions for RPC : False

The error im getting:

OLE DB provider "MSDASQL" for linked server "LINKEDHELL" returned message "[Microsoft][ODBC Microsoft Access Driver]Invalid attribute/option identifier ".
Msg 7391, Level 16, State 2, Line 4
The operation could not be performed because OLE DB provider "MSDASQL" for linked server "LINKEDHELL" was unable to begin a distributed transaction.

I can simply recreate this by doing this from the Sql Management Studio

begin distributed transaction

//anything that queries the linked server

commit transaction

So what have I missed? I have read that distributed transactions are supported by the ODBC driver, but im unsure if the ACCESS database does. So if someone could at the very least confirm that. That would help.

Answer 1

Access doesn't have transactions, because Access is not a database engine. Jet/ACE (Access's default db engine) has supported commit/rollback as long as I've used it (since Jet 2.x, e.g., 1996). It has never supported transaction logging and probably never will (I recognize that's not what you're asking about but many of those coming to Jet/ACE from server database backgrounds are rather foggy on the meaning of the term "transactions" and have a hard time grasping that Jet/ACE supports one and not the other). – David-W-Fenton Jan 16 '10 at 23:12

-- Do we have transactions in MS-Access?

David W Fenton, MVP, RIP