Access front end hangs when more than one user

[mafrank101]

OK, I'm sure once we get this resolved I'll feel like an idiot, but here goes.



We have an Access 2007 front end that has been linked to an Access 2003 back end database. This has worked fine for years, but I am trying to move the back end from Access 2003 to SQL Server 2005. I used the Export Data tool to move the tables from Access to SQL Server, and then relinked the Access 2007 front end to the SQL Server database using a File DSN that's on a shared drive on the network.

Now, when a single user goes into the Access 2007 front end, everything seems to work just fine. They can open forms, reports, enter data, etc. But as soon as another user tries to open the Access 2007 front end, both user's screens hang, and eventually a ODBC-Call Failed Timeout Expired error pops up. If one user closes Access while they are both hung, then the other user gets control back and can go about normally.

The SQL Server is using Windows Authentication, and the users are all part of an Active Directory group which has been added to the SQL Server.

The Access 2007 and File DSN are both in a shared network folder. The users all use the same Access 2007 front end file, I have not copied it out to everyone's PC. The error occurs after the database is opened, and the click on a button that should open a form - that's the first time the front end would try to connect to the SQL Server database.

I've done some searches but can't find an answer to a similar problem, so I'm throwing this one out there for some help.

Thanks

[mafrank101]

I did some more testing, and here are the results so far.

I made a copy of the Access 2007 front end, and when I ran both of them from my PC at the same time, I got the same locking issue as before. I also found that I could not run SP_Who2 in the SS Management Studio while the two Access databases were hanging.

I then made a copy of the file DSN, and linked the copy of the Access front end to it. After doing this, I was able to run both copies of the Access front end without troubles.

So, it appears the problem is having more than one connection to the File DSN. That seems odd to me, as I thought the purpose of a File DSN was to simplify multiple users connecting to the same datasource. Would it make more sense to simply put a standard ODBC connection on all the user's desktops?

Thanks

[mafrank101]

Just to close the loop, after doing some more fiddling, I found that if I removed the UID & Password rows from the File DSN, everything seems to work better now.

[Missinglinq]

...The Access 2007 and File DSN are both in a shared network folder. The users all use the same Access 2007 front end file, I have not copied it out to everyone's PC...

Sadly, I suspect that this resolution will be short lived! Being in forced retirement for the last 6 years, I spend about 8 hours a day, 7 days a week, cruising this and four or five other Access forums and answering questions, and I have read this exact same story dozens and dozens and dozens of times before!

Having multiple users running off of a single Front End is really little better than having everyone run off of a single, non-split app. And doing so can cause all kinds of problems, but most notably those that you are now experiencing. And, as you have found out, you can sometimes get away with this for an extended period of time before the problems raise their ugly heads! But once the problems show up, they always become chronic, happening over and over and over again! The only resolution that I've ever heard of for solving the problems is to do what should have been done in the beginning; place a copy of the Front End on each user's PC.

BTW, the record for one of these errant apps running without problems, before going into this kind a tailspin, was 9 years! Apparently everything can run along just fine for a period of time, and then some convergence of events by two or more users triggers the app into a terminal funk!

Or perhaps it has to do with the alignment of the Planets in the Universe!

Or maybe the Access Gnomes just felt like having some fun!

Bit the bullet and give each user a copy of the Front End.

Linq ;0)>