Schema/User Name Oracle/Access linked tables

[dave71]

Hi

Could someone please advise me how to remove the schema name from
linked tables within Access. For example when I connect to a Oracle
database via Microsoft ODCB for Oracle the list of tables shown are of
the nature username.tablen ame I would like this only to display the
table name I have this arrangement on another PC here but I am not
sure how it was configured.

Thanks

[Steve Jorgensen]

Re: Schema/User Name Oracle/Access linked tables

Just rename the links. The links can have any name you like and still be
linked to the tables you originally linked them to.

On 23 Feb 2004 19:31:43 -0800, davekeating00@h otmail.com (dave71) wrote:
[color=blue]
>Hi
>
>Could someone please advise me how to remove the schema name from
>linked tables within Access. For example when I connect to a Oracle
>database via Microsoft ODCB for Oracle the list of tables shown are of
>the nature username.tablen ame I would like this only to display the
>table name I have this arrangement on another PC here but I am not
>sure how it was configured.
>
>Thanks[/color]

[DFS]

Re: Schema/User Name Oracle/Access linked tables

"dave71" <davekeating00@ hotmail.com> wrote in message
news:cacc6260.0 402231931.17969 e46@posting.goo gle.com...[color=blue]
> Hi
>
> Could someone please advise me how to remove the schema name from
> linked tables within Access. For example when I connect to a Oracle
> database via Microsoft ODCB for Oracle the list of tables shown are of
> the nature username.tablen ame I would like this only to display the
> table name I have this arrangement on another PC here but I am not
> sure how it was configured.
>
> Thanks[/color]

Dave,

Just highlight the link, hit F2 and edit the name. But before doing this,
think about the ramifications. It's yet another issue that has to be
documented. If you relink the tables, you'll have to rename them again. If
someone inherits the system from you, they'll have to know this caveat. The
schema names might help when writing pass-through queries.

I used to rename links, but no longer do so. I recommend against it.

[Steve Jorgensen]

Re: Schema/User Name Oracle/Access linked tables

On Tue, 24 Feb 2004 00:14:02 -0500, "DFS" <nospam@nospam. com> wrote:
[color=blue]
>"dave71" <davekeating00@ hotmail.com> wrote in message
>news:cacc6260. 0402231931.1796 9e46@posting.go ogle.com...[color=green]
>> Hi
>>
>> Could someone please advise me how to remove the schema name from
>> linked tables within Access. For example when I connect to a Oracle
>> database via Microsoft ODCB for Oracle the list of tables shown are of
>> the nature username.tablen ame I would like this only to display the
>> table name I have this arrangement on another PC here but I am not
>> sure how it was configured.
>>
>> Thanks[/color]
>
>Dave,
>
>Just highlight the link, hit F2 and edit the name. But before doing this,
>think about the ramifications. It's yet another issue that has to be
>documented. If you relink the tables, you'll have to rename them again. If
>someone inherits the system from you, they'll have to know this caveat. The
>schema names might help when writing pass-through queries.
>
>I used to rename links, but no longer do so. I recommend against it.
>[/color]

It depends how you re-link, doesn't it? My relinking code updates the connect
strings and refreshes the existing links, so the names of the links,
themselves don't change. If anyone does manually delete and recreate the
links, it will be obvious that the link names don't look the same as they did
before and will need to be fixed. Finally, if you leave the schema names -in-
the link names, you are now making the front-end only work with one schema
name which makes the front-end much less flexible.

By the way, if you -really-have a lot of links to update, you can loop through
the tabledefs collection, and rewrtie the Name property in code. I do that in
one of my apps since I have to delete and recreate the links to switch between
an MDB and PostgreSQL server back-end. I don't know why, but Access won't let
you change one type of link to another type.

[DFS]

Re: Schema/User Name Oracle/Access linked tables


"Steve Jorgensen" <nospam@nospam. nospam> wrote in message
news:dmpl30lr69 muke1jppbat5e2g e6d7llle3@4ax.c om...[color=blue]
> On Tue, 24 Feb 2004 00:14:02 -0500, "DFS" <nospam@nospam. com> wrote:
>[color=green]
> >"dave71" <davekeating00@ hotmail.com> wrote in message
> >news:cacc6260. 0402231931.1796 9e46@posting.go ogle.com...[color=darkred]
> >> Hi
> >>
> >> Could someone please advise me how to remove the schema name from
> >> linked tables within Access. For example when I connect to a Oracle
> >> database via Microsoft ODCB for Oracle the list of tables shown are of
> >> the nature username.tablen ame I would like this only to display the
> >> table name I have this arrangement on another PC here but I am not
> >> sure how it was configured.
> >>
> >> Thanks[/color]
> >
> >Dave,
> >
> >Just highlight the link, hit F2 and edit the name. But before doing[/color][/color]
this,[color=blue][color=green]
> >think about the ramifications. It's yet another issue that has to be
> >documented. If you relink the tables, you'll have to rename them again.[/color][/color]
If[color=blue][color=green]
> >someone inherits the system from you, they'll have to know this caveat.[/color][/color]
The[color=blue][color=green]
> >schema names might help when writing pass-through queries.
> >
> >I used to rename links, but no longer do so. I recommend against it.
> >[/color]
>
> It depends how you re-link, doesn't it? My relinking code updates the[/color]
connect[color=blue]
> strings and refreshes the existing links, so the names of the links,
> themselves don't change. If anyone does manually delete and recreate the
> links, it will be obvious that the link names don't look the same as they[/color]
did[color=blue]
> before and will need to be fixed.[/color]
[color=blue]
> Finally, if you leave the schema names -in-
> the link names, you are now making the front-end only work with one schema
> name which makes the front-end much less flexible.[/color]

But only if you find yourself using the same table from different schemas.
Do you? Why?

I never have. Not once. Though I have used the same table from the same
schema on a different box: dev vs. production.

[color=blue]
> By the way, if you -really-have a lot of links to update, you can loop[/color]
through[color=blue]
> the tabledefs collection, and rewrtie the Name property in code. I do[/color]
that in[color=blue]
> one of my apps since I have to delete and recreate the links to switch[/color]
between[color=blue]
> an MDB and PostgreSQL server back-end. I don't know why, but Access won't[/color]
let[color=blue]
> you change one type of link to another type.[/color]

I've faced all the same issues and written all the same code as you mention
above. For years I've used a form dedicated to switching links and link
names back and forth between test .mdbs and the ODBC version.

I've never found a single positive use for renaming table links (or view
links for that matter). Even if they're clunky names, I prefer to keep them
matching.

[Steve Jorgensen]

Re: Schema/User Name Oracle/Access linked tables

On Tue, 24 Feb 2004 01:15:37 -0500, "DFS" <nospam@nospam. com> wrote:

....[color=blue][color=green]
>> Finally, if you leave the schema names -in-
>> the link names, you are now making the front-end only work with one schema
>> name which makes the front-end much less flexible.[/color]
>
>But only if you find yourself using the same table from different schemas.
>Do you? Why?[/color]

Sure. I sometimes have multiple test databases in different states, and
re-link to switch back and forth between them.
[color=blue]
>I never have. Not once. Though I have used the same table from the same
>schema on a different box: dev vs. production.
>
>[color=green]
>> By the way, if you -really-have a lot of links to update, you can loop[/color]
>through[color=green]
>> the tabledefs collection, and rewrtie the Name property in code. I do[/color]
>that in[color=green]
>> one of my apps since I have to delete and recreate the links to switch[/color]
>between[color=green]
>> an MDB and PostgreSQL server back-end. I don't know why, but Access won't[/color]
>let[color=green]
>> you change one type of link to another type.[/color]
>
>I've faced all the same issues and written all the same code as you mention
>above. For years I've used a form dedicated to switching links and link
>names back and forth between test .mdbs and the ODBC version.
>
>I've never found a single positive use for renaming table links (or view
>links for that matter). Even if they're clunky names, I prefer to keep them
>matching.[/color]

I suppose that's a valid point of view. Probably, the truth of the matter is
that this is more a matter of personal taste than right vs wrong. You can
make it work either way. To me, though, starting with names that include
schema names, then building other objects that expect than name means that
it's now hard to change the app to reflect something different later if that
becomes more appropriate.