4

I'm developing an app which will be used by a small team of workers (up to 15) and it stores data in Access accdb files. I don't have any problem when I'm testing out the app just by myself on one machine but when there's at least two people connecting to the db from time to time the app gives strange errors to the users. The database is stored in a shared folder on the network and each user's app accesses that folder to open the db file. Each of the users has their own system account on the db's machine and each function in the code locks access to the db from other threads so there's always only one connection opening attempt at a time. Each connection is closed in the same function. All db functions are written in one singleton class.

The errors I get:

System.Data.OleDb.OleDbException (0x80004005): Could not use 'D:\Shared\nscm\nscm_db.accdb'; file already in use.

System.Data.OleDb.OleDbException (0x80004005): Cannot open database ''.  It may not be a database that your application recognizes, or the file may be corrupt.

System.Data.OleDb.OleDbException (0x80004005): Too many active users. **(this error even when there's only two users!)**

This is driving me crazy. Is it a problem with the JET4/ACE engines that they don't support multiuser access properly?

I'm thinking about changing the databse to something different like MS SQL Server Express or MySQL but I don't know which one would be easiest to migrate to and which ones are really free.

Thanks in advance for your help and attention!

UPDATE: Data convertion is not a problem for me. The app is still in development so there is no real data. I am more worried about the changes in the code and SQL queries. And I'm not sure if I won't encounter other problems after switching to the other db technology.

My real question is: should I keep striving for correcting something in the present technology, I'm using or is there a known problem with the Access db engines and it's worthless to waste my time on this it would be simplier and faster to start using some sort of a db server (MySQL)?

Improve this question
1
  • have you opened the ms access GUI and kept alter table page open? Commented Apr 8, 2012 at 18:07

2 Answers 2

Reset to default
3

You are walking on thin ice here. MSAccess should support your user base but this is a scenario where you could consider to migrate to a different type of database. I mean, not a file based database but a client-server one. After all, if you have, now, 15 users, it is possible (hopefully) that this number grows and the problems with a file based database will grown exponentially.

If you opt for the migration I can say that both SQLServer Express and MySql are free.
In your case I reccommend SQL Server Express. Its T-SQL syntax is more compatible with JET-SQL but some difference are present especially if you use VBA keyword in your query syntax.
From the code point of view I reccommend to abandon the OleDbProvider and start using the native provider for SqlServer. Thus you need to change every OleDbConnection, OleDbCommand etc in the equivalent SqlConnection, SqlCommand (that's true also if you opt for MySql). These changes are rather mechanical and repetitive, so no big problems here. However, you should retest every data access query you have in your code to assure the same result are returned

About the errors:

  • Too many active users - this could be originated by connection not closed correctly
  • Cannot open database - Database corrupt or you have reached the 2GB limit?
  • Could not use - Check if the LDB file is deleted after all your user exit from the app. Again this could be a symptom that something is wrong in your app
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

1. I'm just using connection.Close() in each function. Should I do something more? 2. I haven't reached 2GB. The app is not used yet, just tested so the db contains only some test data which is about 12MB. 3. I thought the db engine managed the LDB file by itself. And yes, it gets deleted after some time when all the users close the apps
Usually I don't use close, I prefer the using statement that is guaranteed to close the connection also if you encounter an exception. As I have said, your user base is in the possibilities of an MSAccess database, but double check every data access you have, be sure to try..catch everything or use using on your disposable objects.
I'm changing the code to add the using statement. This might take several days. I'll test it then and see how it works.
I've spent a week changing the code. Now it utilizes the using statement and I still get the errors. I will try one last thing today and will then move to the server-based db technology. I'm already downloading SQL Server Express 2008 and 2012.
0

Mysql is totally free and open source, which i recommend since it is very user friendly and for the converting part use this: http://www.bullzip.com/products/a2m/info.php

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.