Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
rasinc
 
Posts: n/a
Default 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   Report Post  
Marcus Langell
 
Posts: n/a
Default

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   Report Post  
rasinc
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel data query locks source file jim.bahr Excel Discussion (Misc queries) 0 June 10th 05 05:48 AM
Returning data to Excel sheet from MS query SNB Excel Discussion (Misc queries) 0 April 27th 05 01:27 AM
Get External Data Web Query new worksheet Nina Excel Discussion (Misc queries) 0 April 14th 05 07:08 PM
Database Query -- Data Type Mismatch StephenP Excel Discussion (Misc queries) 0 April 14th 05 07:05 PM
Named dynamic ranges, copied worksheets and graph source data WP Charts and Charting in Excel 1 November 28th 04 06:19 PM


All times are GMT +1. The time now is 04:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"