Refreshing queries based on tables linked to sharepoint (1 Viewer)

Hi

I am working on a DB which has various tables linked to sharepoint. The DB has queries using data in these tables, which queries I then use to provide data for list boxes in different forms.

DB is used by various users, so it can be the case that the data in sharepoint changes, and a user would need to update the contents of the list box in the form being used. So far user would need to exit the form and wait for the updated data to be reflected in the query used by the particular form, or else use the refresh all button in the ribbon.

I would like to add a button in each form which refreshed the linked table, reruns the query and reloads the form so that the list box content gets updated. I have tried various options but none seem to work fully.

To refresh the linked table I am using the following:
dbs.TableDefs("Linked Table Name").RefreshLink

To rerun the query (a select query) I have tried various options but none seem to work properly.

How should I be doing this, pls?

normally the links do not change.
unless you are constantly changing the links, there is reason to refresh.
you CAN refresh if you want.
just running a query, pulls current data.

Hi thanks. IN fact I usually use the refresh list from drop down menu for the tables of the navigation pane when I change any structure of any lists.

However I still have not found a way of running the query through VBA. When I open the query from the navigation pane it does not automatically reflect the changes in the list and I normally need to press the refresh all button to see the change. I need to find a way of forcing this programmatically so when I reload the list box it shows the updated data.

The normal way to open a query in VBA would be
"
But to force a requery of your listbox based on that query it would be;

Thanks Minty

That works, but opens the query behind the form. And I do not want/need that. Is there a way of forcing it to open without being seen, or to recalculate the query without opening it at all?

I am using VBA code to populate the listbox items since I am only putting some of the fields from the query in the listbox and I found it easier to control the contents of the listbox that way, rather then bounding the listbox to the query through the control source of the list box.

Ah - in that case you can't refresh / requery the listbox without re-running that code, as it not actually bound to that query.

If you put the code to populate the listbox in a sub on the form, then call that on form load and add a refresh button that also calls it.

No that's ok. I am rerunning the code and that is not an issue.

But before I rerun the code I need to rerun the query itself. Opening the query does the job but I would prefer if I do not need to open it. Anyway if I open it I then need the Docmd.close ("form name") to close it?

Are you sure you need to re-run the query?
Normally referencing a query / table always returns the "live" data?

Or - lateral thinking head on - bind the form to the query, even if you don't display any data from it and requery the form then reload the list box...?

as I said when I change something in a sharepoint list and I then open the query from the navigation pane manually, the data in the query is not updated yet... unless some minutes have passed. So it seems like there is some lag from the time the list in sharepoint is updated and when those changes are reflected in access tables and dependent queries.

When this happens I need to press the refresh all button (refresh only does not work), and that is what I want to replicate. Open query works, or at least worked in the 2 times I tested it within seconds after I changed data in list on sharepoint.

I will try the idea of binding the query to the form and requerying the form.