2

I'm in the midst of moving my company's timekeeping system from an old, slow Access backend to a snappy and quick SQL Server backend. What I'm finding though, is that while the frontend has no problem connecting to the backend on my machine or a handful of others, on most of the machines here, it just doesn't seem to work at all.

I'm linking tables using the CreateTableDef method, found here. An AutoExec macro establishes the tables at start-up. On my machine, running Windows 7 Professional 64-bit with Access 2016 (Version 1801, Build 9001.2171), it opens up with no issues. Another, rather disparate machine out in the shop, running Windows XP 32-bit with the Access 2010 Database runtime engine (this machine does not have a full version of Office, only the runtime) also works without a problem.

Other computers here in the office, however, running the same version of Office, with Windows 7 or 10, always 64-bit & Professional, when opening the .mdb file, fail to connect to SQL Server.

In one case, Access will hang for up to 30 minutes, before spitting out

ODBC--connection to '(server name here)' failed.

I've tried running a server profiler while Access is attempting to establish a connection, and what I'm seeing is that the server apparently isn't seeing the connection attempt, at all. This particular machine also runs a separate utility written in VC# to connect to and manipulate the database, which runs without a hitch. Only in the Access frontend, do I encounter problems.

So, in summary:

  • Microsoft SQL Server backend
  • Access frontend
  • Access frontend has no problems connecting on some machines, fails to connect on other machines.
  • Separate utility written in VC# works fine everywhere it's tried, including machines where the Access frontend doesn't work.
  • Profiler trace on server doesn't show any connection attempt being made by machines where the Access frontend doesn't seem to work.

I've tried installing the Access Database Engine, I've tried installing SSMS on one machine (no dice), I've installed as many different ODBC drivers from Microsoft as I can find, but nothing seems to make a difference. I'm inclined to think that the problem is not the driver, but some kind of network-related issue, and somehow specific to Access.

Any ideas? Is there some arcane software requirement that I'm just not aware of?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Garrett Courtney
  • 31
  • 3
  • I suppose I should note, that on machines where it doesn't seem to work, after the "ODBC--connection failed" error does its thing, it will ask for a username and password, with said information already filled in. At this stage, hitting OK, while changing nothing, will make all the connections, and it'll apparently work. Over time, however, that connection drops out. The interval between connecting and dropping is not immediately obvious. – Garrett Courtney Mar 02 '18 at 01:02
  • Please provide the actual code you're using (including connection strings with servername, user and, password censored) – Erik A Mar 02 '18 at 07:39
  • Also you don't mention if your network is Active Directory managed or not. You also don't mention if all users share the same front end file. – SunKnight0 Mar 02 '18 at 16:24
  • This network is a Windows domain, run by a Windows Server 2008 SBS domain controller. All users are using the same .mdb file. The connection string is generated by the VBA scripted linked in my post. Users are logging in using SQL Server Authentication, username and password are hardcoded in the AutoExec macro, which calls AttachDSNLessTable once for each table being linked. I've also tried using Windows Authentication, but that doesn't change the results.I feel like an important clue, is that the initial attempt to connect doesn't appear when I use SQL Server Profiler. – Garrett Courtney Mar 02 '18 at 16:49

1 Answers1

1

So, I found this thread, where someone has having trouble connecting on a first attempt. Turns out that this is a firewall issue. By nailing down the port the SQL Server instance is using, and explicitly opening it in the firewall, all connection issues apparently go away. Why it worked without issue on some computers, I still have no idea, but after making this change, everything does what it's supposed to.

Garrett Courtney
  • 31
  • 3