Access97, ADO and MS SBS 2000

Hello all

I have a simple problem. I have a VB6 application that uses ADO to link to an Access97 database. Old technology I know, but it has been working fine in multi-user environments for some time without any problems.

I have recently carried out a new installation on a Microsoft Small Business Server network which has Windows XP on all of the clients.

The problem is this; When 1 user is working on the system it is very, very fast. So fast that even I was impressed. However, as soon as any one else logs in both users and any subsequent users slow down to a crawl.

I have other users who have a SBS 2000 server but they are not exclusively using XP clients. They are not experiencing the same speed (or lack of it)problems.

I did a quick test and moved the database from the server onto one of the client machines and shared the drive, folder and mdb file. The system was very, very fast even with 3 users logged in. The only problem with this set-up was that on certain occasions I was getting sharing problems. i.e. the database was locking for some reason.

I investigated further and found that when the client application was creating tables (it creates temporary tables in the database on occasion) or when a recordset was left open, all other client were locked out of the database. So it appeared that the local share was locking the whole database and not unlocking it when the create table action was finished and was also locking the database when a recordset was open.

This led me to think that maybe when the database is located on the server, maybe it is also being locked and any transactions are being queued until the database is released. Leading to very slow database response times.

Has anyone else had problems like this?

The network providers are saying that they have installed a standard SBS2000 network and the application is at fault. I have a customer stuck in the middle who is using the application in single user mode until the issue can be resolved.

Any help would be appreciated.

:confused:

I've had a ton of issues using Access in a multi-user environment. If it's possible, you could change the database to MSDE, and it should solve all these issues.

Thanks for the reply VB Joe

A few questions:

is MSDE free?

Secondly where can I get it?

Lastly will I need to convert my app to ADO from DAO? If so I would rather do this when I am not under customer pressure to get things working.

I know this application has been running fine on NT4 and is working fine on SBS2000 and now S2003. It just appears to be this installation. It is running on a HP server. I am not sure if that makes a difference.

:confused:

MSDE is a free version of SQL Server, and it works well for multi-user applications up to about 20 users. You will probably have to change from DAO to ADO, unfortunately. To make it work, you might need to put the database on another machine.

If you're leaving Recordsets open, and that's locking the database, then unfortunately, the application is probably at fault.

Gotta agree with VBJoe here. If you switch to MSDE, you customers will be much happer.

Although MSDE is limited with throughput throttling to approximate Access' multi-user performance when number of concurrent connections is greater than 8, MSDE won't ever get corrupted or bloat like Acess does. In addition, it's a true database server, so you can use server-side cursors, which will reduce network loading substancially.

If you provide an MSDE database, and the client already has SQL Server installed, you can move your database into their existing server and "piggyback" on their existing infrastructure. (Or, you can install a new instance of MSDE.)

In addition, it's scaleable. If your app gets installed on 40-50 desktops, then the customer can invest in a full-blown instance of SQL Server, and your app won't need to change at all. (at least, if you don't hard-code your connection strings)