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.tablename 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]

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, da***********@hotmail.com (dave71) wrote:

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.tablename 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

[DFS]

"dave71" <da***********@hotmail.com> wrote in message
news:ca**************************@posting.google.c om...

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.tablename 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

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]

On Tue, 24 Feb 2004 00:14:02 -0500, "DFS" <no****@nospam.com> wrote:

"dave71" <da***********@hotmail.com> wrote in message
news:ca**************************@posting.google. com...

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.tablename 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

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.

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]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:dm********************************@4ax.com...

On Tue, 24 Feb 2004 00:14:02 -0500, "DFS" <no****@nospam.com> wrote:

"dave71" <da***********@hotmail.com> wrote in message
news:ca**************************@posting.google. com...

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.tablename 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
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. Ifsomeone inherits the system from you, they'll have to know this caveat. Theschema names might help when writing pass-through queries.

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

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.
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.

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.

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]

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

....

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.
But only if you find yourself using the same table from different schemas.
Do you? Why?

Sure. I sometimes have multiple test databases in different states, and
re-link to switch back and forth between them.
I never have. Not once. Though I have used the same table from the same
schema on a different box: dev vs. production.

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.

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.

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.