Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I have 2 identical workbooks within 1 file, how do I delete 1? | Excel Worksheet Functions | |||
How to delete duplicates between workbooks? | Excel Worksheet Functions | |||
How to delete duplicates between workbooks? | Excel Worksheet Functions | |||
Delete columns simultaneously in several different workbooks | Excel Discussion (Misc queries) | |||
simultaneous run | Excel Programming |