Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Server for Query
I have a spreadsheet that uses 20+ queries to access an SQL database. I want
to run the spreadsheet from multiple systems (backups, etc.) to which I can, when needed, restore the database. Each backup system will have a new server name as each has a unique WorkStation ID. How do I update the queries to point to the new server? I tried updating the queries in the Microsoft Query directory but they are not accessed by the spreadsheet which seems to have its own imbedded copies. I did update and run successfully three queries that were initially created in one of my macros - I just changed the WSID in the macro. I found a posting that showed some VBA code to change the server in a Pivot Table. Is there something similar for a Query? If not, is there some way to avoid having to respond with the new server name when the spreadsheet runs on a backup system? I have considered creating a macro with all the queries and running that on the backup. Would that accomplish my purpose of initializing the queries to point to the new server? Thanks for your guidance on addressing this request. I would like to do this in the simplest and fastest way. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Server for Query
The connection property of a query cannot be changed. You need to delete the
query and recreate the query.You can get the other properties of the query to make up a new query. I would like to see the code you found for a pivot table. It probably can be modified for a query table. The Comand Text portion of the qury is the SQL method. You may want to use an Access Application in excel to gewt the data rather than use queries. Any ACCESS VBA instruction can be used in excel. See access VBA help. First open an access application Set obj = createobject("Access.Application") or Set obj = Getobject(filename:=database.mdb") You also have to add areference to your excel vba project from menu Tools - References - Microsoft Access 11.0 Object library (or latest version on your PC) You also may hae to ad aditional references depending on the method you choose. Another choice is to have your macro run in access and export the data to excel. "Cinco" wrote: I have a spreadsheet that uses 20+ queries to access an SQL database. I want to run the spreadsheet from multiple systems (backups, etc.) to which I can, when needed, restore the database. Each backup system will have a new server name as each has a unique WorkStation ID. How do I update the queries to point to the new server? I tried updating the queries in the Microsoft Query directory but they are not accessed by the spreadsheet which seems to have its own imbedded copies. I did update and run successfully three queries that were initially created in one of my macros - I just changed the WSID in the macro. I found a posting that showed some VBA code to change the server in a Pivot Table. Is there something similar for a Query? If not, is there some way to avoid having to respond with the new server name when the spreadsheet runs on a backup system? I have considered creating a macro with all the queries and running that on the backup. Would that accomplish my purpose of initializing the queries to point to the new server? Thanks for your guidance on addressing this request. I would like to do this in the simplest and fastest way. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Server for Query
Joel,
Thanks for the explanation of how query connection works. Here is the code that I found for a Pivot Table. If you can change it to work with a Query Table, I would be thankful. Jim Subject: External Data Query 9/4/2006 6:36 AM PST By: Carim In: microsoft.public.excel.misc Marie, You are right ... View Code and then the icon Object Browser ... Meanwhile, found the following VBA code to ChangeServer Sub ChangeServer() 'Declare your variables. Dim ptc As PivotCache, oldSrv As String, newSrv As String 'Request the name of the old server/file name. oldSrv = InputBox("Input the name of the old server or file path as listed in the Pivot Tables SQL string.") 'Request the name of the new server/file name. newSrv = InputBox("Input the name of the new server or file path which you want the Pivot Table to point to.") 'Replace the ODBC information of whatever PivotTable is currently active. Set ptc = ActiveCell.PivotTable.PivotCache ptc.Connection = Application.Substitute(ptc.Connection, oldSrv, newSrv) ptc.Sql = Application.Substitute(ptc.Sql, oldSrv, newSrv) End Sub HTH Cheers Carim "Joel" wrote: The connection property of a query cannot be changed. You need to delete the query and recreate the query.You can get the other properties of the query to make up a new query. I would like to see the code you found for a pivot table. It probably can be modified for a query table. The Comand Text portion of the qury is the SQL method. You may want to use an Access Application in excel to gewt the data rather than use queries. Any ACCESS VBA instruction can be used in excel. See access VBA help. First open an access application Set obj = createobject("Access.Application") or Set obj = Getobject(filename:=database.mdb") You also have to add areference to your excel vba project from menu Tools - References - Microsoft Access 11.0 Object library (or latest version on your PC) You also may hae to ad aditional references depending on the method you choose. Another choice is to have your macro run in access and export the data to excel. "Cinco" wrote: I have a spreadsheet that uses 20+ queries to access an SQL database. I want to run the spreadsheet from multiple systems (backups, etc.) to which I can, when needed, restore the database. Each backup system will have a new server name as each has a unique WorkStation ID. How do I update the queries to point to the new server? I tried updating the queries in the Microsoft Query directory but they are not accessed by the spreadsheet which seems to have its own imbedded copies. I did update and run successfully three queries that were initially created in one of my macros - I just changed the WSID in the macro. I found a posting that showed some VBA code to change the server in a Pivot Table. Is there something similar for a Query? If not, is there some way to avoid having to respond with the new server name when the spreadsheet runs on a backup system? I have considered creating a macro with all the queries and running that on the backup. Would that accomplish my purpose of initializing the queries to point to the new server? Thanks for your guidance on addressing this request. I would like to do this in the simplest and fastest way. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Server for Query
Joel,
A few days ago, I found the answer to my question in a posting by K. Dales dated 2/14/2005. He wrote: The Query Table Connection property contains the connection string. I have never tried changing the connection this way but it should be possible. I would go to the immediate pane and try this: ?Sheets("SheetName").QueryTables(1).Connection to see what the current string looks like. Then use: Sheets("SheetName").QueryTables(1).Connection = "modified current string" to reset the connection parameters. I am now using this approach in a Macro to change my 20 queries when I move my spreadsheet from one PC/Server to another. It takes about a second and works Great! Jim "Joel" wrote: The connection property of a query cannot be changed. You need to delete the query and recreate the query.You can get the other properties of the query to make up a new query. I would like to see the code you found for a pivot table. It probably can be modified for a query table. The Comand Text portion of the qury is the SQL method. You may want to use an Access Application in excel to gewt the data rather than use queries. Any ACCESS VBA instruction can be used in excel. See access VBA help. First open an access application Set obj = createobject("Access.Application") or Set obj = Getobject(filename:=database.mdb") You also have to add areference to your excel vba project from menu Tools - References - Microsoft Access 11.0 Object library (or latest version on your PC) You also may hae to ad aditional references depending on the method you choose. Another choice is to have your macro run in access and export the data to excel. "Cinco" wrote: I have a spreadsheet that uses 20+ queries to access an SQL database. I want to run the spreadsheet from multiple systems (backups, etc.) to which I can, when needed, restore the database. Each backup system will have a new server name as each has a unique WorkStation ID. How do I update the queries to point to the new server? I tried updating the queries in the Microsoft Query directory but they are not accessed by the spreadsheet which seems to have its own imbedded copies. I did update and run successfully three queries that were initially created in one of my macros - I just changed the WSID in the macro. I found a posting that showed some VBA code to change the server in a Pivot Table. Is there something similar for a Query? If not, is there some way to avoid having to respond with the new server name when the spreadsheet runs on a backup system? I have considered creating a macro with all the queries and running that on the backup. Would that accomplish my purpose of initializing the queries to point to the new server? Thanks for your guidance on addressing this request. I would like to do this in the simplest and fastest way. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel with SQL Query (*.sql) and SQL Server | Excel Discussion (Misc queries) | |||
Query SQL Server from Excel | Excel Discussion (Misc queries) | |||
Query SQL Server from Excel | Excel Worksheet Functions | |||
Changing Server on a Microsoft Query | Excel Discussion (Misc queries) | |||
How do I run a SQL query against a MS SQL Server database from exc | Excel Discussion (Misc queries) |