SELECT through oledbcommand in vb.net not picking up recent changes.

Question

I'm using the following code to work out the next unique Order Number in an access database. ServerDB is a "System.Data.OleDb.OleDbConnection"

Dim command As New OleDb.OleDbCommand("", serverDB)
command.CommandText = "SELECT max (ORDERNO) FROM WORKORDR"
iOrder = command.ExecuteScalar()
NewOrderNo = (iOrder + 1)

If I subsequently create a WORKORDR (using a different DB connection), the code will not pick up the new "next order number."

e.g.

iFoo = NewOrderNo
CreateNewWorkOrderWithNumber(iFoo)
iFoo2 = NewOrderNo

will return the same value to both iFoo and iFoo2.

If I Close and then reopen serverDB, as part of the "NewOrderNo" function, then it works. iFoo and iFoo2 will be correct.

Is there any way to force a "System.Data.OleDb.OleDbConnection" to refresh the database in this situation without closing and reopening the connection. e.g. Is there anything equivalent to serverdb.refresh or serverdb.FlushCache

How I create the order. I wondered if this could be caused by not updating my transactions after creating the order. I'm using an XSD for the order creation, and the code I use to create the record is ...

Sub CreateNewWorkOrderWithNumber(ByVal iNewOrder As Integer)
    Dim OrderDS As New CNC
    Dim OrderAdapter As New CNCTableAdapters.WORKORDRTableAdapter

    Dim NewWorkOrder As CNC.WORKORDRRow = OrderDS.WORKORDR.NewWORKORDRRow

    NewWorkOrder.ORDERNO = iNewOrder
    NewWorkOrder.name = "lots of fields filled in here."

    OrderDS.WORKORDR.AddWORKORDRRow(NewWorkOrder)
    OrderAdapter.Update(NewWorkOrder)

    OrderDS.AcceptChanges()
End Sub

Answer 1

I always throw away a Connection Object after I used it. Due to Connection Pooling getting a new Connection is cheap.

Answer 2

From MSDN

Microsoft Jet has a read-cache that is updated every PageTimeout milliseconds (default is 5000ms = 5 seconds). It also has a lazy-write mechanism that operates on a separate thread to main processing and thus writes changes to disk asynchronously. These two mechanisms help boost performance, but in certain situations that require high concurrency, they may create problems.

• If you possibly can, just use one connection.
• Back in VB6 you could force the connection to refresh itself using ADO. I don't know whether it's possible with VB.NET. My Google-fu seems to be weak today.
• You can change the PageTimeout value in the registry but that will affect all programs on the computer that use the Jet engine (i.e. programmatic use of Access databases)