ADO 2.8 Slow Update Problem with VB.NET 2003 and Access2003

Hi Everyone,

I am having a problem using ADO 2.8 with VB.NET. I have one function that opens an Access 2003 recordset and updates it. Then the next function queries the same table to decide what the next id should be, but the new record is not there. If I slow down execution by stepping through the program then I can find the new record. These two actions occur in separate functions and I have done everything I can think of to force the update to occur immediately.. including, closing the recordset and setting the variable to nothing. I stuck this into a VB6 app and it ran perfectly. What is taking the update so long to complete? How can I fix this problem?

Thanks,
Tony


Here is the code: As you can see the two action occur in a loop.

Private strData() as String
Private TransNum as string


Private Sub DoMain()
ts = New StreamReader(GetAppPath() & "upload.txt")

Do While ts.Peek >= 0
strData = Split(ts.ReadLine, ",")
TransNum = GetTransNum(strData(8))
AddRecord()
Loop

End Sub


Private Function GetTransNum(ByVal TransDate As String) As String
Dim aConn As New ADODB.Connection, aRS As New ADODB.Recordset, TransNum As Integer

aConn.Open(strConn)
strSQL = "select max([Transaction Number]) as Trans from transactions where " & _
"left([Transaction Number],8) = '" & TransDate & "' and [Transaction Number] is not null"

aRS.CursorLocation = ADODB.CursorLocationEnum.adUseServer
aRS.Open(strSQL, aConn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic)

If aRS.EOF = True Then
TransNum = 1
Else
TransNum = VBS.Right(aRS("Trans").Value, 4)
TransNum = CInt(TransNum) + 1
End If

GetTransNum = TransDate & Format(TransNum, "0000")

End Function


Private Sub AddRecord()
Dim aConn As New ADODB.Connection, aRS As New ADODB.Recordset, strSQL As String

aConn.Open(strConn)
strSQL = "select * from transactions order by [transaction number], [line number]"
aRS.CursorLocation = CursorLocationEnum.adUseServer
aRS.Open(strSQL, aConn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic)

aRS.AddNew()
aRS("Item Number").Value = strData(0)
aRS("Serial #").Value = strData(1)
aRS("P O #").Value = strData(2)
aRS("Account Number").Value = strData(3)
aRS("Received").Value = strData(4)
aRS("Returned").Value = strData(5)
aRS("Issued").Value = strData(6)
aRS("Job #").Value = strData(7)
aRS("DateTime").Value = strData(8) & " " & strData(9)
aRS("Transaction Number").Value = TransNum
aRS.Update()
aRS.Close()

End Sub