cannot find action query

[RSXchin]

I'm having trouble running update and delete external sql queries. When I try to run them, I get a "cannot find action query" error. Anyone know how to fix this?

 

[RSXchin]

nobody? I'm starting to hate this time zone, because no one is online.

 

[xenou]

I've never seen this error before.
Can you provide more information:
1) the kind of database you are accessing,
2) the complete error message,
3) the error number,
4) and, the method by which you are running the query?
5) if you are running this in VBA code, also the relevant code.

 

[RSXchin]

1) I was using another spreadsheet to learn about queries
2) cannot find action query
3) no error number
4) After creating a connection, I went to the edit query section
5) N/A

The query I was trying

Update [Table1$]
set Test = 'b'
where Test = 'a'

Delete doesn't work either

 

[xenou]

Interesting. Got me - when you say you created a connection, how did you create the connection? How is the data structured on the other sheet (is it a named range, or an actual Excel Table)? Can you successfully run SELECT queries on the data source (this can help narrow down the problem if we know that Select queries work but Update queries don't)? What version of Excel are you using?

 

[RSXchin]

1) I goto import data (I don't want to import it as a spreadsheet)
2) It uses the top row as the header, which is Test in this case
3) Select queries are no problem
4) excel 2003... My work is cheap.

 

[xenou]

This works for me:

UPDATE `C:\Documents and Settings\<user>\Desktop\Book1.xls`.`Sheet1$` `Sheet1$`
SET `Sheet1$`.Test='b' 
WHERE (`Sheet1$`.Test='a')

I personally don't have much patience with MSQuery - you have to learn it's quirks. You could do worse than read Zapawa's book on "Advanced Excel Reporting" - I did once but now I stick to ADO most all the time.

 

[RSXchin]

I'm starting to think that I just don't have that capability... Thanks for the effort though.

 

[RSXchin]

I was right... MS query is NOT installed, and I'm not allowed to install it. Lame.