Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MSQL Script in Excel will work in SQL 2005

Posted on 2009-07-14
8
Medium Priority
?
202 Views
Last Modified: 2013-11-30
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..
 
0
Comment
Question by:noel412
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
8 Comments
 

Expert Comment

by:zorawar_bahadur
ID: 24856669
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
 
LVL 15

Accepted Solution

by:
CSLARSEN earned 750 total points
ID: 24856677
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
 
LVL 19

Assisted Solution

by:folderol
folderol earned 750 total points
ID: 24857103
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:CSLARSEN
ID: 24866682
I object,
3 different experts attempt to help, points should be distributed.
cheers
cslarsen
0
 
LVL 15

Expert Comment

by:CSLARSEN
ID: 24873870
Recommend #4
Points split on replies 2 and 3
thx
cslarsen
0
 
LVL 19

Expert Comment

by:folderol
ID: 24880499
cslarsen,
I'm happy, at least I was able to add your suggested url to my cool tip toolbox!
thx
Tom.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question