Postgres Pro
Downloads
Home   >   mailing lists

Thread: Schemas, and visibility of tables in MS-Query.

Schemas, and visibility of tables in MS-Query.

From
Harry Broomhall
Date:
   One of the extras I had hoped for with PgSQL 7.3 Schemas was a
somewhat cosmetic, but useful, change in the way that MS-Query sees
tables in a database.

   What I *actualy* want to achieve is for the MS-Query browser only
to show some tables from the database it is connected to.

   I've played with various variations on schemas, but whatever I do
the browser insists on showing <All> schemas, and hence all tables.

   The reason I want to do this is down to support issues.  The users
are mostly senior management and I keep on being asked "What are these
other tables and why can't I get data from them?"!  It isn't easy
telling such exalted people "You can't get at them because I don't want
you to fiddle!".  Much easier if they aren't visible.  <grin>

   Any suggestions on how to achieve this?

   Details of database:
PostgreSQL 7.3.4 on i386-unknown-freebsd4.5, compiled by GCC 2.95.3
   ODBC driver is 'PostgreSQL' (i.e. not Legacy or Unicode etc) version
7.03.01.11.

   I'd rather not depend on a particular ODBC driver if I can help it!

   Regards,
      Harry.

Re: Schemas, and visibility of tables in MS-Query.

From
Harry Broomhall
Date:
Harry Broomhall writes:
>    One of the extras I had hoped for with PgSQL 7.3 Schemas was a
> somewhat cosmetic, but useful, change in the way that MS-Query sees
> tables in a database.
>
>    What I *actualy* want to achieve is for the MS-Query browser only
> to show some tables from the database it is connected to.
>
>    I've played with various variations on schemas, but whatever I do
> the browser insists on showing <All> schemas, and hence all tables.
[SNIP]

   Well that produced a resounding silence!  I'm not sure if this was:
1)  It was thought too silly for words
2)  It is impossible to do
3)  Everybody was waiting for everybody else to comment
4)  Something else


   I'll ask again, just in case somebody did comment and that comment fell
into the global bit-bucket...

   Is there any way to effectively hide tables in a database from being
seen by MS Query?

   Regards,
      Harry.

Re: Schemas, and visibility of tables in MS-Query.

From
Jeff Eckermann
Date:
--- Harry Broomhall <harry.broomhall@uk.easynet.net>
wrote:
> Harry Broomhall writes:
> >    One of the extras I had hoped for with PgSQL
> 7.3 Schemas was a
> > somewhat cosmetic, but useful, change in the way
> that MS-Query sees
> > tables in a database.
> >
> >    What I *actualy* want to achieve is for the
> MS-Query browser only
> > to show some tables from the database it is
> connected to.
> >
> >    I've played with various variations on schemas,
> but whatever I do
> > the browser insists on showing <All> schemas, and
> hence all tables.
> [SNIP]
>
>    Well that produced a resounding silence!  I'm not
> sure if this was:
> 1)  It was thought too silly for words
> 2)  It is impossible to do
> 3)  Everybody was waiting for everybody else to
> comment
> 4)  Something else
>
>
>    I'll ask again, just in case somebody did comment
> and that comment fell
> into the global bit-bucket...
>
>    Is there any way to effectively hide tables in a
> database from being
> seen by MS Query?

AFAIK (I haven't done this myself), you can issue a
"set search path" command in the "Connect Settings"
box in the DSN, which will limit the schemas seen to
whatever you put in the search path list.

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

Re: Schemas, and visibility of tables in MS-Query.

From
Harry Broomhall
Date:
Jeff Eckermann writes:
>
> AFAIK (I haven't done this myself), you can issue a
> "set search path" command in the "Connect Settings"
> box in the DSN, which will limit the schemas seen to
> whatever you put in the search path list.


   Nice idea - I hadn't spotted that in the driver config!
Unless I'm doing it wrong though I've just tried it and it doesn't work!   :-(

   Regards,
       Harry.

Re: Schemas, and visibility of tables in MS-Query.

From
Tom Lane
Date:
Harry Broomhall <harry.broomhall@uk.easynet.net> writes:
>    I'll ask again, just in case somebody did comment and that comment fell
> into the global bit-bucket...
>    Is there any way to effectively hide tables in a database from being
> seen by MS Query?

I think you're asking the wrong people.  This looks like a question
about MS Query's behavior, not Postgres's.

If you're using pgsql's ODBC driver to hook MS Query to Postgres, then
it's barely possible that rejiggering some driver setting would help you
do what you want.  But I'm not sure any of the ODBC people read
pgsql-novice.  Try pgsql-odbc.

            regards, tom lane

Re: Schemas, and visibility of tables in MS-Query.

From
Harry Broomhall
Date:
Jeff Eckermann writes:
[SNIP]
>
> Harry,
>
> I haven't worked with this to know what the issues
> might be.  I believe I remember seeing a similar
> question come up before, but a quick archives search
> brings up nothing useful.
>
> I am forwarding your message to the pgsql-odbc list,
> where you are more likely to get a helpful reply.  If
> you have not yet subscribed to that list, you may wish
> to now.
>
> Sorry I don't know enough about this feature to
> diagnose your problems.
>

   Many thanks for taking the time nonetheless.

   I am already subscribed to the ODBC list - and was wondering if that
was a better fit!

   Regards,
       Harry.