Run "make table" query from Excel

[scotty2000]

This one is starting to get me down!

I've got a background Access database with an Excel front end report.

I run a "make table" query in my Access database to make sure I have the most up to date information from our data warehouse.

I then run a couple of other "select" queries from that table that are linked to my excel front end.

When I refresh the data in Excel, it runs my select queries with no problem, however I need to run the "make table" query first.
I know I could change the "make table" to a "select" query but it means that each time I run my other select queries it has to get the data again from the data warehouse which takes ages.

I'd like to do this without having to open the database and have tried all sorts.

I'm using Excel 2003 and Access 2000

Any ideas?

 

[villy]

hey xenou
I tried your code and substitute my database location but gives me an error 7866:
Microsoft office Access can't open the database because it is missing, or opened exclusively by another user.
Where as it is actually in the location and nobody is using it.

Can you tel me the solution please
Thanks

 

[xenou]

Not sure. Make sure there is no lock file and try again. If you have put this code into some larger project it could be a conflict with some other code you are running. I personally don't use make-table queries very often. My preferences would be to have a permanent table that is cleared of data and reloaded (run a delete query and an append query, rather than a make-table query).

 

[villy]

Sounds interesting.. Can you show me how?
Thanks