MS Access – Fix ‘Network Connection Was Interrupted’ Error

Note: SQL Server Agent itself does not access the share

It depends on how you have mapped linked server security between local and remote credentials. SQL Server Agent running as local system will connect to SQL Server, which then accesses the share based on linked server credentials.

SUSER_SNAME will be different of course in this case to when you run it.

Options:

• add a linked server login mapping for "local system"
• configure all local credentials to use one remote login
• look at SQL Server Agent proxy accounts for the job step (changes SUSER_SNAME)

Access most certainly supports a network and multiuser operation right out of the box. You don't have to change your code or really do anything special.

However to keep in mind while Access is tremendously easy to use on a network, and many people often utilize access with multiple users on a network, it has its limitations.

There are many issues to consider, but the basic concept is that your network has to be very solid, I can't say the network has to be above average, but Access as a product in a network environment is quite sensitive to poor connections.

So if you're just talking a couple computers on the Office Network and you share a folder on one of the computers or your server, then access can work quite well on this environment. However if your network is a wide area or has users connecting over the Internet, then such setups tend to be quite problematic.

As a general rule, it's recommended that you split your application into two parts. You place the program part on each computer, and this task is not require any extra programming skills on your part, but it does help enormously in terms of reliability.

I explain this concept of splitting and how you run access in a multi user environment in the following article of mine:

http://www.kallal.ca/Articles/split/index.htm

And a primer on the different kinds of networks, and some of the obstacles as you may encounter are outlined in the following article of mine:

http://www.kallal.ca//Wan/Wans.html