renaming table names

[TjS]

Hellow,

Little question, hopefully not a big answer

i have four oracle db'ses mydb_db_test, mydb_db_production, mydb_db_develop

within access i have linked tables from the first one; let's say: mydb_db_test.tblOne, mydb_db_test.tblTwo

What i want to do is:
1) import the same tables from the other trhee db'ses
2) rename the table names by vba code
example: my_db_db_test.tblOne has to become tblOne
and when i switch from db (to for example the production), the tblOne has to be renamed originally (mydb_db_test.tblOne) and the other has to be renamed (so mydb_db_production.tblOne becomes tblOne).

Now the question which you probably ask me:
Why not by a connection string change...

Well, that's the problem, the linked tables are being set to readonly, and the property cannot be changed (at least, as far as i tried); so that's why i thought of this workaround. By linking all three databases, i also always have for those table the three connection strings, and by renaming them (i now do this manually) i always can pick the right connection.

Thanks in advance for the advise!

 

[jdraw]

TJS,

You mention 4 databases but only list 3. You mention linking and import as if they were the same.

If you're using ACCESS and Oracle, you have the Linked Table Manager available to you and you can select your ODBC data source when prompted.

Your rights with the Oracle database and tables is determined by what privileges you have in Oracle. The Oracle dba can assign/change your "grants" on specific tables.

Could you be a little more specific on what you're trying to do?

 

[TjS]

Do Until rst.EOF

    Set tbl = db.TableDefs(rst!TableName)
    tbl.Connect = rst!PathToBe
    tbl.RefreshLink
    rst.MoveNext
Loop

Above code is one of the options for me to change the connection string of a linked table.
I have made a table in which i have put the PathToBe and the PathAsIs as fieldnames, the tables which have to be changed are mentioned.

When the code tries to refresh the link, it isn't possible because appaerantly the properties cannot be modified.

Actually that's my problem and also why i am trying to search for workarounds (in stead of re-linking the tables from the different database)

 

[neileg]

Why not use a query? You can give it any name you want.