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..
 
noel412Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorawar_bahadurCommented:
I am not sure what your requirements are but I have a VB macro in my excel sheet that queries a MS SQL server 2005 and retrieves all the info and displays it in the excel file.

All I have to do is to press the VB button.
0
CSLARSENCommented:
Hi

It is actually not a lot of work.
What you need to do is:
- Generate the tables in SQL (You could generate the tables with the same name as they have it in the access db thereby allowing you to just change the connection string)
- Alter the connection string of your excel file querytable connection to your SQL database
Your current connection string is probably a Jet database engine driven ODBC, but it is not too bad to change it.
See a good overview here:
http://www.connectionstrings.com/

Cheers
cslarsen
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
folderolCommented:
Do you plan to copy the Access tables back to SQL?  If so, I would start with a DTS package to import the Access tables.  This will make and run for you the SQL create tables scripts so you won't have to do that part.  Second, you need to write the stored procedure that will update the reporting tables (formerly in Access, now in sql), this is the replacement for the old dts package that updated Access.
Third, you need to update the connection strings, and commandtext of all your Excel files.  You can do that with a "find / replace" type of operation using a VBA macro.  If you make a connection query to SQL for your Excel, then the snippet below will display the connection string.  You can use this string in another macro to replace the old Access connection string in the Excel workbooks.  You can do the same thing with the commandtext.

Tom.
Sub macro2()
    MsgBox ActiveSheet.QueryTables(1).Connection
End Sub

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

CSLARSENCommented:
I object,
3 different experts attempt to help, points should be distributed.
cheers
cslarsen
0
CSLARSENCommented:
Recommend #4
Points split on replies 2 and 3
thx
cslarsen
0
folderolCommented:
cslarsen,
I'm happy, at least I was able to add your suggested url to my cool tip toolbox!
thx
Tom.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.