[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

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..
 
0
noel412
Asked:
noel412
  • 3
  • 2
2 Solutions
 
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
 
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now