MSQL Script in Excel will work in SQL 2005
Posted on 2009-07-14
Hello Guru. I hope this wont twist your mind. I have more than 1000 Excel files piled up over the years each file is running a microsoft query script from Access DB the tables data is being populated by running a batch file (DTS Pacakge) in SQL Database. In short the data is comming from SQL and dumping into Access Database and being queried by all Excel Files.
My problem is, the Access Database has reached now to 2GB and from time to time the DTS package would not run giving an unknown error, this probably the access db size limitation has already reached.
So, I was wondering without modifying each and every query script used in Excel file I can generate this Access tables inside SQL Database itself and make an ODBC connection and let Excel file read those tables in SQL using the same query script, am I making sense guru?
Do you think the same Excel query script would be able read the newly generated tables in SQL? Or, do you have any other permanent solution how I can prolong the life of my Access Database? Till, I get the time to convert all the Excel Scripts to read directly to SQL.
Ok, I got this idea, may I could make a path for Excel file to read those tables through Access DB but Im actually going to read SQL tables?
You are my GURUs just let me know what is the best way to do it.
If you already have this case before I appreciated if you could pass me the solution.
Thanks for your patience..