table missing in query design view 2

[willybgw]

I have an Access 2000 database with a query where the table is not visable in design view. The query works but I would really like to get the table back. If I add the table access appends a 1 (table_1) to the name and the query doesn't work.

Any suggestions?

 

[fneily]

Cool! How did you do that? I could use that trick on my peers. I've been trying to duplicate your problem, but I can't. When in design view, click on the dropdown arrow next to the View button and go into SQL view. Do you see the table name after the word FROM?

 

[willybgw]

fneily

Thanks for the response. The answer to your first question - I wish I knew how I did it. In sql view the table view is there.

 

[fneily]

I forgot to ask. Does your table begin with the letters "usys" or "msys"?

 

[BattleDroid42]

Maximize the database window and Access and make sure the table isn't hidden under the data grid.

 

[willybgw]

fneily

The table name is appraisal.


BattleDroid42

That is what I first thought, but that doesn't seem to be the problem.

 

[humbleprogrammer]

What happens if you re-create the query. Go into the original query in SQL view, copy the SQL and paste it into a new query. Is the table still hidden in the new query?

 

[BattleDroid42]

Does the table exist in the tables window? Was the table created with ADO?

 

[willybgw]

humbleprogrammer

That worked! The only downside was I had to format a calculated field.

This could have been solved by simply rebuilding the query, in this case a very simple one, but this is not the first database this has happened in and it could happen in a complicated query. I never considered copying the sql, and am glad to know that trick. Thanks!

Also thanks to fneily and BattleDroid42 for your interest.

BattleDroid42

The table does exist in the tables window

 

[humbleprogrammer]

If the table doesn't exist in the table's window, chances are it's hidden.

- Go to tools - options and check hidden objects.
- Then go to your tables. The tables that were hidden will be dimmed. right click on the dimmed table, choose properties and uncheck hidden.

This may be part of your problem.

 

[fneily]

I'm glad you had your problem solved. BUT - no one explained how the table in query design got hidden, disappeared, whatever? I know we live, but WHY??? Too deep.

Neil

 

[willybgw]

fneily

I agree. It would be nice to know how it happened.

 

[humbleprogrammer]

It's really hard to know without seeing the database. It can be so many things (security settings, corruption, ect.). If you want to send me a copy of the db to gosmart@charter.net with the table data cleared out, I will be happy to look at it for you.

 

[willybgw]

humbleprogrammer

I will email it to you Monday.

 

[humbleprogrammer]

Neil,

After viewing willybgw's database, it was discovered the database had permissions set on it. The query that was not displaying the table had Admin permissions set on it.

What's the reason you want to hide the tables in query design? If the table is hidden, it doesn't stop the user form viewing or changing the query. They can still see all the table fields by using the field drop down in the query. Do you not want users to be able to view or change the queries in design mode? If so, I can offer you some solutions?

 

[fneily]

Since I never came across this before, I wanted to know what caused it because it may come up again one day when you and Tek-Tips aren't available.

Also, I can use it on my students. Let them get practice in trouble shooting - a necessary evil if you're going to develop in Access.

Plus I worked at AT&T when it was the biggest corporation in the World -2 million employees up to 1990. Sometimes you have to work with people you don't like and this would be a nice way to aggravate them.

 

[humbleprogrammer]

Got ya! You just like to see people sweat don't ya

 

[judgehopkins]

I gave a star to humbleprogrammer because:

(1) he got info off-list from someone struggling with an Access question(which is a wonderful thing to do for your fellow man);

(b) he examined the info he received from the strugglee;

(iii) he came back to the forum to tell all of us the answers.

I also gave a star to fneily for telling me how to view SQL, plus he asked, [red]"I know we live, but WHY???"[/red]

I have this thread marked; I need to know the answer to that question.....

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[fneily]

Mr. HumbleProgrammer
Which gave you better satisfaction - solving the hard problems or the easy ones? That's the problem with kids these days, they're spoiled. Sweating's good for you anyway.
By the way, nice job.

Mr. JudgeHopkins
Thanks for the star, but wait 'til I get the answer. I'm almost there.

A humble programmer???

Enough. Back to Access.

 

[humbleprogrammer]

fneily - most definitely solving the harder problems. You learn the most that way and become a better programmer.

Judge – Thanks for the compliments and the star. What makes you think I am a he though?