Update Access Table From VB Issue! 1

[GarstonMassive]

Hi a number of tables within my A97 app are updated by a VB app.

I have a number of forms bound to these tables, the idea being that once the tables are populated the form opens displaying the info. However what I'm finding fairly regularly is that the form will open displaying the data from the previous search i.e the previous record which has been deleted. Doesn't happen all the time but often enough.

It's almost as though the previous data is hanging around in memory. I thought about refreshing/requerying the tables but it seems you can only refresh the links and these tables are local.

Has anyone else either had a problem like this or knows what I could do?

 

[PHV]

Seems like a cache issue.
Have you tried to close and reopen the connection ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fneily]

How about putting Form.Requery on the OnOpen event? Since I can't duplicate the problem, I can't test it.

 

[GarstonMassive]

Ah now you mention it my laptop is a Celeron which I believe doesn't have on-board cache. Could this have something to do with it?

Also are you suggesting that I open and close the connection each time I send data to my Access app? Wouldn't it already be doing this after it finishes updating the last table?

This problem could be linked to the other problems I'm having with update queries not working as if Access doesnt see any records in the table it wont update them.

Interestingly this only happens at runtime; if I step through the code it ALWAYS works.

 

[GarstonMassive]

Thanks for your reply fneily but I've already tried this with no joy.

 

[abaldwin]

I run into this occasionally with vb apps that update records, rebuild a grid or form and the updated field is not showing the data. If I close the app and open it back up the data is there. I have found this to be a timing issue. The app was sending the update statement (which executed) and then immediately querying the table to retrieve thedata and write to a grid (disconnected recordset).

If I put a pause of about 5 seconds after the write / before the rebuild of the grid all is fine.

Sounds like you are having the same issue. After the update you may need to pause before the form is opened to display records.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[PHV]

In your VB app you use DAO or ADO ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cmmrfrds]

Also, on your recordset, what is the cursor type, cursor location, lock type and other properties set on the recordset.

 

[GarstonMassive]

Thanks for all your replies guys. Let me deal with these in turn:

1) Considered putting a delay on a timer event and could be the solution. Rather not though as a bit crude. Supporting evidence is that it always works when stepping through.

2) VB app uses ADO.

3) ADO Recordset properties within the VB app that open the Access tables are: adOpenStatic and adLockBatchOptimistic. The cursor location is adUseClient. Sorry not sure what catergories the other options fall into.

Is there anything I could try changing here?

 

[abaldwin]

I have found that when stepping through code you are giving the db time to reflect the update. When the code is running ti is going much faster than your stepping.

Also try it with adlockoptimistic instead of batchoptimistic.


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[GarstonMassive]

Andy I've put a 5 second delay in the Timer event and it's done the trick!

Ridiculous that something so crude should fix it but at least it's working.

Cheers you fellas for your help.

 

[abaldwin]

Just a note. Running MSDE as the backend vs Access for the same application on the same machine does not give me the issue.

Access is just slower posting its updates. Have not had this issue with insert statements, just deletes and updates.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[cmmrfrds]

This should have fixed it.

adlockoptimistic instead of adbatchoptimistic.

 

[GarstonMassive]

As a footnote though my VB app (which was authored by someone else) does a :

.BatchUpdate

later on in the code. To the uninitiated (me) it seems sensible to open the recorset using the adBatchLockOptimistic argument...

What d'ya say?

 

[cmmrfrds]

If you want the update to be reflected in the database at the end of a batch. Let's say there are 10 records entered on the screen and then the .updatebatch is done. At this point the records updates would be in the database. If you want each update to be reflected when it is entered then do not use the batch. instead use .update. The updatebatch makes more sense in a Web App, but can be used in VB. If you are doing the batchupdate after every record then there is no point in it.

 

[GarstonMassive]

Thanks for your input cmmrfrds.

I'll do some experimenting and get back to you by way of follow-up.

 

[PHV]

.BatchUpdate
Property/Method of which object ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GarstonMassive]

...of an ADODB Recordset.

 

[PHV]

I guess you meant UpdateBatch ?
and thus my aforementioned Cache issue ...
You may have a look at the Resync and Update methods instead.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GarstonMassive]

Yes PHV you're quite right. Typo on my part.

Does the fact that it works now that I've put a timer delay in support your hypothesis?