How to prevent multiple connections when using linked tables to SQL server

  • Thread starter Thread starter ertanzanagar
  • Start date Start date
E

ertanzanagar

Hello,


I have a VB app that tries to read/write data from an access 2002 DB
that has linked tables to a SQL server.


My problem is that my read after the begintrans creates a new
connection (SPID) in SQL server therefore causes an update on an
already locked record. How can I make sure that all my reads and
writes are done in the same connection?


Thanks


ertan Zanagar


l_objDBConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Program Files\Imagine Solutions Distributed
Capture\ISDistCapture.mdb;Persist Security Info=False"


l_objDBConn.CursorLocation = adUseClient


Set l_objRecordset = l_objDBConn.Execute("Select * from Customer where

customerid =1")


'If I comment out the line below, I have no problems.
l_objDBConn.BeginTrans


l_objDBConn.Execute "update customer set SSN = '199999992' where
customerid =1", l_lngRecordsAffected


'*** This read starts a new SPID in SQL server (a new connection)
Set l_objRecordset = l_objDBConn.Execute("Select * from Customer
where customerid =1")


l_objDBConn.Execute "update customer set SSN = '100000000' where
customerid =1", l_lngRecordsAffected


l_objDBConn.CommitTrans
 
Back
Top