Error on displaying query design

[RobinHughes-at-ilsco]

I have just installed Access 2007 and am trying to open a query that was
written in Access 2000. When I click Design view I get "is not a valid Name,
I've tried deleting the query and re-writting it in Access 2007 which then it
works, but once I close it and try and open it again in Design view I get the
same error "is not a valid Name. Anybody got an idea what's wrong. The query
name is Pull-Week and is a select query that joins two tables and pulls data
from them. A very simple query.

 

[Allen Browne]

Open the Immediate Window (Ctrl+G), and enter:
Debug.Print CurrentDb.QueryDefs("Pull-Week").SQL

When you press enter, it should give you the SQL statement. Copy to
clipboard, create a new query, switch to SQL View, and paste the statement
in. You can now try switching to Design view, or (if that doesn't work),
editing the SQL statement to fix up what's wrong.

If you can get this new query working, you can delete the old one, and save
the new one instead. Make sure Name AutoCorrect is turned off. Explanation:
http://allenbrowne.com/bug-03.html
Then compact/repair the database.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

in message
news:[email protected]...

 

[RobinHughes-at-ilsco]

Still does the same thing, where do I turn off Name AutoCorrect I can't seem
to find anything yet

 

[Allen Browne]

in message

Still does the same thing, where do I turn off Name AutoCorrect I can't
seem
to find anything yet

2nd pargraph of the linked article explains where to turn off Name
AutoCorrupt in different versions of Access.

 

[drumlinds]

Hello,

I'm having the same problem as Robin. I've shut the AutoCorrect name
option off, and I tried pasting the SQL statement into a new query,
but I still can't open the new query in design view. I get the same
error ("is not a valid name...). Any other suggestions?

Thanks!
Lindsay

 

[drumlinds]

Sorry, I should be more complete. I created the database in Access
2007 and I just split it yesterday. Unfortunately, I gave the back
end file a name with an apostrophe in it, which I've seen on some
forums as the cause of this error message ("is not a valid name...).
I tried removing the apostrophe from the back end file name and
relinking the front end, but that didn't solve the problem. The
queries worked fine before I split the database. Now they still run,
but I can't view them in design view. When I try to make a new query
using the Query Wizard I encounter the same error message in the
middle of the process. Where did I go wrong??

Thanks again,
Lindsay

 

[Allen Browne]

Did you try a compact/repair after renaming the back end?

If that didn't work, next thing might be to delete all the linked tables in
your front end. Then link them again (using the Access icon on the External
Data tab of the ribbon.)

If it still fails after that, on only one query (others work), post the SQL.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sorry, I should be more complete. I created the database in Access
2007 and I just split it yesterday. Unfortunately, I gave the back
end file a name with an apostrophe in it, which I've seen on some
forums as the cause of this error message ("is not a valid name...).
I tried removing the apostrophe from the back end file name and
relinking the front end, but that didn't solve the problem. The
queries worked fine before I split the database. Now they still run,
but I can't view them in design view. When I try to make a new query
using the Query Wizard I encounter the same error message in the
middle of the process. Where did I go wrong??

Thanks again,
Lindsay

 

[drumlinds]

Hi Allen,

I did try a compact/repair after renaming the back end. I also tried
deleting the linked tables and re-importing/linking them. However,
there are 2 links to tables that no longer exist that will not
delete. When I try to delete them I get the same "is not a valid
name.. error. There are 3 queries that work, but won't let me see
them in design view (though they used to) and they're all similar and
reference the same table. Here's the SQL to one of them:

SELECT Projects.ProjectName, [Project Log].LogDate, [Project
Log].Description, [Project Log].Category
FROM Projects INNER JOIN [Project Log] ON Projects.ID=[Project
Log].ProjectName
WHERE ((([Project Log].LogDate) Between [Forms]![ProjectLogReport].
[StartDate2] And [Forms]![ProjectLogReport].[EndDate2]))
ORDER BY [Project Log].LogDate DESC;

I'm thinking I may have to put my database back together and try re-
splitting it if nothing else works. Is that possible?

Your help is much appreciated!
Lindsay

 

[drumlinds]

Hi Allen,

I think I fixed the problem (so far, so good)! I think it was the
defunct table links that were the problem or the last of the
problems. I created tables in the backend to match the names of the
linked tables that wouldn't let me delete them, updated the links, and
then I was able to delete them. Once I got rid of those two, I
deleted all the remaining linked tables and relinked them.

I also tried this (from another forum) before I deleted the defunct
links, but it didn't seem to help as far as I could tell...:

***Alternatively, to fix it in Access 2007 do something like this:

Public Sub DealWithApostrophes()

Dim dbCurrent As DAO.Database

Set dbCurrent = CurrentDb
With dbCurrent.TableDefs("tblJohn's Table")
..Connect = Replace(.Connect, "'", "")
..RefreshLink
End With
Set dbCurrent = Nothing

End Sub

Where "tblJohn's Table" is a table in a database with a name with an
apostrophe such as "C:\John's Data\John's Data.accdb". Fix the entire
path
of the database so that there are no apostrophes anywhere. Note that
the
table name can continue to have apostrophes. Then with the above code
in a
regular (not class, form or report) module, place your cursor anywhere
inside
of the code and press F5 to run it.

Good Luck!

Clifford Bass***


Thank you so much for your help!
Lindsay