Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: Linked Tables when ODBC connection details have changed

    My DBA has moved an Oracle database to another server. I have many linked tables and relationships but now none of them work because the Oracle connection properties have changed. Changing the value in the DNS file is okay for new links but the old links are not available.

    I'd hate to have to re-link all these tables and build the relationships again but that may be my fate unless someone comes up with a good suggestion.

  2. #2
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244

    Re: Linked Tables when ODBC connection details have changed

    You've probably already considered this, but why can't you just use the Linked Table Manager in Access to re-assign your links?


    Originally posted by d0325mgray
    My DBA has moved an Oracle database to another server. I have many linked tables and relationships but now none of them work because the Oracle connection properties have changed. Changing the value in the DNS file is okay for new links but the old links are not available.

    I'd hate to have to re-link all these tables and build the relationships again but that may be my fate unless someone comes up with a good suggestion.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    Erm...that seems to be the only option right now unless I hear from someone who knows another way. The problem is there are many tables with some complex relationships. I have to drop the relationships, re-link the tables and then test the thing.

  4. #4
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    In principle you could write some code to loop through the Relations collection, drop each one, then loop through the Tables collection and if an Item is a linked table, delete it and re-create it, and its relationships, using the new path. You could simplify this operation by putting the names of the tables concerned, and the new path to link to, into a table. You could also add columns specifying the names of the primary and foreign keys, so that your code knows how to re-create the relationships. You could then loop through the table, reading the information and carrying out the appropriate operations. The question would be whether this would take longer to write and test than doing the whole thing manually!!
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    I thought about writing similar code but I am not a VBA expert and the tradeoff, doing it manually, won out!

    Thanks!

  6. #6
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    My absolute pleasure Dennis. Any time. Good luck!!
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •