Solved

MSQL Script in Excel will work in SQL 2005

Posted on 2009-07-14
8
192 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
  • 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 250 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 250 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now