Last inserted record not found 1

[PinnacleDan]

I hope I am in the right forum.
I have a VB program that first clears an Access table. Then, using a recordset opened to another database, inserts several records into it.

Set WK = CreateWorkspace("", "admin", "", dbUseJet)
Set DB2 = WK.OpenDatabase(MasterFile)
SQL = "Delete * From Materials "
DB2.Execute SQL, dbFailOnError 
Do While Not RSmatl.EOF
 SQL="Insert Into Materials (JobCode, Item)"
 SQL=SQL & "Values ('" & CurJob & "','" & CurItem & "')" 
 DB2.Execute SQL, dbFailOnError
 RSmatl1.MoveNext
Loop

I then load the table into a datagrid for review.

SQL = "Select * From Materials "
<===delay inserted here===
frmRpt2.AdodcR1.RecordSource = SQL
frmRpt2.AdodcR1.Refresh
frmRpt2.DataGrid1.Refresh

The problem is that the last record inserted does not display in the grid. I added a 1-second delay after the insert and the problem seems resolved. However, I am sure this is not the right way to ensure the insert is complete before I read the table. Any help would be appreciated.

 

[PinnacleDan]

Sorry, I left two lines out of my first set of code:
After the "Do While" and before the "SQL =" I load two variables.
CurJob = RSmatl!Job_Code
CurItem = RSmatl!Item_No

There were three records in the RSmatl recordset and all three got inserted into the Materials table. But only two of them displayed in the datagrid unless I add a delay to the program as indicated. This really looks like an timing problem. I don't understand how to syncronize the process.

 

[JerryKlmns]

You 'll have to use the RefreshCache method of the JetEngine Object, to force pending data changes in the cache to be processed

 

[PinnacleDan]

I may be a bit thick-headed but it appears the RefreshCache method is used in JRO which I know nothing about. Please enlighten me if I am wrong on this. I don't have the time to learn about JRO so I guess I will just have to insert the 1-second delay. Thank you for your help.

 

[BobRodes]

Sigh

 

[SBerthold]

>JRO RefreshCache

That will not work. It only works on the connection passed to it, and only the data written by that connection, and not data written by other connections.

PinnacleDan , you are mixing ADO and DAO, and that is a problem with delays, just the same as it would be using two different ADO connections, or two different DAO connections with different workspaces, because the two different connections cannot additionally see the data the other has written to the cache by the OS.

Use only ADO or DAO, and use the same connection object for both actions. Then, as far as the same connection is concerned, there will be no delays (the same connection will pull data from the source AND the data data written by itself which is still held by the OS in the cache).

You should also wrap the DB2.Execute in a transaction, or at least open and close one afterwards, and under DAO you can additionally request operating system to flush the data during the commit phase, or under ADO use RefreshCache as mentioned.

DAO:
DB2.BeginTrans
DB2.CommitTrans dbForceOSFlush

ADO:
conn.BeginTrans
conn.CommitTrans
Call oJRO.RefreshCache(conn)

So, you have three or four things you should do here for best results:
1. Use just DAO or ADO
2. Use the same connection object for both tasks (or see #4)
3. Use a Transaction for the INSERT action.
4. Force a flushing of the cache if two different connection objects are used (see #2).


You may get by in your current code by just using the following after the update loop:
'Force write by the DAO connection
DB2.BeginTrans
DB2.CommitTrans dbForceOSFlush

'Refresh Read (Add a reference to the "Microsoft Jet and Replications Object") by the ADO connection
oJRO.RefreshCache frmRpt2.AdodcR1.ActiveConnection

 

[JerryKlmns]

>JRO RefreshCache

That will not work. It only works on the connection passed to it, and only the data written by that connection, and not data written by other connections.

I must say I 'm little confused

RefreshCache Method Example
This example demonstrates the RefreshCache method.

Public Sub RefCache()

Dim lateje As JRO.JetEngine
Dim conn As New ADODB.Connection
Dim conn2 As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field

' Open both connections to the database.
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=C:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb;"
conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=C:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb;"

Set lateje = CreateObject("JRO.JetEngine")

On Error Resume Next
conn.Execute "drop table tab1"
On Error GoTo 0

' Create table and input new values for columns.
conn.Execute "create table tab1 ( col1 int)"
conn.Execute "insert into tab1 values (1)"
conn.Execute "insert into tab1 values (2)"

' Flush the data so that conn2 can see the changes from conn1.
lateje.RefreshCache conn2
Set rs = conn2.Execute("select * from tab1")
Set fld = rs.Fields(0)
Debug.Print "Record 1: " & fld.Name & " = " & Str(fld.Value)

conn.Close
conn2.Close
Set lateje = Nothing

End Sub

It uses 2 different connections one doing the job and the other reads.

I am using this

App_Cnn.BeginTrans
App_Cnn.Execute mySQL, , 129
DoCmd.Hourglass False
If MsgBox("Are you sure you want to change that value?", vbQuestion + vbYesNoCancel, App_Name) = vbYes Then
    DoCmd.Hourglass True
    App_Cnn.CommitTrans
    myJRO.RefreshCache App_Cnn
    DoCmd.Hourglass False
    MsgBox "Value changed succesfully.", vbInformation, App_Name
    DoCmd.Hourglass True
    Forms!frmProjects!frmMainData.Requery
    DoCmd.Close acForm, Me.Name
Else
    DoCmd.Hourglass True
    App_Cnn.RollbackTrans
    myJRO.RefreshCache App_Cnn
    MsgBox "Update was canceled", vbInformation, App_Name
    myJRO.RefreshCache App_Cnn
End If

but there are times that updates do not show right away ....

 

[SBerthold]

>That will not work.

It will work as you have posted. Just not as the OP has.

The RefreshCache is used on the connection which did the writing, so the written data is flushed from the cache into the db table(s), and then the second connection, when it's read cache is refreshed, will see those changes, which are pulled from the db, and not the first connection's cache.

What the OP has, is a DAO connection and data written to the db using it. Then they are trying to read the data using ADO. Because the DAO connection cannot be passed to JRO, JRO cannot flush that connection's cache.

JRO can only flush the cached data from the connection you pass to it, not just any and all connections.

 

[PinnacleDan]

Thank all you folks for the input. The lessons are very hellpful. Since I am still in the initial development stage, I will probably take a new approach and rewrite this module completely using your pointers.

 

[SBerthold]

PinnacleDan, good idea.

JerryKlmns,

I didn't look at the second part of your post, just the quoted first part.

You wrote:
"...but there are times that updates do not show right away ...."

Then I looked closer and saw you are using MS ACCESS as the front end.

I am not sure what version you are using, but I slightly remember reading something short several years ago stating that the ACCESS forms still use DAO, even if you use ADO in code. I do not know if this is [still] true.

Another thing would be, if the Forms do use ADO, then you might want to check if they use the same connection as your coded connection object. If not, then do also a JRO refresh using that form's data connection. It will refresh the read cache.

 

[BobRodes]

<rewrite this module

Yes, good idea.

 

[JerryKlmns]

SBerthold

Thanks and a pinky