MS Query Data Source Change

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Thanks. I'll try your code when I get a chance.

Marcus Langell said:
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
Click to expand...
 
Back
Top