Exporting query to excel does not refresh unless opened first.

[ser01]

I have a database that exports a few queries to excel with a lot of data in them. Before the queries are exported, the tables that the data comes from, have a lot of data added to them since the last export. The actual query is never opened, since the only purpose they serve is to process the data a certain way before it is exporte. The whole process is handled by VBA.



This does not seem to happen all the time. But at least some of the time, if the query is not opened before the export it does not export the updated data and instead what is exported is the data the query contained the last time it was opened. It all seems to be very inconsistent. Since this is all being handled by VBA it is no big deal to open the close the queries before the export. It is however rather inelegant.

So I have a few questions to the more experienced users:

1. Am correct the the queries do not update unless opened first? Is this an access bug, or normal behavior?
2. If the answer above is YES, what other ways are there to forse the query to refresh programmatically without opening/closing them first?

Thank you very much in advance.

[help_me_with_access]

wow, strange issue. The first thing you should know is that all interface-based objects in access, when referenced by vba as a collection, have a "refresh" method. try using it at various places in your code.

what exactly is handled by VBA? query object *creation* or just this export process?? were the objects created as static objects to just sit in the database window and never be replaced?? as, in you're *not" deleting and adding db window objects everytime the code runs, right??

try using .refresh for the collections first. I would guess that would work fine. the other refresh that I've used before is RefreshDatabaseWindow(). that's a built-in function for access only, but I don't believe that has anything to do with the data. I believe that's only used to update the content list in the database window itself. but try it anyway.

queries should *not* have to be opened in order to make new table data visible to it unless there's an outside intrusion that's got the query's attention from the start of your process to the end. like an object being locked, sort of...

[ser01]

This is very strange indeed. Like I said it does not happen all the time, but it does happen. I will try .Refresh of the query object and see what happens.
Thank you for your reply.

[help_me_with_access]

let us know either way.