Hidden Queries in Office 2000

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill Barber - 05 Dec 2005 18:46 GMT

A query is created from a Texas University in an Access 2000 mdb. The name
of the Query does not appear.

I have the Hidden Boxes checked so that all tables and queries will be
displayed. When a Query is created using the execute.connection "Create Proc
queryname" the query does not display.

Does anyone know how I can display that the query is there. The old report
that was generated using this query no longer works. This did work back in
Access 2.0

The universities want to acces this information again after discontinuing it
for 3 years.

Thanks

Bill

Reply to this Message

[MVP] S.Clark - 05 Dec 2005 21:56 GMT

Did you say from Texas? Oh, sorry, Texas colleges aren't allowed to make
queries in Access. I think there was a KB article about this, but it was
written by a Sooner, and not Microsoft. :D

Queries with the prefix of 'msys' or 'usys' are considered "System" queries,
and do not display in the database window. In the same place where you
checked 'Show Hidden', there is an option for "system" queries, too. If the
names of your queries start with these prefixes, then check the box and see
if they are displayed.

The only other thought would be that you are not looking at the Query tab,
but a user-made favorites or group listing.

Signature

Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

>A query is created from a Texas University in an Access 2000 mdb. The name
> of the Query does not appear.
[quoted text clipped - 17 lines]
>
> Bill

Reply to this Message

david epsom dot com dot au - 05 Dec 2005 22:44 GMT

Queries generated by using .connection "Create Proc queryname"
are not visible unless you use a later version of Office.

You can view the sql from the immediate window by using

?querydefs("queryname").sql

You can use the same sql to create a query which is visible like this:

codedb.QueryDefs.append
codedb.CreateQueryDef("newname",codedb.querydefs("oldname").sql)

(watch for line wrapping)

(david)

>A query is created from a Texas University in an Access 2000 mdb. The name
> of the Query does not appear.
[quoted text clipped - 17 lines]
>
> Bill

Reply to this Message

Bill Barber - 06 Dec 2005 00:52 GMT

David;
Thanks for getting back. We have installed both Office XP and
Office 2003. The problem is that we have been fighting this for three weeks.
For some reason the exact asp page keeps telling us the file is in use when
it is not. We have tried both ODBC and ADO connections and it fails in both.
We have paid a Microsoft consultant to find the problem and so far she has
been unable. It worked in Access 2.0 and we have not been able to get it to
work since.

Why both Office XP and 2003 think the file is open no one has been
able to figure out. It even fails on a stand alone machine and is really bad
if we use localhost. However the same data works in 2000 but the query will
not display in the mdb.

we are using six different scenarios and so far we have six
different problems.

> Queries generated by using .connection "Create Proc queryname"
> are not visible unless you use a later version of Office.
[quoted text clipped - 33 lines]
> >
> > Bill

Reply to this Message

david epsom dot com dot au - 07 Dec 2005 00:56 GMT

> For some reason the exact asp page keeps telling us the file is in use
> it is not. We have tried both ODBC and ADO connections and it fails

That looks like a file permission problem. Security has been
tightened, or perhaps the asp page just doesn't have permission
to r/w/create/delete in the file location or system.mdw location
or temp location.

Access 2.0 did not require create/delete permission, which is
a change even if your security and file location and IIS user
permissions have not changed.

BTW, you can't use both Access 2.0 (DAO 2.5) and Access 2000+
(DAO 4.0) at the same time.

(david)

> David;
> Thanks for getting back. We have installed both Office XP and
[quoted text clipped - 61 lines]
>> >
>> > Bill

Reply to this Message