noel412
asked on
MSQL Script in Excel will work in SQL 2005
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..
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..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I object,
3 different experts attempt to help, points should be distributed.
cheers
cslarsen
3 different experts attempt to help, points should be distributed.
cheers
cslarsen
Recommend #4
Points split on replies 2 and 3
thx
cslarsen
Points split on replies 2 and 3
thx
cslarsen
cslarsen,
I'm happy, at least I was able to add your suggested url to my cool tip toolbox!
thx
Tom.
I'm happy, at least I was able to add your suggested url to my cool tip toolbox!
thx
Tom.
All I have to do is to press the VB button.