Simultaneous rst.AddNew and rst.Delete from 2 Workbooks
 

Hello all,

I was hoping to get some direction with an issue I am having.

I have two workbooks connecting to the same MS Access table across a
network. Both workbooks will work fine for a while and then suddenly
the second workbook will hang. Here's how they are set-up:

When the workbook "One" receives data (via a dde triggering .ondata) it
writes the data to the MS Access table as follows...

With rstTempMarketDataStore ' used by the slave copy for market data
access
.AddNew
!MarketIndex = Market.MarketIndexNumber
!Time = Market.TimeStamp
!MillSec = Market.MillSec
!Last = Market.Last
.Update
End With

rstTempMarketDataStore is opend as follows:

Set rstTempMarketDataStore = New ADODB.Recordset
rstTempMarketDataStore.CursorLocation = adUseClient
rstTempMarketDataStore.Open "SELECT * FROM
TempMarketDataStore", eScalperTestDBADOConnection, adOpenDynamic,
adLockOptimistic, adCmdText


Workbook "Two", via a timer (set-up via a win 32 api) fires this every
one second...

With rstReadOnlyTempMarketDataStore
.Open "SELECT * FROM TempMarketDataStore ORDER BY Time,
MillSec", eScalperDBADOConnection, adOpenForwardOnly, adLockOptimistic,
adCmdText

If Not .EOF And Not .BOF Then

.MoveFirst

Do Until .EOF Or .BOF
TempTickStore(0, TempTickStoreCounter) = !MarketIndex '
the market index number
TempTickStore(1, TempTickStoreCounter) = !Time
TempTickStore(2, TempTickStoreCounter) = !MillSec
TempTickStore(3, TempTickStoreCounter) = !Last
TempTickStoreCounter = TempTickStoreCounter + 1

On Error Resume Next
RetryDelete:
.Delete

' if data colision then wiat 1/10th sec and try
again
If Err.Number < 0 Then
PauseTime = 0.1 ' Set duration.
start = Timer ' Set start time.
Do While Timer < start + PauseTime
'DoEvents ' Yield to other processes.
Loop

Err.Number = 0
GoTo RetryDelete

End If

If TempTickStoreCounter = 9000 Then Exit Do
.MoveNext
On Error GoTo 0
Loop
End If
.Close
End With

rstReadOnlyTempMarketDataStore is set as follows:
Set rstReadOnlyTempMarketDataStore = New ADODB.Recordset
rstReadOnlyTempMarketDataStore.CursorLocation = adUseClient

(Yes, the "ReadOnly" part of the rst name is a misnomer due to a design
change)

Any thoughts on a better way to handle this??

Thanks!

Trip