none
Change SQL server newer version in linked table of the Access DB - Connection failed RRS feed

  • Question

  • I relinked the MS Access Database from SQL 2012 to SQL 2016.  The access accdb file is located at the network.

    Install the SQL Native Client 11.0 to my user's PC

    User click the accdb file.  User gets Connection failed error. MS SQL Sever Login

    SQL State '08001'  

    SQL Server Error:10054.

    TCP Provider: An existing connection was forcibly by the remote host

    Client unable to establish connection.

    User's PC is windows 7 and run 32 bits.

    Any idea to address the issue?

    Thanks!


    • Edited by Daphne1212 Tuesday, September 19, 2017 7:51 PM
    Tuesday, September 19, 2017 4:34 PM

Answers

  • It should not matter. However, if you link the tables using anything other than the standard “default” legacy SQL driver, then each workstation WILL have to be upgraded and you have to install the native 11 driver on each workstation.

    So if you link say using the native 11 SQL driver, that driver is NOT included by default on each workstation. You thus have to install the native 11 driver. So if you link on a workstation station with native 11 drivers, then using the application on a workstation that does not have native 11 SQL drivers will fail. (This explains the failure on some workstations – it not the OS issue, it is using a different or in this case un-available driver).

    I tend to use the legacy (default) SQL driver since it is installed by default on recent versions of windows. That driver is:

    SQL server.

    E.g. this one:

    However, if you database uses any “newer” columns and data types such as datetime2, then this default driver will display date columns as text – this tends to really mess things up.

    So if you use any newer datetime formats in that sql database, then you have to bite the bullet, and use this driver:

    However, while the native 11 driver is preferable and a better choice, the downside is you WILL have to install this driver on each workstation. So this “choice” will much depend on if you using newer datetime columns in SQL server. If you are not, then I would for ease of deployment go with the older “standard” SQL driver.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by Daphne1212 Tuesday, September 26, 2017 2:32 PM
    Friday, September 22, 2017 7:10 PM

All replies

  • Check the Event Viewer on one of the machines that cannot connect, and check the SQL Error log.

    Also, users should NOT share the same "frontend" file (the one with the forms, reports, etc). You should make copies of that file and distribute it to each user. Often we'll use a batch file to do this, but you can do it however is simplest for you.


    -- Scott McDaniel, Microsoft Access MVP

    Tuesday, September 19, 2017 8:19 PM
  • Not sure why no Errorlog in SQL database.  Relink the new server 2016 from 2012. Nothing was changed. The accdb file in the network for everybody using as always.  No issue before relink them in new version. 
    Wednesday, September 20, 2017 12:34 PM
  • Did you check the Event log on one of the machines having troubles? It may point to issues with the driver, for example.

    Was the utility developed on a machine running the SAME version of Access as your users are running (including the bit-ness of the OS and Office)?

    The accdb file in the network for everybody using as always.

    That's fine, but it's NOT the right way to deploy a multipuser Access application. It's a simple matter to create a batch file on the user machine that (a) copies the FE from the network location to a local folder and then (b) launches that FE. Multiple users running the SAME copy of an Access database cause quite a few issues, and could actually be the root cause of this.


    -- Scott McDaniel, Microsoft Access MVP

    Wednesday, September 20, 2017 1:38 PM
  • Thanks so much!

    Batch file on the user machine that (a) copies the FE from the network location means:

    And run the batch file daily at Window schedule?

    That keep the files as latest version after any update on network?

    • Edited by Daphne1212 Wednesday, September 20, 2017 6:48 PM
    Wednesday, September 20, 2017 5:10 PM
  • I would consider re-linking the tables with the linked table manager. It possible you linked using some user or machine specific process – thus when you distribute to other machines, they don’t have the same setup.

    So, first, on that machine, when you re-link, you have to create a new DSN (ALWAYS create a file dsn). Once done, and if the link works, then try that on other workstations.

    So do the linking process on the workstation. If it works, then copy that to the folder you using to “distribute” that front end to the other workstations.

    So I would first try a table re-link using the re-link table manager. If the re-link process don’t work, then copying the front end to other stations of course will also not work.

    In other words, have you been able to successful link to the 2016 SQL server at any point in time? (a big question I suppose?).  Perhaps some ports or TC/IP is not enabled for that instance of SQL server (SQL server express does not enable TC/IP by default). Once you get a connection working, then I suspect the application will work from any workstation on the network.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Wednesday, September 20, 2017 10:10 PM
  • get a connection working is depend on the file is re-linked in which operating system to get the setup connection.
    Friday, September 22, 2017 5:31 PM
  • It should not matter. However, if you link the tables using anything other than the standard “default” legacy SQL driver, then each workstation WILL have to be upgraded and you have to install the native 11 driver on each workstation.

    So if you link say using the native 11 SQL driver, that driver is NOT included by default on each workstation. You thus have to install the native 11 driver. So if you link on a workstation station with native 11 drivers, then using the application on a workstation that does not have native 11 SQL drivers will fail. (This explains the failure on some workstations – it not the OS issue, it is using a different or in this case un-available driver).

    I tend to use the legacy (default) SQL driver since it is installed by default on recent versions of windows. That driver is:

    SQL server.

    E.g. this one:

    However, if you database uses any “newer” columns and data types such as datetime2, then this default driver will display date columns as text – this tends to really mess things up.

    So if you use any newer datetime formats in that sql database, then you have to bite the bullet, and use this driver:

    However, while the native 11 driver is preferable and a better choice, the downside is you WILL have to install this driver on each workstation. So this “choice” will much depend on if you using newer datetime columns in SQL server. If you are not, then I would for ease of deployment go with the older “standard” SQL driver.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by Daphne1212 Tuesday, September 26, 2017 2:32 PM
    Friday, September 22, 2017 7:10 PM
  • You could do it daily, or you could run the batch file each time they open the application. With the speed of networks today, users generally don't notice any lag.

    -- Scott McDaniel, Microsoft Access MVP

    Friday, September 22, 2017 7:15 PM
  • Thank you! Albert,

    I will go with Native 11 driver. I didn't get the good msi to fit clients PC with both Wx2 and Wx64. Allowing using my accdb file created from x64 with Access 32.

    Monday, September 25, 2017 1:31 PM
  • Thanks you! Scott,

    I will learn to modify the accdb to run the batch file. The batch file will be put in their C drive.

    Batch file will do copy accdb file from Network drive to C:\Database\accdb file.

    Not sure how to make it run when the user click the file from C:\Database\accdb file.

    Thanks!

    • Edited by Daphne1212 Monday, September 25, 2017 7:57 PM
    Monday, September 25, 2017 1:33 PM