Form won't requery

[JHB]

If the DoEvents doesn't help either, could you post a stripped down version of your database with some sample data + how to reproduce the problem you've?

 

[rede96]

Just as an update, when I add the split tables back into the front end, the problem goes away. ?????

So it must be something to do with spliting the database into a back end and front end.

 

[pbaldy]

Did you try DoEvents, in case it's a timing issue?

 

[JHB]

Try what pbaldy writes!
Only for "error" finding:
Do you've the Front- and Backend in the same folder, else try it.
Try it on another computer.
I don't think a "Compact & Repair" does solve your problem, but it doesn't any harm to do it.
Why not post a stripped version of your database with some sample data + a description how to reproduce the problem you've?

 

[The_Doc_Man]

when I add the split tables back into the front end, the problem goes away

This adds some spice to the mix...

When you have a split FE/BE situation, you have something extra - the network. We sometimes describe the idea of opening a recordset from FE to BE as a "persistent" connection. This persistent connection is usually something simple like a select query to a one-record scratch table. We only close the persistent connection when we are about to close the FE file (and thus release the BE). But I can say with absolute certainty (having tripped over this too many times) that frequently opening and closing all connections from FE to BE is a slow process.

It gets worse when your network isn't yours to control. For example, if you have an encryption requirement imposed by your local security folks, frequently opening and closing a DB requires you to renegotiate every aspect of the connection including such things as key exchanges and credential exchange - unless you happen to include a persistent connection that Access can use for subsequent connections.

Another issue is the nature of the linking between the FE and BE. I learned the hard way (from answering too many frickin' dialogs) that you get the best results if you link the BE tables but you create FE single-table queries to actually do anything in the BE. The queries remember where the tables are located and are silent when you open them.

One last "gotcha" is the depth of the path on the BE side. While I would try very hard to avoid putting a table in the top-level directory (i.e. C:\database.MDB), I will say that every layer of directories means one more set of access (little-a) arbitrations. We try to put our BE files at no more than 3rd-layer deep, as e.g.

D:\share\production\backend\thedb.mdb

No single factor I have discussed will guarantee massive performance reclamation, but failing to address each of those issues will drag down performance a little bit when considering FE/BE split databases. I'm sure my colleagues here can add further factors to the mix.

 

[gemma-the-husky]

the problem may be one of timing. when you open a popup form, you need to wait for the popup form to close, before you can use the data


so

code block
open popupform
wait for popupform to close
refresh data (eg simply me.requery)
code block

 

[The_Doc_Man]

Interesting and true point, Dave. Form_Close and Form_Unload are not cheap events because a lot of the objects on that form have to be reclaimed from memory (or, in programming terms, have to go through object destructors). If they are bound, there is a recordset to resynchronize. The FE/BE split only slows down that process further.

 

[gemma-the-husky]

DocMan

I meant more that if the OP isn't waiting for the popup to close, (either with code, or by opening the form in dialog mode) then his code will just continue, and therefore mot immediately acknowledge anything that happens in the popup.

 

[rede96]

Did you try DoEvents, in case it's a timing issue?

Sorry for the late reply, been away for the week.

Yes I did try the DoEvents but it didn't work. Thanks anyway.

 

[rede96]

Why not post a stripped version of your database with some sample data + a description how to reproduce the problem you've?

I am doing a rebuild next week and trying a different structure to see if this solves the issue. But if not then I'll post a striped down version.

The only thing that seems to cure the issue to not split the database. But lets see how I go on after the re-build.

 

[rede96]

the problem may be one of timing. when you open a popup form, you need to wait for the popup form to close, before you can use the data


so

code block
open popupform
wait for popupform to close
refresh data (eg simply me.requery)
code block

Thanks for the tip but I had already tried this. I put the requery in the Ontimer event of the main form. But it still has to fire 2 or 3 times before the new data shows. Also the problem isn't just with the pop up. I also have some code that runs directly from the main form but it still doesn't update the data in forms record source unless I requery 2 or 3 times.

So I will try a rebuild and see if I can accidently fix it

 

[rede96]

This adds some spice to the mix...

When you have a split FE/BE situation, you have something extra - the network. We sometimes describe the idea of opening a recordset from FE to BE as a "persistent" connection. This persistent connection is usually something simple like a select query to a one-record scratch table. We only close the persistent connection when we are about to close the FE file (and thus release the BE). But I can say with absolute certainty (having tripped over this too many times) that frequently opening and closing all connections from FE to BE is a slow process.

It gets worse when your network isn't yours to control. For example, if you have an encryption requirement imposed by your local security folks, frequently opening and closing a DB requires you to renegotiate every aspect of the connection including such things as key exchanges and credential exchange - unless you happen to include a persistent connection that Access can use for subsequent connections.

Another issue is the nature of the linking between the FE and BE. I learned the hard way (from answering too many frickin' dialogs) that you get the best results if you link the BE tables but you create FE single-table queries to actually do anything in the BE. The queries remember where the tables are located and are silent when you open them.

One last "gotcha" is the depth of the path on the BE side. While I would try very hard to avoid putting a table in the top-level directory (i.e. C:\database.MDB), I will say that every layer of directories means one more set of access (little-a) arbitrations. We try to put our BE files at no more than 3rd-layer deep, as e.g.

D:\share\production\backend\thedb.mdb

No single factor I have discussed will guarantee massive performance reclamation, but failing to address each of those issues will drag down performance a little bit when considering FE/BE split databases. I'm sure my colleagues here can add further factors to the mix.

Thanks Doc_Man, some great tips there. Do you mind if I ask what you meant by single table queries in the FE? Do you mean create a query linked to just one of the tables in the back end and then call on the query to edit / add new?

Also when you say frequently opening and closing all connections, how frequent do you mean? Data in the BE tables is usually only updated / added around once every 20 minutes or so. BUT I do open and close about 3 connections through the recordset method each time the code runs an update. Do you think this might have something to do with it?

 

[vbaInet]

Very good pointers from everyone. May I just add one more.

I have a continuous form who's record source us a query I use for summarising data in the form detail.

Can we possibly see the SQL of the query that's bound to this form? When you have many joined tables in a query it can take a while to refresh, and considering that this is a summarised view I'm guessing this is a fairly complex query. Furthermore, if some of the joined tables are not linked in the Relationships Manager it also sometimes have adverse effects on performance.

Lastly, if you were using bound forms the connection will be persistent, but because you're using DAO you're having to bounce back and forth to the backend... The_Doc_Man has covered this in detail.