MS Query Data Source Change
I have a series of saved queries that I use on an ODBC data source. I would
like to put them on other computers that may not have the same data sources but have different ones (tables are the same in each data source). Right now I am editing the query in Notepad to change the data source and path to the correct one on the other computers but am wondering why it is not evident how to change this in the query itself. Can it be done? How? TIA |
I had a similiar problem which I solved with a form/macro. (I don't think you
can point to a different source other than manually changing the code.) 1. Construct a from with 4 field: Old and new connection strings, old and new query. 2. When activating the form, populate the "old" fields with the data like this: (assuming you have selected the query before you activate the form) OldConnection = Selection.QueryTable.Connection OldQuery = Selection.QueryTable.CommandText (If it is a Pivot Table, use this: OldConnection = Selection.PivotTable.PivotCache.Connection OldQuery = Selection.PivotTable.PivotCache.CommandText) 3. Write some code to populate the new files. For example: NewConnection = Replace(OldConnection, "OldODBC", "NewODBC") 4. Add a button that approves of changing the connection and query strings: Selection.QueryTable.Connection = NewConnection Selection.QueryTable.CommandText = NewQuery (If Pivot Table: Selection.PivotTable.PivotCache.Connection = NewConnection Selection.PivotTable.PivotCache.CommandText = NewQuery) 5. To make the change, select the query/pivot, activate the form, check that the new connection/query strings look allright and then click OK/Approve. I don't know if this helps, but for me this saved hours of changing query strings. /Marcus "rasinc" wrote: I have a series of saved queries that I use on an ODBC data source. I would like to put them on other computers that may not have the same data sources but have different ones (tables are the same in each data source). Right now I am editing the query in Notepad to change the data source and path to the correct one on the other computers but am wondering why it is not evident how to change this in the query itself. Can it be done? How? TIA |
Thanks. I'll try your code when I get a chance.
"Marcus Langell" wrote: I had a similiar problem which I solved with a form/macro. (I don't think you can point to a different source other than manually changing the code.) 1. Construct a from with 4 field: Old and new connection strings, old and new query. 2. When activating the form, populate the "old" fields with the data like this: (assuming you have selected the query before you activate the form) OldConnection = Selection.QueryTable.Connection OldQuery = Selection.QueryTable.CommandText (If it is a Pivot Table, use this: OldConnection = Selection.PivotTable.PivotCache.Connection OldQuery = Selection.PivotTable.PivotCache.CommandText) 3. Write some code to populate the new files. For example: NewConnection = Replace(OldConnection, "OldODBC", "NewODBC") 4. Add a button that approves of changing the connection and query strings: Selection.QueryTable.Connection = NewConnection Selection.QueryTable.CommandText = NewQuery (If Pivot Table: Selection.PivotTable.PivotCache.Connection = NewConnection Selection.PivotTable.PivotCache.CommandText = NewQuery) 5. To make the change, select the query/pivot, activate the form, check that the new connection/query strings look allright and then click OK/Approve. I don't know if this helps, but for me this saved hours of changing query strings. /Marcus "rasinc" wrote: I have a series of saved queries that I use on an ODBC data source. I would like to put them on other computers that may not have the same data sources but have different ones (tables are the same in each data source). Right now I am editing the query in Notepad to change the data source and path to the correct one on the other computers but am wondering why it is not evident how to change this in the query itself. Can it be done? How? TIA |
All times are GMT +1. The time now is 04:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com