Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel data query locks source file | Excel Discussion (Misc queries) | |||
Returning data to Excel sheet from MS query | Excel Discussion (Misc queries) | |||
Get External Data Web Query new worksheet | Excel Discussion (Misc queries) | |||
Database Query -- Data Type Mismatch | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |