ODBC--call failed (Attempting to open a table in MS Access)

Question

First of all, I know there are a few other questions with a similar issue but I really would appreciate a clear and working answer.

My colleagues and I have tried opening the database from Access (front-end) and this error only came up when either of us tried opening one table or a query. I checked my ODBC and I have the Native Client for SQL 11.--- etc.

They were able to access the database after a couple attempts.

Notes: The backend is MS SQL Server 2008 R2 The frontend is MS Access Users run Windows 10 on their local machines and access the database frontend through MS Access. Only two users are able to access the backend from their local machines, myself included.

Can someone share any notes that are relevant to this issue? Kind answers please because I have received some really sarcastic answers and I am new to database management in a large organisation.

Any assistance will be greatly appreciated.

Update: My superior told me and I noticed as well that the semi-colon is dropped when passing a query. I will get more information on this tomorrow but I have a screenshot of what I am trying to say:

The yellow mark indicates where the semi-colon supposed to be

Answer 1

That error message suggests that your connection was “cut” or “stopped”.

In most cases, this is due to the computer “going” to sleep mode. Or often the computer puts the network card to sleep to save power.

Recent versions of windows (especially windows 10) is more aggressive for power saving. I would bring up the properties for the network card, and turn off power management.

And ensure that the computer does not go to “sleep”, as that also will give your error message.

Of course since you using native 11, then that driver has to be installed on each workstation. For this reason I often still use the very old “SQL Driver”, as opposed to the native 11 driver since the older driver is installed on windows by default.

Since you find that connecting DOES work, then clearly your ODBC connection is working.

That error message suggests that the computer(s) in question are going to sleep, or the network cards are being put to sleep by windows. You want/need to prevent both of these issues from occurring.

If changing the sleep issue, and turning off windows allowing the network card to power down does not fix this, then this suggests some kind of hardware problem on your network.

I would also turn off Wi-Fi if you using laptops, since that often results in two different network connections, and windows will sometime “flip” or change the network connection used, and you then again get that error message.

I also seen this message when your computer has more than one network setup. Say a workgroup + a “company domain” setup. And again the computer will “switch” between the two networks, and thus resolving to the SQL server is lost.