DAO, ADO Transactions With SQL Server 2005 Using Vb6

I have a million plus line program that uses DAO and JET/Access databases. We are modifying the code so that it will work with BOTH JET and SQL Server databases with an eye to moving to just SQL Server eventually.
Much of the code works just fine without change when using DAO and SQL server as 90+ percent of the code is accessing data READ-ONLY. (Jet's ODBC capability is used)

There are however some transactions that will not work.
When I examine the SQL Trace, I notice that after the transaction has begun, the SPID has changed. This happens often when cursors are used, BUT happens even if the only activity after a DBEngine.BeginTrans is issued DO NOT USE CURSORS as in the following code:
DBEngine.BeginTrans
strSQL = "INSERT INTO [TestTable] (MyKey,MyData, MyData2) VALUES (1,'apple','fruit')"
DB1.Execute strSQL
strSQL = "UPDATE [TestTable] SET [MyData] = 'apple2' WHERE [MyKey] = 1"
DB1.Execute strSQL
DBEngine.Committrans

The second DB1.Execute times out: [Microsoft][ODBC SQL Server Driver]Timeout expired
The trace shows a new SPID is opened when the UPDATE query is executed. A new SPID is spawned and the JET engine is sending an SELECT SQL to get the value for the MyKey:

SELECT "dbo"."TestTable"."MyKey" FROM "dbo"."TestTable" WHERE ("MyKey" = 1 )

The second SQL statement is waiting for the first one that is being done in a transaction to be completed. The program is expecting that both of these will be being done on the SAME SPID or at least the same transaction scope.
So, since I have no way of knowing how and when a new SPID will be spawned using DAO, we are not going to use it for when we do transactions.

I have tried to use ADO (Provider=SQLOLEDB.1), but ADO has the same problems as DAO as far as a new SPID being spawned in some circumstances, but it works in more situations than DAO.
The Connection Object is not guaranteed to be attached to a specific SPID - it can change at will. So Cn.Execute done twice in succession may be done on separate SPIDs.

My question is this: Is there a way to make sure that no new SPID is spawned?, Is there a way to know when a new SPID will be spawned so we can know how to avoid this situation? Is there a better way than using ADO? Keep in mind that this program has to be able to use BOTH Access/Jet databases and SQL server.

Note:
I have spent over 160 hours reading about Jet, the VB Guide to SQL Server, searching MSDN, Google etc. No article really does justice to this subject of transactions using DAO, ADO and SQL Server and the issue of the SPIDs (equivalent to a session and the session is the scope of the transaction). If you could be sure to stay on the same SPID, then you could just issue your own SQL Begin Transaction and control it all if you only plan on doing SQL statements without the need for cursors. A Microsoft article on this subject would be really helpful to all the programmers that are just now getting around to migrating to SQL Server.
Also, note, that if we did not do transactions, then we would not have to modify a single line of our code to make it work with SQL Server.
There is virtually no documentation for much of what I have written about in this Thread. I am very surprised as it seems this would have come up for thousands of programmers. I must be missing something, so thank you in advance for your help.