 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Excel Errors / January 2005Unable to Edit query for SQL database
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  SignatureScott Gipson You should be able to get connection details via VBA e.g. this in the Immediate Window: ? Sheet1.QueryTables(1).Connection Jamie. -- 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? > You should be able to get connection details via VBA e.g. this in the > Immediate Window: [quoted text clipped - 4 lines] > > -- > 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. -- 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 > > Thanks for the help. When I run this function, I get a "Compile > Error: [quoted text clipped - 7 lines] > > -- I have it working and when I execute the request I now get a "ODBC:DNS=onc". ONC is my database but I still get the hour glass when I select Edit Query. Any additional ideas would be appreciated. Scott > > Thanks for the help. When I run this function, I get a "Compile > Error: [quoted text clipped - 7 lines] > > -- > I have it working and when I execute the request I now get a "ODBC:DNS=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. -- 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 > > I have it working and when I execute the request I now get a > "ODBC:DNS=onc". [quoted text clipped - 14 lines] > > -- > 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. -- 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 > > I went to the Control Panel and found that ONC is the system DSN and > even the [quoted text clipped - 9 lines] > > -- > 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. -- 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 > > 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 [quoted text clipped - 7 lines] > > -- > 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. -- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.