When you select data into your recordset, it is only a snapshot of the
data at the instant that the Open method ran. Any subsequent updates
to the data that you selected will not be visible to your VB
application until you Open the recordset again. If the datadb table
is very large, you may want to find a way to limit the number of rows
you are selecting by adding a where caluse and being as restrictive as
possible. Also, since you are ordering your results by the AutoIDdb
field, you will want to make sure that it is indexed, unless the
AutoIDdb field is already the primary key or has a unique constraint
on it.
If your VB application is "looking" for new records or to simply see
if new records have been entered, then you could use an Identity field
in your SQL Server table to accomplish this. Get the maximum value
from your table with:
Dim sql as String
sql = "Select max(AutoIDdb) as MaxID FROM [datadb]"
Dim lMaxId as Long
mrsdatadb.Open sql, mconAP, adOpenKeyset, adLockOptimistic, adCmdText
mrsdatadb.MoveFirst
lMaxID = mrsdatadb("MaxID")
mrsdatadb.Close ' be sure to close a recordset as soon as you are done with it
Then you can get newer records with:
sql = "Select * FROM [datadb] WHERE AutoIDdb>" & lMaxID & " ORDER BY AutoIDdb"
mrsdatadb.Open sql, mconAP, adOpenKeyset, adLockOptimistic, adCmdText
Or you can tell if there are new records with:
sql = "Select count(*) as NewerRecs FROM [datadb] where AutoIDdb>" & lMaxID
mrsdatadb.Open sql, mconAP, adOpenKeyset, adLockOptimistic, adCmdText
mrsdatadb.MoveFirst
If mrsdatadb("NewerRecs")>0 Then
' you have new data to handle
Else
' wait a litte while
End If
Essentially, you are trying to minimize your interaction with the data
to only the data you know you are going to need for the current
operation. The data you fetch is only a snapshot, so anticipate that
it will begin changing the moment your code continues executing on the
next line. |
