ADO lock problem with linked tables from SQL Server (ODBC)

[cglisc]

Hello,

We have an application that was developed in MS Access,
with the a front end access mdb and a database back end.

We use ADO 2.7 to do the SQL work.

We are now requried to have 2 versions of this application, both using MS Access and SQL server as the back end database, depending on the clients requirements and prefererences ( and wallet )

So we took the front end mdb and configured it so that it can attache (link) back end tables either from another access mdb or from an SQL server database through ODBC.

Everything seems to be working fine, except at one point in the system where the following is supposed to happen:

1.Begin Transaction
2.Read some data from table A,B
3.Update table B
4.Update table C
5.Read some data from table A,B and C
6.Update table D
7.Commit Transaction

The above steps go fine if the attached back end tables are Access. However, with SQL server, step 5 fails.

When we look at the SQL server, 3 process ids are created when we open the Access database that the SQL Server tables are attached. Let's say process 51,52 and 53.

When step 5 above fails, there are holding blocks in the system that always involve one process waiting for the other one, for example pid 52 wating for pid 51.

Now, we went through the code line by line, and we OPEN ONLY ONE ADO CONNECTION to execute the statements needed for the 7 steps above.

Why it is then that ADO seems to be using different process ids for execute and other for select statements?

Any help would be GREATLY appreciated.

Thanks,
Chris

 

[cmmrfrds]

What is the importance of the linked tables if you are using an ADO connection?

Show the connection/recordset properties - i.e. cursor etc....

Are you batching all the statements?

What ADO method are you using to run the SQL?

Need to see how you are setting the SQL up.