Delay in Updating Record

[momon]

Ok, the following code checks to see if a record exists in the ORDER table, and if it doesn't it adds it to the table. It uses an ADO implementation. Now, the problem is, it takes a while before the ORDER table is updated with the new entry. I was thinking that the table would be updated immediately with the .Update command, but this doesn't seem to be the case.

Does anyone know how to add a new record to the table followed by the table updating itself immediately?

Private Sub Command55_Click()
On Error GoTo command55_click_err

Dim criteria(1) As Variant
Dim stdocname As String
Dim curconn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim curdb As Database

Set curdb = CurrentDb
Set curconn = New ADODB.Connection

criteria(0) = Forms!start!

criteria(1) = Forms!start![ITEM NUMBER]

With curconn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & curdb.Name
.Open
End With

Set rst = New ADODB.Recordset

rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "ORDER", curconn, , , adCmdTableDirect
rst.MoveFirst
rst.Index = "PrimaryKey"
rst.Seek criteria

If rst.EOF Then
With rst
'add new record
.AddNew
!

= criteria(0)
![ITEM NUMBER] = criteria(1)
.Update 'at this point shouldn't the table update
End With
End If

rst.Close

'stdocname = "Order Checklist Form"
'DoCmd.OpenForm stdocname

command55_click_exit:
Exit Sub
command55_click_err:
MsgBox Err.Description
Resume command55_click_exit

End Sub

Thanks in advance.

 

[thornmastr]

1) how many records on your table?
2) Are you using JET or a high powered engine like SQL SERVER?
3) Is this a networked application? Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[momon]

1) About 4 000 records with each record having about 150 fields
2) I am using JET
3) Yes, this is an networked application

 

[momon]

The funny thing is that when I write the equivalent code in DAO I don't experience this problem of delayed table updates.

Here is the code

Private Sub Command55_Click()
On Error GoTo command55_click_err
Dim dbs As Database, rst As DAO.Recordset
Dim quote As String
Dim number As Integer
Dim stDocName As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("order", dbOpenTable, dbAppendOnly)

quote = Forms![start]!

number = Forms![start]![ITEM NUMBER]

With rst
.Index = "PrimaryKey"

.Seek "=", quote, number
If .NoMatch Then 'no match found
'add new record to the end of the recordset
.AddNew
!

= quote
![ITEM NUMBER] = number
.Update
End If

.Close

End With
dbs.Close

stDocName = "Order Checklist Form"
DoCmd.OpenForm stDocName

command55_click_exit:
Exit Sub
command55_click_err:
MsgBox Err.Description
Resume command55_click_exit
End Sub

 

[thornmastr]

Jet should have no problem updating your table certainly fast enough that you are not going to notice any lag at all.

Assuming that you are using a FE/BE configuration on your network, I would look closely at your code for the answer. Looking at your code puts me at a bit of a disadvantage because my personal preference is not ADO. I know how to use it, and from time to time I do use it, but I am, simply through years of use, more inclined to use DAO; so I did not spend too much time looking at your connection specs. However, I do have a serious question about your use of seek. You are assuming that a no match condition will put you at EOF. That is not true. A2K simply leaves currentrec as undefined.

My first suggestion is to use the nomatch test to make your determination. If it is true, then update your record. You do not physically have to move to EOF. Just do the addnew.

Also, your seek does not have a conditional test. It should be rst.Seek “=”, criteria.

And last but I think not least, you define criteria as a one element array while it is a two element array. While this will compile and may work for a while, eventually it will bite you. And as a corollary I would make the seek statement rst.seek “=”, criteria(0) & criteria(1).

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com