['' is not a valid name.] error

[ljoekelsoey4]

I am aware that this error has been discussed numerous times and is, or at least was not uncommon. Originally, there was an apostrophe in the address link. This has been removed, both backend and front end have been compacted and the links re-established. This has not affected the frequency and or timing of the error occuring. Running 'Analyze Performance' showed no issues.

I have appended data to both tblPatient and tblAppointment through excel 2007 import without issues in the past.

- If I try to create a new query on top of ANY of the tables, the error occurs.
- I can open any of the tables without the error occuring.
- If I try to create a new query on top of ANY other query, the error occurs.
- If I try to create a new report on top of any of the error prone queries, the
error occurs.
- Creating reports from queries that aren't having the error is problem free.
- Running older reports from the problematic queries does NOT cause the
error to occur.

I've gone through all the queries and made a note of dependencies and error occurrence. Of the 14 queries, 7 are getting this error. Of those 7, all of them are dependent on tblAppointments, however 2 of the error-free queries are also dependent on tblAppointments. Other than that, I cannot see any correlation or pattern.


I'm running Access 2007 SP3 MSO
Really have no idea what to try next. Greatly appreciate any guidance. Thanks.

 

[Uncle Gizmo]

I'm not exactly clear on what you are asking. My impression is that you have queries that are not running correctly. I think you suspect an apostrophe somewhere is the cause. I think you suspect the table appointments may contain some erroneous data. If that's the case then I would suggest you make a copy of your database and then delete all of the records in table appointments. Try and running your queries and see what happens. Add in some sample date and see what happens.

 

[ljoekelsoey4]

Hey, sorry for the ambiguity, but you are correct, that is essentially the state of things.

I tried emptying all the tables and then checking current queries and new queries, and the error has remained.

 

[Uncle Gizmo]

Tables can be funny beasts. You may have a corrupt record in one of the tables. Next stage would be to recreate the tables in a new database, however before taking that step I suggest you make a copy of your database, delete all of the objects, everything except the tables. Now pull in each query, one at the time, only have one query in the testing database at the time and see if the query works. If you find a query that does not work, then post the SQL version of the query in this thread.

 

[ljoekelsoey4]

With empty tables, I've now deleted all queries, forms, reports and macros. Tried and ultimately failed to create a new query with the same error repeating.

Ooft.:banghead:

Edit: will try re-entering queries

Edit: putting in queries one at a time still draws the same error. I can't post the sql as I can't get into it

 

[ljoekelsoey4]

I think I've found something actually. MSysCompactError table has logged the issue, however it is saying it is actually:

'|' is not a valid name ... and it is logged in the table MSysRelationships (error code -1002)

Trying to open relationships, the original error pops up. In full, it says:

'' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

EDIT: I seem to have narrowed it down table relationships; running the performance analysis works for every item, except for relationships, where the ['' is not a valid name] error appears. Can't see anything in relationships that would cause this though. Note that this only occurs in the front end. In the backend I can open up relationships without any issue.

 

[The_Doc_Man]

The '|' reference tells me that you have a problem in a query or table field selection (cannot tell which type from that info alone) in which something was substituted by concatenation or some other form of indirect reference, and the concatenation or reference failed to provide a valid field name. It could also occur if you changed the structure of something that contained the referenced info and forget to check for "related items" to catch the inevitable "dangling references" that would be sure to pop up.

Since the log references MSysRelationships, that is almost certainly where your problem lies, and it might be fixable if you are careful. You CAN open MSysRelationships by right-clicking in the top part of the navigation pane and telling it to show you System objects. You want to be VERY careful when doing this next step. Open table MSysRelationships in datasheet view. Read the lines one at a time, looking for a record that contains a blank in one of the columns szColumn, szObject, scReferencedColumn, or szReferencedObject. If you can find that faulty row, you can click on it and delete it. Take notes on what it contained before you do so, since that might help you reconstruct & replace that one relationship that was damaged.

DO NOT diddle around with any other records than the damaged one. You are poking around with a system infrastructure table and what you delete here disappears from the Relationships window that is launched from the Database Tools tab on the ribbon.

 

[ljoekelsoey4]

When I went into the MSysRelationships table, there wasn't any rows with blank columns unfortunately.

 

[Frothingslosh]

Your problem could be those parentheses in DiagnosisID(Specific). Access does NOT play well will non-alphanumeric characters.

You should lose the spaces in tblDiagnoses - Specific, too.

And yes, I'm aware that means you'll need to rewrite a bunch of queries.

 

[ljoekelsoey4]

Your problem could be those parentheses in DiagnosisID(Specific). Access does NOT play well will non-alphanumeric characters.

You should lose the spaces in tblDiagnoses - Specific, too.

And yes, I'm aware that means you'll need to rewrite a bunch of queries.

So i've gone in and changed both of these, and despite the fact some of the queries will be have to be rewritten from scratch as the original error was preventing me for getting into them anyway, I still cant create new queries from the original tables, which suggests this hasn't fixed the issue. I did a compile also.

 

[ljoekelsoey4]

I can create queries without any bother in the back end... not sure if this is advisable, or indeed if it gives any clue as to the issue, other than it is either in the front end or in the linkage between front and back? I can then export the query to the front end and it works normally.