Solved

MSQL Script in Excel will work in SQL 2005

Posted on 2009-07-14
8
197 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

821 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