How many tansactions? - JET Insert into linke SQL Server Table

[lameid]

I have never tested this and would like to know before I find out via trial by ordeal (or fire up my SQL Express server at home this weekend)...

If I use an Access Front End and import data to a local table, process it and subsequently insert it into a SQL Server linked table, is that one transaction or might the ODBC driver break it up?

I ask because I am inserting into a table with a trigger that groups by data that is inserted... In this case there should be 1 action taken by the trigger if inserted in the same statement.... but there are mulitple rows that could yield multiple actions if Access/ODBC conspire against me.

Many thanks in advance.

 

[kjv1611]

I'm pretty sure that's 2 actions:
Basically...
1. SELECT FROM (inserting into a local Access table)
2. INSERT INTO (updating the SQL linked table from Access

Whether you do it direct on the SQL Server or do it in your Access database, that should line up to 2 actions every time.

I would imagine that the ODBC connection will be instantiated twice as well.

Are you sure you can't have the full action done direct on the SQL server? I'd wonder about just using a Passthrough query for the whole thing - then you could do it in one action.

 

[lameid]

It feels like it is running as one SQL Transaction... Although I've never really confirmed this is always the behavior. As long as my trigger is good, it shouldn't matter except for performance anyways.