Concurrent Users Cause Data Loss

[gkmchardy]

I am using Microsoft JET as the back-end of a web-based application. I am using JavaScript as the interface between the HTML pages and the database. The MDB file is placed on a shared drive accessible to all application users.

Normally the application works fine. One can create, read, update, and delete data just fine. But occasionally, if two persons are using the application concurrently, a very funny thing happens:

User A interacts with the database, making several changes. The changes are all apparent in the application.

User B interacts with the database, making several changes. The changes are all apparent in the application.

However, after both users log out, only User A's changes are retained. Although User B's changes were somehow in the database (the application has no method for pulling data from some other cache), they are nowhere to be found.

I have tried everything I can think of and many other things I have learned from online searches. I create connection objects with local cursors and open locks, and then quickly use and dispose of those connection objects. But despite my best efforts, when two or more are gathered at the database, only one lucky soul gets his data persisted in the database.

My average database transaction now looks like this:

// establish database connection
var objConn = new ActiveXObject("ADODB.Connection");
objConn.open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=db.mdb; Persist Security Info=False");
var objRS = new ActiveXObject("ADODB.Recordset");
objRS.CursorLocation = 3;
objRS.CursorType = 3;
objRS.LockType = 1;

// get data from database
var strSQL = "someSQLstring";
objRS.Open(strSQL,objConn);
[do something with data from recordset]

// cleanup
objRS.Close();
objConn.Close();

 

[Poppa Smurf]

Have you split the database into a front end and a back end?

 

[georgedwilkinson]

The first thing that pops to mind is checking to see if they both have the same key. Are you using an auto-number for the PK? If not, why not?

 

[gkmchardy]

Thanks for the quick replies. Poppa Smurf, the database is only a back-end...just Jet...no Access. Is that what you were asking? George, I always use autonumbering for the primary key, even on associative tables where the record just consists of foreign keys from other tables.

 

[Max D]

It looks strange, but so does your approach too. You are one of the million who decided to use JavaScript for database access. It doesn't give you any advantages comparing to usual MS Access frontend.

I'd advise you to change your way of doing things while it is not too late. You have wide list to choose from:

1. MS Access / MS Access runtime + MDB on network share.
2. MS Access / MS Access runtime + MS SQL (maybe free one)
3. ASP + MDB/SQL - its really easy with current .NET languages
4. PHP + MDB/SQL - that's nice for custom web-pages

there are much more ways, just make sure you pick a common one.

 

[gkmchardy]

I'd love to use any other of the solutions you suggested, but the only one that is feasible at the tools available at my place of work is the complete MS Access solution. My company expects everything to be done with the tools available in SharePoint, without giving me the capability of using SharePoint developer or any ASP or .NET tools. The only thing I could sneak in was JavaScript to a cloaked MDB file on a SharePoint server. I could do a complete MS Access solution, but unfortunately I do not know enough about Access front end, and I don't have the luxury of taking the time to learn it. Thanks for the suggestions anyway.

 

[Max D]

Oh, ic your situation is not easy one.

Creating simple MS Access frontend is easy task (comparing to JS scripting) and usually requires only some mouse-work. Give it a try, I think you'll reproduce your functional in a hour or 2.

Btw, just to check you know it - several ppl can open & use usual MDB file on network share simultaniously - without any additional changes and tweaks. Sure, its a subject for some additional risks.