Visual Basic SQL App. Question

[cj92713696]

I've written a VB 6 application that checks for the existence of a simple ASCII-based transaction files in a specific hard-coded directory and subsequently parses each file it encounters. After the file is parsed, it is moved to a "processed" directory.

My ASCII-based files look something like this:

Start->TableName=Table1
"Field1", "Field2", "StoreCode"
1,"Jack",AIR
2,"Bill",AIR
3,"John",AIR
End->TableName=Table1
Start->TableName=Table2
"Field1", "Field2", "Field3"
1,"Jack","4.00"
2,"Bill","5.00"
End->TableName=Table2

... etc. I might have 25 table's data in one transaction file. After I read each table identifier to determine the table being updated, I issue a delete request. This saves me the headache of having to calculate the changes to the table since the last update b/c I can just subsequently append the data read from this transaction file back to the SQl Server table.

My resolution to the problem: I had to issue a 10 second pause after issuing a delete request to each table to ensure the records were updated properly. Without this pause, it seemed that SQL wasn't finished deleting when I began appending new records to the same table.

My problem: I want to speed up this process b/c as you can see delaying 10 seconds after each table can cause heavy delays when 50 transactions are in the queue.

Is there a way (or a function) to determine if SQL is ready to handle table appends? Or perhaps a way to determine that the last command issued is complete?

Thanks for your help,
CJ

 

[pweegar]

Take a look at the Recordset:State property (IF you are using ADO). A good source of ADO info is:

http://www.devguru.com.

Also look at the Recordset:Status property.

 

[cj92713696]

Pweegar,

Thanks - I appreciate your help.

After I issue my .execute query, I do a simple while-wend loop making sure the recordset state is not currently executing code. The funny thing is, it's instant as I expected! I put a timer on my loop and it takes less than a second for SQL Server to execute and process the delete query. I'm at a total loss at this point as to what is happening. I posted my issueDelete subroutine. My while-wend loop in the below example is under "Access" and not SQL only b/c I had already tested the SQL side w/o any luck.

Thanks,
CJ

Private Sub issueDeleteByStoreAndDate(strTableName As String, _
strStoreCode As String, _
startDate As Date, _
endDate As Date)

Dim startTime As Date

Dim dbConn As New ADODB.Connection

If strDatabase = "Access" Then
dbConn.Open DE.fatsales_Access.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
ElseIf strDatabase = &quot;SQL Server&quot; Then
dbConn.Open DE.fatsales_SQL.ConnectionString

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

GlobalAccess.addDbHistory _
strStoreCode, _
&quot;SQL Server database 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; 5 secs.&quot;

startTime = Now
While Utils.wait(startTime, 5) = True
frmService.txtCmdStatus.Text = dbConn.State
Wend

dbConn.Close
End If
End Sub