MS Query changed Datasource
 

I have a number of MS Queries which i need to point to a new SQL datasource .
On pc's both datasources are present as System DSN's.

On opening existing Queries with new datasource, View/tables/criteria and
Criteria menu options are supressed.

New created Queries have no issue.

How can i preserve Query function with a changed datasource?

MS Query changed Datasource
 

Each query creates a QueryTable object on your worksheet and the QueryTable
has a .Connection property that specifies the datasource (i.e., the
connection string for the database driver). I usually just edit that in the
Immediate pane of the VBA editor, e.g. I will type:
Set K = ActiveSheet.QueryTables(1)
? K.Connection
ODBC;DSN=OLDDBDRIVER;.... ' This would be the response
K.Connection = Replace(K.Connection, "OLDDBDRIVER", "NEWDBDRIVER")

If all else is the same this should do - save the file with the new
connection. But in some cases you may have to modify other parts of the
string, or possibly even part of the SQL query (which is in the
QueryTable.CommandText property): Take a look at both the .Connection and
..CommandText first to see if anything needs changing. Comparing these from
one of your old queries (old DSN) and one of the new ones (created using the
new DSN) would be instructive.
--
- K Dales


"Dave W." wrote:

I have a number of MS Queries which i need to point to a new SQL datasource .
On pc's both datasources are present as System DSN's.

On opening existing Queries with new datasource, View/tables/criteria and
Criteria menu options are supressed.

New created Queries have no issue.

How can i preserve Query function with a changed datasource?

MS Query changed Datasource
 

With thanks and much appreciated...saved days of work!

"K Dales" wrote:

Each query creates a QueryTable object on your worksheet and the QueryTable
has a .Connection property that specifies the datasource (i.e., the
connection string for the database driver). I usually just edit that in the
Immediate pane of the VBA editor, e.g. I will type:
Set K = ActiveSheet.QueryTables(1)
? K.Connection
ODBC;DSN=OLDDBDRIVER;.... ' This would be the response
K.Connection = Replace(K.Connection, "OLDDBDRIVER", "NEWDBDRIVER")

If all else is the same this should do - save the file with the new
connection. But in some cases you may have to modify other parts of the
string, or possibly even part of the SQL query (which is in the
QueryTable.CommandText property): Take a look at both the .Connection and
.CommandText first to see if anything needs changing. Comparing these from
one of your old queries (old DSN) and one of the new ones (created using the
new DSN) would be instructive.
--
- K Dales


"Dave W." wrote:

I have a number of MS Queries which i need to point to a new SQL datasource .
On pc's both datasources are present as System DSN's.

On opening existing Queries with new datasource, View/tables/criteria and
Criteria menu options are supressed.

New created Queries have no issue.

How can i preserve Query function with a changed datasource?