Solved

Excel 2003 connects to SQL2000 using DSN - Changing server

Posted on 2011-02-18
7
852 Views
Last Modified: 2012-05-11
Hi!

Current situation:

We have a number of Microsoft Excel 2003 workbooks on Windows XP that connect to a Windows 2000 server running SQL 2000 which use a system DSN to connect. We are upgrading to a new server (Windows 2008 Server running SQL2005)

What is the best way to get the Excel workbooks to connect to the new server? I have read quite a bit about this on the net and it seems that just changing the DSN information will not achieve this as connection information is imbedded in the Excel workbooks. I have found this:

How to change connection information for a query in Excel

Is this the right answer? Anything else I should be aware of? Is there an easier way? What are the best practices?

Thanks in advance

Cheers!
Steve
0
Comment
Question by:hal8999
  • 4
  • 2
7 Comments
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 34932648
Steve,
There should be no problem with you changing the DSN and effecting the update across the board provided the following points are adhered to:
The new source should have :
 - the same DSN
 - the same Description
 - the same UID & PW i.e. whatever is valid on the old source should be valid on the new
The old source should be deleted (or renamed until all is confirmed as working) from the System DSN lists
This is the standard method I use when various clients have changed servers. Except once, where the table structure was changed by their IT team without telling anyone, I have never had any issues this way.
Assuming that the queries are linked by DSN name, whether as a workbook query or via VBA macros, all should be good.
Have you already tried this method?
Regards
0
 

Author Comment

by:hal8999
ID: 34942716
Hi. Many thanks for your reply

I have not tried this method yet but will do so when I am on-site during this week. I will keep you posted but my initial thoughts seems to think that this'll work

Fingers crossed!
0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 34944493
Keeping fingers crossed for you. If not just let me know and I will set up a dummy server just to test.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:hal8999
ID: 35027117
Ok...

I tried what you suggested and it worked fine. However, I copied some workbooks to a clean XP machine that had no
DSN set-up whatsoever, opened a workbook and refreshed the data. It connected fine..!

I then opened the workbook in Notepad++ and searched for the server name and lo and behold, I found the following buried in the workbook:

DRIVER=SQL Server;SERVER=OLDSERVER2K;UID=USER;PWD=PASSWORD;APP=Microsoft Open Database Connectivity;WSID=CT_8;QueryLogTime=Yes

I had read that this info was 'hard coded' into Excel workbooks and found the following code:


This seems to work but do I really have to run this on every workbook?

It appears that once an Excel workbook is created with an ODBC link, the ODBC link is no longer needed and the workbook can be passed around to whomever. However, if there is a system DSN, the method described above will work as well

I've got some more experimenting to do...auurrgg!!



Sub ChangeConn

Dim qt As QueryTable
Dim wsh As Worksheet
Dim NewLoc As String
Dim LiveLoc As String

NewLoc = "NEWSERVER2K8"
LiveLoc = "OLDSERVER2K"

For Each wsh in ThisWorkbook.Worksheets
    For Each qt in wsh.QueryTables
        If InStr(1, qt.connection, NewLoc) > 0 Then
            qt.Connection = Replace(qt.Connection, NewLoc, LiveLoc)
        Else
            qt.Connection = Replace(qt.Connection, LiveLoc, NewLoc)
        End If
        
        qt.Refresh
    Next qt
Next wsh

End Sub

Open in new window

0
 

Accepted Solution

by:
hal8999 earned 0 total points
ID: 35324692
The only way to do what I needed was from this free Excel Add-in:

http://vangelder.orconhosting.net.nz/excel/queryeditor.html

Running the above code does fix some workbooks...but not others and I had to run this add-in and manually correct the connection string(s) for each worksheet

Thanks for any help!
0
 

Author Closing Comment

by:hal8999
ID: 35360821
Answered by OP
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem to open Excel file 15 43
Populate data based on a  criteria 2 16
Rearrange Macro 7 22
excel time calculation 11 22
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

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

16 Experts available now in Live!

Get 1:1 Help Now