Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQLSetConnectAttr error with Excel - converting to an older database?

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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 21 Feb 2007 13:33:16 -0800
Message-ID: <1172093596.721248.134640@j27g2000cwj.googlegroups.com>


On Feb 21, 4:01 pm, "S Davis" <theseanda..._at_gmail.com> 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:
ActiveSheet.QueryTables(1).CommandText = Replace(ActiveSheet.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: ActiveSheet.QueryTables("PO_QTY_PRICE_CH").CommandText = "SELECT 'MYSTUFF' FROM DUAL"
ActiveSheet.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. Received on Wed Feb 21 2007 - 15:33:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US