Re: SQLSetConnectAttr error with Excel - converting to an older database?

On Feb 22, 5:43 pm, "S Davis" <theseanda...@xxxxxxxxx> wrote:
On Feb 21, 1:33 pm, "Charles Hooper" <hooperc2...@xxxxxxxxx> wrote:
On Feb 21, 4:01 pm, "S Davis" <theseanda...@xxxxxxxxx> wrote:

Hello,

Recently my place of work switched servers to a new product. I have a
number of queries that would run from within Excel (MS Query), hit the
database and return data

What's happened is that with the wipeout of the old database, I am
stuck with a whole wack of useless queries and files. If I try and
refresh the query, I get the error in the title. I initally knew this
would be an issue and so we kept our database online as a backup for a
month or so, and I converted everything over. You need to actually get
into the query via MSQuery in order to view, edit, or change the query
parameters, including the database location and DSN.

Is there any way that I can somehow force MSQuery to get into the
query built and change the location and DSN of the database? The new
database has all of the same data - just a new name, a couple digits
off on the server address.

My thanks.
-S

You can modify a query defintion using a macro created in the Visual
Basic editor within Excel. For example, if the schema containing the
data changed from MONDAY to TUESDAY:
Active***.QueryTables(1).CommandText =
Replace(Active***.QueryTables(1).CommandText, "MONDAY.", "TUESDAY.")

If you name the query range, for example, as PO_QTY_PRICE_CH, you can
modify the query definition like this, and then refresh the data:
Active***.QueryTables("PO_QTY_PRICE_CH").CommandText = "SELECT
'MYSTUFF' FROM DUAL"
Active***.QueryTables("PO_QTY_PRICE_CH").Refresh

Search Excel's Visual Basic help for QueryTables to determine how to
change the Connection property for the queries.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.- Hide quoted text -

- Show quoted text -

Thanks for the reply. You've given me some ideas.

My thought now is to open the file that contains the queries I can no
longer access. What I need to do is find a way with VBA to return the
connection data. I'll also need a way to return the SQL. Right now, Im
basically at a zero starting point, so this should be fun...

No matter how difficult, at least I dont have to reproduce the most
obscure sql known to man... two days of trying to replicate it from
scratch and Im scratching my head.

Any programmers out there with VBA knowledge who know how to retrieve
SQL and connection information from a Querytable, let me know.

It can be done. Some time ago, I created a VBA function that scanned
all Excel files in a particular folder, opened each one, modified the
SQL statement, and then saved the Excel file. The person was in the
process of switching from a Centura SQLBase database to a SQLServer
database.

Drop into the Visual Basic editor, type QueryTables and then press the
F1 key. That will take you to documentation that shows you how to
work with the QueryTable object. One of the properties of the
QueryTable is the Connection property, and this property is covered in
the help documentation.

Experiment with what I previously posted. In a macro add the lines:
msgbox Active***.QueryTables(1).CommandText
msgbox Active***.QueryTables(1).Connection

Flip back to an Excel work*** with a query, and run the macro.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

.