Help viewing SQL of a query w/ a syntax error

[Guest]

Hello,

I'm using Access 2003. I have a query that returns results, but it also
pops up a "syntax error in FROM clause" message. I must have changed one of
the tables or queries it uses without updating the query, but I am not sure.
I would like to open the query in Design or SQL View and find out what is
happeing.

When I try to open the query in Design View via right-click->Design View, I
get the same pop-up: "syntax error in FROM clause", and it does NOT open in
Design view (or SQL View, or any View for that matter), so I cannot edit the
query to fix the sytax error.

Does anyone know how I can open my query in Design View (despite the fact
that right-click->Design View isn't working) ?

It seems to be locking me out of changing the query.

Thanks,

Scott

 

[Guest]

I'm thinking that you have a corrupt database. At times like this, nothing
beats a good backup. In fact make a complete backup of your database now and
put it away for safe keeping.

See if a compact and repair helps. If not then try opening a new database
file and importing everything from the problem database. If something won't
import, such as that query, chances are good that it was corrupt.

For more corruption help:
Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

 

[John Spencer]

Another option would be to use the VBA immediate window to grab the SQL of
the query.

?CurrentDb().QueryDefs("NameOfQuery").SQL
That should print the query string in the immediate window. You can copy
that and paste it into a new query. If the new query works, then I would
assume that there is some corruption in the old query. If it fails, perhaps
you can troubleshoot it and see why?

Good luck

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..