locked
Access losing IP address of SQL server RRS feed

Answers

  • Thank you for your detailed response – they should all be this informative and non-newbie-oriented!

    The browsing was turned on already, but you may be on to something about the networking.  The network branch in the file explorer only shows an unused server on the domain network and two units on a workgroup network used by the telephone system. 

    But although I can’t figure out how the SQL server IP address is getting lost, I think I’ve come up with a workaround:  simply take the DNS server out of the equation and map the server to its IP address using a HOSTS file on the problem machine.  I did this, changed back to the name of the server instead of the IP address in the DSN file and refreshed the connections.  So far so good, but I won’t know for certain if the problem is solved until it goes a week without a problem.

    Thanks again.

    Tuesday, December 19, 2017 4:43 PM

All replies

  • If you do try and use the IP address, then of course you have to re-link the tables.

    (this would be the "best" test + solution here). So I would try re-linking the tables with IP.

    And this problem can arise since often your computer may well have more than one network setup (say the default workgroup, and then perhaps another network such as your local company  network or even "homeGroup"– this setup even occurs when using workgroups and not a “domain” system common at a company network.

    So it could be that the computer is “choosing” or “flipping” to the other network.

    However, if you linked the tables using an IP address and that works and THEN it breaks the connection?

    That could well be an issue of power management. Win 10 is quite aggressive in terms of trying to power down a network card that not in use – that could in theory cause you to lose your network connection.

    So it not clear if you attempted linking tables by IP address, but I would try that first.  From what you suggest it does seem that the network use is “going away”, and thus it not really an Access problem, since as you noted you can’t even ping the SQL server.

    I would also ensure that the SQL “browser” service is running on the box with SQL server. I seen some cases in which you can link to the server, but connection issues will occur if you don’t run the SQL browser service on that SQL machine.  You “really” need the browser service running, and that is especially in the case of “named” instances of SQL server running (incoming connections will NOT know which instance of SQL server to connect to. So that “instance” part is this:

    \\ServerName\”INSTANCE

    Which more typically is:

    \\ServerName\SQLEXPRESS

    So my best guess so far is one of the above 3 issues.

    1)

    Ensure the SQL browser service is running (that allows incoming requests to be routed to SQL server)

    2

    Check the power management of the network card. Eg this:

    3)

    Do you have more than one workgroup or “domain” that is part of your network. Now for this issue, I don’t a great solution, but more than one workgroup/network connection can cause this, since your network that is given preference may “swap” on you.  So for this, I would simply remove the un used “homegroup” or “workgroup” if you have such additional networks setup as connection on your computer.

    Regards,

    Albert D. Kallal  (Access MVP 2003-2017)

    Edmonton, Alberta, Canada

    Monday, December 18, 2017 11:20 PM
  • Thank you for your detailed response – they should all be this informative and non-newbie-oriented!

    The browsing was turned on already, but you may be on to something about the networking.  The network branch in the file explorer only shows an unused server on the domain network and two units on a workgroup network used by the telephone system. 

    But although I can’t figure out how the SQL server IP address is getting lost, I think I’ve come up with a workaround:  simply take the DNS server out of the equation and map the server to its IP address using a HOSTS file on the problem machine.  I did this, changed back to the name of the server instead of the IP address in the DSN file and refreshed the connections.  So far so good, but I won’t know for certain if the problem is solved until it goes a week without a problem.

    Thanks again.

    Tuesday, December 19, 2017 4:43 PM
  • Hi ArtSnob,

    Thanks for sharing it.

    If it works without a problem, I would suggest you mark your reply as answer, and then others who run into the same issue would find the solution easily, which is also the way to close a thread here.

    Regards,

    Tony


    Help each other

    Wednesday, December 20, 2017 3:09 AM