Prolem with ODBC

[Aloha]

I'll try to make my post as clear and short as I can! It's Sunday here I really don't want to bother your nice weekends at all, but I must! Then let me begin!

My sister's school used a program for 2 years. It worked fine on around 40 client computers (Pentium 4, XP SP2, 512 MB of RAM). Now, this program has a problem when it runs on around 60 computers (Core 2 Duo, still XP SP2! 1GB of RAM). These computers cannot get access to the database on the server so well as they used to do. The client computers are connected to the server through ODBC (Open Database Connection???). When a computer cannot access the server, all applications on it have to be closed and restarted.

The man who is responsible for the network of the school keeps saying that the problem comes from the program, not from any other hardwares! My sister, who is responsible for this program, tried using an old computer with old build and it still works fine. She doesn't know what is the real cause of the problem. She doesn't know about the hardwares very well, either. She needs to explain the problem to the school, I think.

What may be the causes of the problem? Any advice and answers here are highly appreciated by her.

PS. The big difficulty she has to confront is that she can't use English to ask about this by herself. I am just a translator for her and this task is not easy at all! There are many words I don't know how to call in English. If you don't understand something of what I said, or need more information, just tell me and I'll try to explain it again. Many thanks in advance!

Edited October 20, 2008 by Aloha

[Mordac85]

ODBC is very simple but can really end up being a pain if it isn't setup correctly. I'll assume this is XP because of the forum this is in so go to a system that is working fine. Then go to the Control Panel (Start->Settings->Control Panel) and under Administrative Tools open the Data Sources (ODBC) snap-in.

Now I have no idea how the database is accessed or what type it is, but look through the User DSN, System DSN or File DSN tabs to see if you can find one that stands out as the one for this application. Then highlight that DSN, note the driver used and hit the Configure button. Now write down all of the settings that you can about this. When you're done, hit Cancel to avoid applying any changes you may have made while looking at this.

Now go to a system where the application is not working and compare the ODBC settings. Chances are you either do not have an ODBC entry for the application, you don't have a database driver loaded or there are incorrect settings.

If this is a Microsoft Access database based application, you may run into connectivity issues with multiple users on a network since Access is not designed to be used that way. Knowing the application or database type would also help.

Edited October 19, 2008 by Mordac85

[Aloha]

Thanks for your reply, Mordac85.

Sorry for my bad explanation in the previous post. I'll manage to add more information here.

You are right! This application is based on Access 2003, accessing to the database through SQL Server (I hope I express this right, and hope you can understand it!). And it worked fine on older computers (Pentium 4, XP SP2, 512 MB of RAM) for two years.

Recently, all the computers (from 40 increasing to 60 in quantity) are replaced with Core 2 Duo and 1GB of RAM. Now it has problem. I made a big mistake when saying the client computers cannot access to the database. Actually they can, but have many difficulties. I mean, when they run the application on a new client computer, there is intermittence in the connectivity after a few minutes. Even the computer may crash and they have to restart it. And this keeps happening again and again.

Then we can say the cause is not incorrect settings. I pay a special attention at what you said:

If this is a Microsoft Access database based application, you may run into connectivity issues with multiple users on a network since Access is not designed to be used that way.

I think maybe this is the cause. But, my sister doesn't agree. She tried using an older computer to run this application and it worked perfect. It did work perfect on all old computers as I said before. She can't stand that! She wonders why a more powerful system can't accept or run this program smoothly. Is it because that a Core 2 Duo machine doesn't allow running old database based applications on it, Access for example? She is thinking of converting the application from Access 2003 to Access 2007 to see if there is any difference.

I forgot to say this. My sister said these computers are used to surf the internet too. So may it be one of the causes of the problem too?

Thanks again for your reply, Mordac85. I'm sorry for the slow answer. I have to wait for my sister's reply and she is busy all day long!

[Mordac85]

The issue with an Access database on the network is usually found when all of the data is stored in the Access database and you start increasing the load of concurrent users on the database. However, it sounds as if the data is stored on a SQL database and it uses Access as a front-end to the data. The hardware shouldn't have an impact on the ODBC connection. However, if you are using a different version of Access on these systems it may be causing you some issues. I would certainly recommend converting a copy of the Access database to Access 2007 as a test. It may be an issue with backwards compatibility and not so much ODBC.

[Aloha]

Mordac85, thanks for a fast reply.

Then she will follow your suggestion to test it. This can't be done soon because her school is having a lot of final exams for the students now. We hope the problem can be solved after the test.

I pass her saying thanks to you, Mordac85. Goodbye, friend.

[Aloha]

Mordac85, too bad the problem can't be solved yet.

The disconnection happened on Access 2003 after 5 minutes of running the application. When she tried with Access 2007, the problem happened after 10 minutes of working.

Edit: removing the image.

Edited October 29, 2008 by Aloha

[Tripredacus]

You have failed to mention the operating system on the server where the database is stored.

I am not sure if any of the following information is relevant:

50 instances on a stand-alone server for all SQL Server editions except for Workgroup. Workgroup supports a maximum of 16 instances per computer.

http://msdn.microsoft.com/en-us/library/ms143432.aspx

Basically I am thinking that the server is in fact fine, that the client is being rejected. You may wish to check on the server for any errors regarding credentials, authentication or connections in relation to MSSQL. I am thinking that you may be reaching a connection limit on the server, whether with MSSQL or another service.

[TravisO]

You are also hitting a situation where you can see Access does not scale well, even when it's only just a UI front end. She might need to "rebuild" the application in a proper setup, such as a .NET application. It would be a good idea to use all Stored Procedures instead of queries, to make the transition easier.

It sounds like to me, Access & ODBC hogs a connection, where as ASP.NET or a .NET desktop application allow the developer to close the connection the moment you're done with it.

Edited October 28, 2008 by TravisO

[Aloha]

Tripredacus and TravisO, thank you for the answers.

You have failed to mention the operating system on the server where the database is stored.

It is Server 2000 with SQL Server 2000 on it.

I managed to translate all of your ideas to her. And she has come to the following solutions:

- Trying to update the SQL server to a newer version (2003 or 2005 or ...) within the ability of the school.

- Trying rebuilding the application in a proper setup. (Not sure if she can!)

If the above trying doesn't help, she will suggest the school to buy another program to use. Maybe it's not an Access database based application any more. This will surely cost a lot! But this information is really interesting to her:

It sounds like to me, Access & ODBC hogs a connection, where as ASP.NET or a .NET desktop application allow the developer to close the connection the moment you're done with it.

She said the application could still run on an old desktop and around ten other laptops brought to school by some teachers. These computers are connected to the server through either cables or wireless connection. And they work just fine! No disconnections at all. She cursed when telling me about that. It's the first time I've heard a female teacher swearing! How nice!

OK, thanks again for all the help. My sister decided that she and the school should manage to solve the problem without any more questions. I hope you all don't mind when I copy your anwers for her so that she can discuss them later with her colleague.

PS. Now I feel that my topic wasn't posted in the right forum. If so, may some mods move it to a proper place for me, please? Many thanks!