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: 22 Feb 2007 15:22:06 -0800
Message-ID: <1172186526.776069.29130@s48g2000cws.googlegroups.com>


On Feb 22, 5:43 pm, "S Davis" <theseanda..._at_gmail.com> wrote:
> On Feb 21, 1:33 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> > 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.- 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 ActiveSheet.QueryTables(1).CommandText msgbox ActiveSheet.QueryTables(1).Connection

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

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Feb 22 2007 - 17:22:06 CST

Original text of this message

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