I have been bowled a googly by one of my clients' software providers.

I have built a significant number of Excel applications, some of which operate automatically each day, to get data in the form that the clients want from the SQL database. The main software had an issue with the Live Company database which it did not have on the Test Company database even with data restored from the Live Company backup. So a decision was made to create a completely new database in which to store the Live Company data for their program to access. The new database had a different name from the old one and could only be accessed by their program. Nevertheless, they added 'Old' to the original Live database.

I went through all of my Excel applications and edited the queries to refer to the new database name. However, this only works if the original  database has the same name as when the query was created. I cannot seem to locate, within the Excel worksheets and associated queries, where the original SQL database is named other than in the SQL script. If I take off the 'Old' from the original database name via the Management Studio, all of my queries work. However, this change keeps getting reversed by the software providers.

The sensible course would be for me to have a dialogue with the providers and ask them to stop renaming the original database. However, I would like to understand if there is a way that I can get my MSQueries to not look at the original database at all - I am not comfortable with all of my work being subject to the whims of someone else.

Hi Pedro,

Without knowing how you are connecting to the server I can only suggest you try :

Check the DSN settings?

Refresh / reconfigure the connections?

Have you tried to create a new connection or query?

Hi Brian,

The queries go direct to the SQL server on the network using a machine data source on the SQL Server template. There is no issue with the server connection and getting the data from the new source database, provided that the old source database retains its original name. The problem is within the Excel files where the old source database seems to be embedded in the file where it is not editable. The only way that I can actually see it (with my limited knowledge) is to open the file with Notepad and find these references that way. Trying to edit them there, of course, corrupts the file.

I can create a new query which will not contain the reference to the old database without any problem. However, I was seeking to avoid re-writing them all because I do not really have the time and the client will not really want to pay me!!

Regards

Peter

Hi Peter,

Try this...

On the Data Tab, Click Connections, Select your connection, Click Properties, Click Definition.

Review the Connection String and edit it to you new database name.

Thank you Brian. Sorted

Kind regards

Peter