0

We have a MS-Access front application using data from a SQL server back-end. We are using ODBC connections for the data communications. The application randomly getting frozen or crashes giving different errors as attached below which very likely seems to be issues related to ODBC connection. Problems can be recreated by running different queries in quick successions.

SQL Server Version: SQL Express 14.0.3356.20

Access Version: Microsoft 365 Apps for Business Version 2011 (Build 13426.20204)

Most common error codes: 10054, 10060, 3146, 3151

ODBC Data Source Type: System DSN

ODBC Driver: ODBC Driver 17 for SQL Server (Version 2017.176.01.01)

Example Connection String for a linked table: ODBC;Description=Test Description;DRIVER=ODBC Driver 17 for SQL Server;SERVER=MY-SERVER;UID=MyUser;Trusted_Connection=No;APP=Microsoft Office;DATABASE=TestDatabase;;TABLE=dbo.TableName

Largest Table Size: 53000 records (Doesn't really matter, fails for even smaller queries, but if you try faster getting results from different queries you can break it)

Error1 Error2 Error3

We have gone through hundres of searches/articles about these and applied the fixes listed below, but still couldn't get the problem solved.

We have tried below solutions as of now which didn't help.

  • Created firewall rules (Inbound - TCP Port 1433, Outbound - TCP all ports)

  • Added TCP Port 1433 - TCP/IP protocol

  • Enabled Namepipe

  • Updated SQL Server Express from 14.0.3335.7 to 14.0.3356.20

  • Made IP4 and IP6 are both enabled

  • Enabled and checked ODBC Trace Logs

  • Checked Windows Event Logs

Hope I have included everything that might be useful to get some help resolving this issue.

Note: Not sure if it's related, but the SQL Server itself (in the backend) giving the below error in rare occasions if it helps.

Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=29278; handshake=26244;  (.Net SqlClient Data Provider)
Jay
  • 24
  • 7
  • https://stackoverflow.com/questions/15488922/connection-to-sql-server-works-sometimes It's almost certainly a networking issue. Do you have a faulty/misconfigured switch? – Mitch Wheat Jan 05 '21 at 00:48
  • Have you tried setting TransparentNetworkIPResolution=false in the connection string? https://learn.microsoft.com/en-gb/archive/blogs/dataaccesstechnologies/connection-timeout-issue-with-net-framework-4-6-1-transparentnetworkipresolution – Mitch Wheat Jan 05 '21 at 00:52
  • Thanks @Mitch. I will try changing the connection string and let you know the results. – Jay Jan 05 '21 at 01:02
  • Is the SQL Server running in an ESX/i guest? Do you have the recommended virtual NIC configuration and drivers installed to suit the guest OS? – AlwaysLearning Jan 05 '21 at 13:41
  • @Mitch Wheat, When I add `TransparentNetworkIPResolution=false`, it is always giving 3151 error – Jay Jan 06 '21 at 00:14
  • each user has their own front end file? the PCs with the front end file - are they wired ethernet LAN or using wifi? assuming this problem is new: what has changed? – Cahaba Data Jan 07 '21 at 01:56
  • Can I know the reasons for down votes please? I have done all the research and posted everything clearly in the question and also posted the solution worked for us. Thanks – Jay Jan 11 '21 at 01:53
  • thanks for posting your fix. can I ask what edition/version of Microsoft Access? i.e.2010, 2013, 2016, 2019, 365 ?? ... this might be germane to compatibility of the SQL edition... – Cahaba Data Jan 11 '21 at 21:18
  • It's at the top of my question. Access Version: Microsoft 365 Apps for Business Version 2011 (Build 13426.20204) – Jay Jan 11 '21 at 23:40
  • @Jay I upvoted and agree that the downvotes are not warranted. Your answer is of course not very satisfactory since you kind of "punted" by moving to 2019, but your statement of facts and research is helpful. – batpox Aug 02 '23 at 09:15

1 Answers1

0

Thanks everyone who tried to help. This issue has now been fixed. For anyone who will come here in future looking for a solution for a similar issue, below is what worked for us.

  • Added a new instance of SQL Server 2019 (in the same virtual machine which 2017 sits on)
  • Enabled UDP Port 1434
  • Changed virtual port from blank to zero

2017 was still crashing before taking offline with the same settings. No issues with the 2019 version.

Jay
  • 24
  • 7