Connect a Query in MS Query top another DSN

[Michiel via OfficeKB.com]

Hello,

I import some data from a database using a query from MS Query.
The Query was created on a DSN named DSNDB1
Now I need to get the same data from DSNDB2 into the same report.

Is there a way to change the DSN used (DSNDB1) to the other one (DSNDB2) just
by setting that somewhere in MS Query. I do not want to use code here.
And I do not want to re-create the same query either. Just replace DSNDB1 by
DSNDB2 somewhere in a hopefully available option of MS Query.

Anyone who has a tip for me?

 

[FSt1]

hi
right click the MSQuery data area then click edit query.
click through the wizard to the last screen(4th) and check edit query with
MS query.
when MSQuery come up, click on the SQL button.
if your just changing the db source, you can modify the from clause to the
new database. you may need the entire file path if different from the old
database.

Regards
FSt1

 

[ward376]

You could copy the sql statement into a new query pointed at your
other data source. Click on the SQL icon in Query, copy the contents
of the dialog that appears, paste into a text file, find and replace
the table and server references, then paste it into the sql dialog in
a new query using the other dsn.

Cliff Edwards

 

[Michiel via OfficeKB.com]

Hi FSt1 and Cliff,

Thanks for your reply.

If I click the SQL button in MS Query I only get to see the query:
"Select date, name from customers"

Yes I can copy this code , delete the link in excel and create another link
(and then choose the new dsn) and copy in the code. But I was hoping there
would exist some place in MS query where Y could simple replace the current
DSN's name (DSNDB1) for the new one (DSNDB2).
I guess that is not possible.

Thanks anyway very much for your time!

 

[Dan]

Michiel,

I just add a new connection to the file. I usually have a new sheet and then
move it to the area I want. You can use the refresh all icon and all of the
queries are updated.

In some cases, I use the table function and use a vlookup to join the two
files together. (I have to perform some calculations in order to join the
files or else I would have used the joins in MS Query.

 

[Michiel via OfficeKB.com]

Hi all.

Thanks for thinking together with me!
I was still lokking for the option to just change the DSN info. And I guess I
found the way.

1) Save the MSQuery query to a folder
2) go to that folder and right click it and select "open in Notepad"
3) Now you get some editable text. Change the DSN name AND (!!!) the server
name AND (!!!!) the database name.
4) Save the file

Now the MS Query query will use the other DSN and corresponding database.