SQL Server Delete / Append Request - Duplicates

[cj92713696]

In the below example, if I don't issue a 10 second pause after the delete query and attempt to add data w/o this pause, I get duplicate entries. It's like SQL Server isn't finished deleting data when I attempt to append. Am I doing something wrong?

Thanks,
CJ



dbConn.Open DE.fatsales_SQL.ConnectionString

dbConn.Execute "DELETE FROM " & strTableName & " WHERE " & strTableName & _
".StoreCode = '" & strStoreCode & "' AND " & strTableName & ".Date >= #" & _
Format(startDate, &quot;mm/dd/yyyy&quot & &quot;# AND &quot; & strTableName & &quot;.Date <= #&quot; & _
Format(endDate, &quot;mm/dd/yyyy&quot & &quot;#&quot;

GlobalAccess.addServiceHistory &quot;Command issued at - &quot; & Format(Now, &quot;hh:mm:ss&quot

frmService.txtCmdStatus.Text = dbConn.State

While dbConn.State = adStateExecuting Or _
dbConn.State = adStateConnecting Or _
dbConn.State = adStateFetching

frmService.txtCmdStatus.Text = dbConn.State
Wend

frmService.txtCmdStatus.Text = dbConn.State

GlobalAccess.addServiceHistory &quot;Command completed at - &quot; & Format(Now, &quot;hh:mm:ss&quot

dbConn.Close

GlobalAccess.addDbHistory _
strStoreCode, _
&quot;Microsoft Access database (*.mdb) delete; table: &quot; & strTableName & _
&quot;, store code: &quot; & strStoreCode & &quot;, start date: &quot; & startDate & _
&quot;, end date: &quot; & endDate

GlobalAccess.addServiceHistory _
&quot;(tbl: &quot; & strTableName & &quot Pause to allow cached data to flush; 10 secs.&quot;

startTime = Now
While Utils.wait(startTime, 10) = True
Wend

 

[RJFost]

Try wrapping the DELETE operation in a transaction. That way, when you issue the COMMIT TRAN, your code waits for that statement to finish before it moves on.

 

[cj92713696]

RJFost, I appreciate your help w/this, but I'm rather new to SQL. Would you help me w/a simple example of a delete option in a transaction?

Thanks,
CJ