INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Last inserted record not found

Last inserted record not found

(OP)
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.

CODE

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.

CODE

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.

RE: Last inserted record not found

(OP)
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.

RE: Last inserted record not found


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

RE: Last inserted record not found

(OP)
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.

RE: Last inserted record not found

Sigh

RE: Last inserted record not found

>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


RE: Last inserted record not found

Quote (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.
I must say I 'm little confused

Quote (Help):


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

CODE

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 sad....

RE: Last inserted record not found

>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.

RE: Last inserted record not found

(OP)
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.

RE: Last inserted record not found

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.

RE: Last inserted record not found

<rewrite this module

Yes, good idea.  smile

RE: Last inserted record not found

SBerthold

Thanks and a pinky

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close