Link an access table to multiple database tables

[netangel]

My Oracle database engine has several tables, owned by different users. I need to link a table in an Access database to those tables, but, depending on the user.

This is easier to explain with a sample:

Tables in Oracle:
-----------------
user1.Table1
user2.Table1
user3.Table1

Table in Access (linked to the Oracle tables):
----------------------------------------------
Table1

Now depending on the user who loged to Access (user1, user2 or user3), I need to show the data in the correct table.

Any ideias???


NetAngel

 

[KenReay]

Hi

Relink at run time ?

This may perform slowly however, you would to try it and see

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[netangel]

Sorry, I forgot to mention that relink the tables at run time is not an option, because the Access mde is shared by all the users.

It is placed on a network share and everyone uses the same file. so, I can't relink it.

NetAngel

 

[KenReay]

Hi

Two thoughts

1) can you change the oracle database to have just one table, but with each row containing the user id, the restrict the rows Access uers can see via a query wit user id as criteria

2), if you cannot do 1) above, can you do a variation on it, ie in Oracle make a view using UNION so that you can present Access with what LOOKS like a single table, then filter on user id as suggested above



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[netangel]

That's the last extreme solution I see to solve this problem. I have two reasons to say this:

About adding a user column:

1. I can't add the column, because of the database structure. If I added a column, that would kill all my primary and foreign keys. In my sample I used only 1 table, but each user will have about 200 tables under its ownership.

2. I already have an Access application working with that database structure. By changing it, it would mean to rewrite it, and that's exacly what I'm trying to avoid.


About the UNION:

1. I can't union all the tables, because I can't say how many users exist, or their names, so I can't make a view with those unions. I've already thought about stored procedures that could do it, but those can't be linked in Access.



NetAngel

 

[KenReay]

Hi

Well no much point in my devoting any more time to thinking about it

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK