ODBC takes time to update
Hello,
I have a macro that connects to an orocale database with ODBC and passes the database a query and the result is that data is pasted into excel. I then take that pasted data and do all sorts of stuff with it. The problem is that the data takes a few (perhaps 2) seconds to appear in the excel spreadsheet as the connection to the database is verified and the data retrieved and then the spreadsheet 'refreshed' (?). This is a problem because the macro runs faster than the database connection so by the time the macro has executed the data retrieve code and is on to the data maniluplation code there is no data to work with and it errors out. I have got around this by having two macros - the first pulls down the data and then the second, when i run it manually with a button a few seconds later, does the stuff with the data but I would liek to combine this into one macro. How can I make excel wait until the data has been downloaded? I have tried to use the wait() command but that seems to put all of excel on hold (including the data dump sequence - even if i insert the wait() command after the data should have been successfully dumped into the sheet). I guess that i could use a do until loop like this (in psuedo code) Do until (timer circuit breaker = true) or isempty(activecell.value) = false if the loop has run for 10 seconds then there is a problem there timer circuit breaker = true loop Doesnt seem like a very elegant soln. Help much appreciated. thank you |
ODBC takes time to update
The query has a backgroundquery property. Set it to false and the code will
wait for the query to finish. It is a property of the querytable object. -- Regards, Tom Ogilvy "kaiser" wrote: Hello, I have a macro that connects to an orocale database with ODBC and passes the database a query and the result is that data is pasted into excel. I then take that pasted data and do all sorts of stuff with it. The problem is that the data takes a few (perhaps 2) seconds to appear in the excel spreadsheet as the connection to the database is verified and the data retrieved and then the spreadsheet 'refreshed' (?). This is a problem because the macro runs faster than the database connection so by the time the macro has executed the data retrieve code and is on to the data maniluplation code there is no data to work with and it errors out. I have got around this by having two macros - the first pulls down the data and then the second, when i run it manually with a button a few seconds later, does the stuff with the data but I would liek to combine this into one macro. How can I make excel wait until the data has been downloaded? I have tried to use the wait() command but that seems to put all of excel on hold (including the data dump sequence - even if i insert the wait() command after the data should have been successfully dumped into the sheet). I guess that i could use a do until loop like this (in psuedo code) Do until (timer circuit breaker = true) or isempty(activecell.value) = false if the loop has run for 10 seconds then there is a problem there timer circuit breaker = true loop Doesnt seem like a very elegant soln. Help much appreciated. thank you |
ODBC takes time to update
On Jun 12, 8:09 pm, Tom Ogilvy
wrote: The query has a backgroundquery property. Set it to false and the code will wait for the query to finish. It is a property of the querytable object. -- Regards, Tom Ogilvy "kaiser" wrote: Hello, I have a macro that connects to an orocale database with ODBC and passes the database a query and the result is that data is pasted into excel. I then take that pasted data and do all sorts of stuff with it. The problem is that the data takes a few (perhaps 2) seconds to appear in the excel spreadsheet as the connection to the database is verified and the data retrieved and then the spreadsheet 'refreshed' (?). This is a problem because the macro runs faster than the database connection so by the time the macro has executed the data retrieve code and is on to the data maniluplation code there is no data to work with and it errors out. I have got around this by having two macros - the first pulls down the data and then the second, when i run it manually with a button a few seconds later, does the stuff with the data but I would liek to combine this into one macro. How can I make excel wait until the data has been downloaded? I have tried to use the wait() command but that seems to put all of excel on hold (including the data dump sequence - even if i insert the wait() command after the data should have been successfully dumped into the sheet). I guess that i could use a do until loop like this (in psuedo code) Do until (timer circuit breaker = true) or isempty(activecell.value) = false if the loop has run for 10 seconds then there is a problem there timer circuit breaker = true loop Doesnt seem like a very elegant soln. Help much appreciated. thank you- Hide quoted text - - Show quoted text - Great! Thanks for the help |
All times are GMT +1. The time now is 03:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com