MS Query - From in SQL

[Guest]

Question...
We have quite a few queries developed in Excel/MS Query that query an AS400.
We need to share these with users at other locations that use an AS400 with
a different name. Is there any way around changing the SQL FROM statement
for the AS400 machine name to another AS400 machine name without editing the
query in MS Query and doing it in View, SQL? Looking for the quick, easy way
to do this, because there are too many to change this way.

 

[Fredrik Wahlgren]

Question...
We have quite a few queries developed in Excel/MS Query that query an AS400.
We need to share these with users at other locations that use an AS400 with
a different name. Is there any way around changing the SQL FROM statement
for the AS400 machine name to another AS400 machine name without editing the
query in MS Query and doing it in View, SQL? Looking for the quick, easy way
to do this, because there are too many to change this way.

I think one way to do this would be to use ADO instead. With this approach
you can get the connection from an .udl file. Thus, you don't have to edit
the code. It's not an easy and quick way, though. The question is
interesting and I will follow it.

/Fredrik

 

[Robert Bruce]

Question...
We have quite a few queries developed in Excel/MS Query that query an
AS400. We need to share these with users at other locations that use
an AS400 with a different name. Is there any way around changing the
SQL FROM statement for the AS400 machine name to another AS400
machine name without editing the query in MS Query and doing it in
View, SQL? Looking for the quick, easy way to do this, because there
are too many to change this way.

If you used MSQuery to return the results then you will have a QueryTable
object on your worksheet. As long as you chose to save the query definition
with the sheet, the QueryTable object will have a Connection property that
corresponds to the connectionstring used by the query. Your mission, I
suppose, is to ensure that this property is updated with the appropriate
value depending on who opens the workbook. You could put a lookup table of
usernames and connectionstrings on a hidden sheet.

Rob

 

[Guest]

In addition to updating the Connection property to update the DB command
string, in order to change the FROM clause in the SQL you will need to modify
the CommandText property, which should contain the SQL query. You could use
the Replace function to modify it, e.g.
Replace(Sheets("MySheet").QueryTables(1).CommandText, "FROM AAA", "FROM BBB")