MS Access Time Delays After Insertion

[Chillgeek]

Dear All,

I am facing a strange problem off late with the MS Access database. After Insertion of records, I close the recordset and not the connection as I use a global connection, which is set at the time of the app launch. Now, after closing the recordset I move on to the next job in the program flow and return a success to the calling function. Once the value is received by the calling function it opens the same table and finds no records there. I could come around this prob by providing a delay of a few seconds and then returning the value to the calling function. Can someone help me understand this prob and the solution to this?

Thanks in advance.

Chillgeek

 

[gemma-the-husky]

It would be interesting to see your code. Do you have a set of queries etc running in a sequential manner via code, or are you runnnig your series of procedures in another way.

 

[Chillgeek]

Thanks Gemma,

I am not running any queries as you say and I am not using procedures at all. Here is the code sample,

If rsItmDtls.EOF = False And rsItmDtls.BOF = False Then

For i = 1 To rsItmDtls.RecordCount

If rsRPEx.State = 1 Then rsRPEx.Close

rsRPEx.Open "Insert Into XYZ Values ('" & rsItmDtls(2).Value & "','" & rsItmDtls(3).Value & "','" & rsItmDtls(4).Value & "','" & rsItmDtls(5).Value & "','" & rsItmDtls(6).Value & "','" & rsItmDtls(7).Value & "','" & rsItmDtls(8).Value & "','" & rsItmDtls(9).Value & "','" & rsItmDtls(10).Value & "','" & rsItmDtls(12).Value & "')", cnRPEx, adOpenDynamic, adLockBatchOptimistic


If Not rsItmDtls.EOF Then rsItmDtls.MoveNext

Next i

If rsRPEx.State = 1 Then rsRPEx.Close ' Closes the recordset after finishing the insert.

' InwInvErr = 1 'Indicates successful completion of the request.


End if

Now, once this return value is returned to the calling function, it immediately tries to open the XYZ table and read the inserted data but finds nothing there. Now if a delay of a few seconds is provided then all data is perfectly available.

 

[gemma-the-husky]

I am not sure if open in this context is the same as open when dealing with text files, but the A97 access help, talks about flushing buffered/delayed writes with a reset command

I don't use ADO (i think thats what .open is, isnt it?)

But, It sounds like there is some sort of buffered write going on, if the new record is not being made visible immediately. Perhaps there is a way of forcing the write to commit.

 

[Chillgeek]

you are right gemma.. thats ADO. I too thought of a buffered write taking place but do you know of a way of forcing the commit in this situation? Your help will be greatly appreciated friend.

 

[gemma-the-husky]

no, i don't use ADO, and I'm not familiar with the ADO model.

I've had a look in access 2003 help. It says that when you use, as you have adLockBatchOptimistic setting, the writes are buffered, and you need to force the update with rspex.BATCHUPDATE

QED hopefully.

I found this in cxhapter 5 of the ado guide in A2003 help, (help from the CODE window)

 

[Chillgeek]

Oh my god ........ gemma ....... you are gem really ....... I just looked into my code and for some strange reason I am using adLockOptimistic instead of the usual adLockBatchOptimistic that I have been using all these years ........ thanks buddy !!