Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DNS ODBC Reconnect (A2k)

    Hello.

    I have a Access 2000 MDB which uses tables in 3 different mysql databases which are hosted in our offices on a server computer named P99.

    Setting up ODBC links Windows>control panel>admin tools>ODBC

    I define the 3 DNS connections on all computers (computers inside the LAN and outside the LAN with the exact same names)
    Those DNS names are: DB_red, DB_green, DB_blue.

    On Computers inside the office LAN I setup the 3 DNS using: P99 as the server name.

    On Computers outside the office LAN I setup the 3 DNS using: zebraserver.com as the server name

    All that works great.

    Problems arise when I do some DB development on a copy Access MDB inside the LAN and then send a copy of it to a PC outside the LAN.

    The MDB seems to be stuck trying to use the LAN DNS config to find the ODBC tables (on P99 rather than zebraserver.com which is what it should be using)

    This can be corrected by using linked table manager. BUT upon closing and re-opening the problems returns.

    In Access using tools>options>show system objects check box I can reveal a table called MSysObjects which shows the DNS string data. I can see that it still has in it P99. Doing a MSWord style CTRL-H and replacing all the P99 with zebraserver.com seems to be what I need to do.

    I have seem some solutions that provide a re-connect/refresh but seem to be only for situations where all tables are in one DB on one server.

    Any thoughts on how I may programatically solve this issue.

    The solution I usually use is to delete one by one all linked tables and then relink them all. This is slow.... but if I must do this. Is there are way to delete ALL linked tables.

    Also if I use the linked table manager...Is there a way to get those changes to STICK even when close and reopening MDB

    Thanks very much.

    Jason

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DNS ODBC Reconnect (A2k)

    What happens if you do the following:
    - Press Alt+F11 to activate the Visual Basic Editor.
    - Press Ctrl+G to activate the Immediate window.
    - Type the following, while substituting the name of one of the linked tables:
    <code>
    ? CurrentDb.TableDefs("tblCustomers").Connect
    </code>
    - Press Enter.

    Do you see the DNS name in the returned string or the connection string containing either P99 or zebraserver.com?

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DNS ODBC Reconnect (A2k)

    Hans.

    thanks for the reply.

    ? CurrentDb.TableDefs("tblCustomers").Connect
    does return the DNS string. It contains P99 (thought the DNS defined on this PC is for ZEBRAserver.com)

    Tried it again after using the linked tables manager to refresh the tables links ..
    ? CurrentDb.TableDefs("tblCustomers").Connect
    returns the DNS string... still with P99
    Though the tables connect so the mdb is using zebraserver.com (somehow)

    Tried it again after Compress and Repair. Tables connect fine but the command still returns P99

    Any more thoughts.

    Thanks

    Jason

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DNS ODBC Reconnect (A2k)

    Hmmm - there goes another idea. I had thought that it might be possible to use code to replace P99 with Zebraserver.com in the Connect string, but if the tables connect correctly to Zebraserver.com even though Connect contains P99, that won't help.

    It may be best to maintain two versions of the frontend, and import new versions of the queries, forms, reports and modules if needed.

Posting Permissions

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