Outlook 2007 And Excel 2002 query problem

rpotterfrito · 07-09-2009, 09:30 PM

Having problem with Excel 2002 VBA Queries not refreshing. History: I work for a large company and we have almost everyone using Windows XP and Office XP. Recently we began upgrading everyone to Outlook 2007 (everything else unchanged). On computers that have migrated to OLK2007, the queries are halted ("run-time error") in Excel VBA. The VBA code has run for several years without a hitch and still works fine on non-migrated computers which are still on OLK2003. The application is fairly simple. The user pushes a button to update several sales reports from 5-6 underlying queries. The queries are not very large (<1000 rows) and the data is a local MS-Access .mdb (about 40mb). The reporting model is about 4mb when fully utilized. The queries were originally built using MS-Query. The code to update the queries is fairly simple. Ex: Range(QueryName).QueryTable.Refresh BackgroundQuery:=False. It was originally developed on latops with much less memory and it usually runs extremely fast.
Each iteration of the module runs about 8-10 small queries that are loaded into a vlookup table. On OLK2007 deployments, the application is halted with a "run-time" error, but not on the same query. It appears to be random as to which query will fail each time. Remarkably, sometimes (if you are lucky) it will work, but not very often. The only known workaround that I have been able to come up with is to reboot, do not open Outlook, run the queries and then start Outlook2007. The queries run fine untiil OLK2007 is opened and then the problem manifests again. While this workaround seems to help, it is very cumbersome to not have e-mail available and also rebooting to use the application is not a good solution. Curiously, it seems that laptops with more memory have a better chance of success, but even they eventually fail. The app was originally designed on older computers that had 256mb of memory and still runs on those computers (sans OLK2007). Again, computers on OLK2003 are not having this issue. Another option is to uninstall OLK2007 / reinstall OLK2003, but I can't find any downloads for OLK2003 on the MS website. Anyone got an idea on how to workaround? Our corporate tech support thinks we should upgrade everyone to Office2007, but the money guys are balking at the software costs and the training due to the user interface differences with Excel2007 vs legacy Excel. Looks like I'm stuck in the middle. Would reworking the queries via ADO sql strings (vs MSQuery help)? Help!

07-09-2009, 09:30 PM	#1 (permalink)
rpotterfrito Registered User Join Date: Jul 2009 Posts: 1 OS: win xp sp3	Outlook 2007 And Excel 2002 query problem Having problem with Excel 2002 VBA Queries not refreshing. History: I work for a large company and we have almost everyone using Windows XP and Office XP. Recently we began upgrading everyone to Outlook 2007 (everything else unchanged). On computers that have migrated to OLK2007, the queries are halted ("run-time error") in Excel VBA. The VBA code has run for several years without a hitch and still works fine on non-migrated computers which are still on OLK2003. The application is fairly simple. The user pushes a button to update several sales reports from 5-6 underlying queries. The queries are not very large (<1000 rows) and the data is a local MS-Access .mdb (about 40mb). The reporting model is about 4mb when fully utilized. The queries were originally built using MS-Query. The code to update the queries is fairly simple. Ex: Range(QueryName).QueryTable.Refresh BackgroundQuery:=False. It was originally developed on latops with much less memory and it usually runs extremely fast. Each iteration of the module runs about 8-10 small queries that are loaded into a vlookup table. On OLK2007 deployments, the application is halted with a "run-time" error, but not on the same query. It appears to be random as to which query will fail each time. Remarkably, sometimes (if you are lucky) it will work, but not very often. The only known workaround that I have been able to come up with is to reboot, do not open Outlook, run the queries and then start Outlook2007. The queries run fine untiil OLK2007 is opened and then the problem manifests again. While this workaround seems to help, it is very cumbersome to not have e-mail available and also rebooting to use the application is not a good solution. Curiously, it seems that laptops with more memory have a better chance of success, but even they eventually fail. The app was originally designed on older computers that had 256mb of memory and still runs on those computers (sans OLK2007). Again, computers on OLK2003 are not having this issue. Another option is to uninstall OLK2007 / reinstall OLK2003, but I can't find any downloads for OLK2003 on the MS website. Anyone got an idea on how to workaround? Our corporate tech support thinks we should upgrade everyone to Office2007, but the money guys are balking at the software costs and the training due to the user interface differences with Excel2007 vs legacy Excel. Looks like I'm stuck in the middle. Would reworking the queries via ADO sql strings (vs MSQuery help)? Help!