SQL Excel

Thanks for offering such a great program for free!

When I run a simple SQL query on an Excel database, the resultset returns extremely quickly (much faster than using SQL in VBA with an ADO connection string). I then click the paste button, and my resultset is immediately placed in a new Excel workbook. This is great!

When I run a more comple SQL query on an Excel database, the resultset returns fairly quickly (at least as fast and probably faster than using ADO). BUT when I click the paste button, my resultset is LOST, because the computer freezes up.

I believe this is due to a memory problem...that is, the computer used up so much memory to run the SQL query, that it doesn't have enough left in order to copy and paste the resultset (300-500 rows) into a new Excel workbook.

Do you have any fixes for this? It would be ideal if there was a way to clear the memory in Excel, but I am not aware of any way to do this without closing/re-opening Excel.

Thanks in advance with any ideas.

Hi again Pilotctk !

Thanks vm for the feedback...

This is a strange issue.

Within the addin, please can you try to change a few of the options - paste to the active cell, apply no formatting, and apply no auto filter. These options will minimize what the paste does.

I don't think it will make a difference though unfortunately. These options should not take up much memory.. But please can you try when you have time.

It would be great if you can see if you can try the paste on a different machine. This problem sounds like it could be machine specific - sorry.. Alot of testing was done and I have pasted more than 500 K records with Excel 2007... Also, alot of testing was done on making sure all used objects were cleaned up..

For Excel, there are somethings that can cause problems. Having alot of XLL addins loaded or having very pivot table caches are two items.

Please can you try if possible on a different machine (sorry for asking..).. Also, on your machine, if possible please remove all tmp files and also please check that your XLStart Folder has no unwanted files.

Please let me know how it goes..

Thanks, Al