locked
DAO, ADO Transactions with SQL Server 2005 using vb6 RRS feed

  • Question

  • 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.
    Monday, March 19, 2007 10:40 PM

Answers

  • Dan,

     

    I also have spent a lot of time on this with no solution. As to Microsoft's comments about this, you may want to check out this article:  http://support.microsoft.com/kb/170548

     

    I think, if you haven't done this already, that you'll have to cut bait from Access and go directly to SQL Server. Not fun, I know...

     

    Good Luck & God Bless!

    Bob

    Thursday, April 17, 2008 6:36 PM
  • Thank you.  I had read kb170548 before and had forgotten specifically that it had helped me a little by steering me toward thinking the answer to my question was NO.

    The kb170548 is the closest we have to Microsoft admitting that this is a problem and that it has no solution.

    At this time, my only solution has been to handle manually the transactions and do my own clean-up from them via code, or write the transaction into a stored procedure in the SQL Server Database.  But, of course, with that solution it is really not the same code being used for both Access and SQL server.

    Thank you again,
    Dan

    Friday, April 18, 2008 5:16 PM

All replies

  • Dan,

     

    I also have spent a lot of time on this with no solution. As to Microsoft's comments about this, you may want to check out this article:  http://support.microsoft.com/kb/170548

     

    I think, if you haven't done this already, that you'll have to cut bait from Access and go directly to SQL Server. Not fun, I know...

     

    Good Luck & God Bless!

    Bob

    Thursday, April 17, 2008 6:36 PM
  • Thank you.  I had read kb170548 before and had forgotten specifically that it had helped me a little by steering me toward thinking the answer to my question was NO.

    The kb170548 is the closest we have to Microsoft admitting that this is a problem and that it has no solution.

    At this time, my only solution has been to handle manually the transactions and do my own clean-up from them via code, or write the transaction into a stored procedure in the SQL Server Database.  But, of course, with that solution it is really not the same code being used for both Access and SQL server.

    Thank you again,
    Dan

    Friday, April 18, 2008 5:16 PM