Unable to Edit query for SQL database

[Guest]

I have an application running on WinXp / Office 2K. This is an Excel app that
queries data from a SQL database on my C: drive that is synchronized from my
company's Oracle server. I originally designed this query and now I cannot
edit it. The corp tech gurus spent the better part of Tuesday trying to
troubleshoot the problem but to no avail. I can use the query to Refresh the
data but when I go to Data / Get External Data / Edit Query, all I get the
hourglass and nothing is returned. We think that it is looking for
information on the server but no matter what I do I cannot find out where in
the application the query is pointing to. Since the Refresh function works
fine, I will aussme that it is pointing at the database on my C: drive. Any
help / ideas would be greatly appreciated. Thanks

 

[onedaywhen]

You should be able to get connection details via VBA e.g. this in the
Immediate Window:

? Sheet1.QueryTables(1).Connection

Jamie.

--

 

[Guest]

Thanks for the help. When I run this function, I get a "Compile Error:
Invalid outside procedure" and a window pops up with "Print
Sheet1.QueryTables(1).Connection"

Any ideas?

 

[onedaywhen]

Thanks for the help. When I run this function, I get a "Compile Error:
Invalid outside procedure" and a window pops up with "Print
Sheet1.QueryTables(1).Connection"

So do I <g>. Try putting a ? at the start of the line, meaning 'Print'.
BTW the line assumes your querytable is on a sheet with the codename
'Sheet1' and it is the first (or only) querytable on the sheet.
Jamie.

--

 

[Guest]

Got it. I get the response "ODBC;DNS=onc" . ONC is my database but when I go
to edit the query all I get is the hour glass forever and access to the
query. Any other ideas would be appreciated. Scott

 

[Guest]

I have it working and when I execute the request I now get a "ODBCNS=onc".
ONC is my database but I still get the hour glass when I select Edit Query.
Any additional ideas would be appreciated. Scott

 

[onedaywhen]

I have it working and when I execute the request I now get a "ODBCNS=onc".
ONC is my database but I still get the hour glass when I select Edit Query.
Any additional ideas would be appreciated. Scott

Is the DSN missing? Control Panel, Administrative Tools, Data Sources
(ODBC) and you should be find a DSN called onc. Also look at the SQL
text e.g.

? Sheet1.QueryTables(1).CommandText

The path to the data source is sometimes repeated in the query text
itself.

Jamie.

--

 

[Guest]

I went to the Control Panel and found that ONC is the system DSN and even the
test connection works. SO far everything is there and everything works except
the Edit Query function. Any other ideas?

Scott

 

[onedaywhen]

I went to the Control Panel and found that ONC is the system DSN and even the
test connection works. SO far everything is there and everything works except
the Edit Query function. Any other ideas?

Well, now you've found the data source (from the DSN), can you recreate
the querytable? Note, try and create a new query before you delete the
original.

Jamie.

--

 

[Guest]

I have tried that. When I open a blank sheet and attempt to
Get External Data / New Database Query all I get is the cursor turned into
an hourglass and nothing happens.

I was able to find the query itself in the application data folder and was
able open and edit the query but not from the Excel application. I manage
this application for 15 different people who have unique user names and this
is a real pain if I have to open / modify and save the individual queries.

Scott

 

[onedaywhen]

I have tried that. When I open a blank sheet and attempt to
Get External Data / New Database Query all I get is the cursor turned into
an hourglass and nothing happens.

Ah, so you can't start MS Query at all, then? Take a look in the google
groups Usenet archive for Microsoft.Public.Excel. I see posts on this
problem on a regular basis (I don't recall see a solution ...)
Jamie.

--

 

[Guest]

What I discovered is that I had to have MS Access loaded in order for the
query to work. When my IT guy loaed Acess, all of my problems went away.
Thanks Again, Scott

 

[Jamie Collins]

What I discovered is that I had to have MS Access loaded in order for the
query to work.

What do you mean by 'loaded' e.g. was the driver not installed?
Jamie.

--