Problem with connection to Jet Database

gkmchardy · December 2nd, 2009, 12:38 PM

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();

dparsons · December 8th, 2009, 11:35 AM

Wow, someone that codes ASP in JScript! Just so you know, you probably should of dropped this in an ASP forum since it is a bit misleading in a JavaScript forum. Anyway.

With regard to User A and User B changing data but only User A's changes being refelected in the datastore, is the data in question the same record(s) in the database? To be clear, for example, is User A and User B both working on John Smith's contact record? Or are they working on two entirely different records within the database?

-Doug

gkmchardy · December 8th, 2009, 09:50 PM

Quote:

Originally Posted by dparsons

Wow, someone that codes ASP in JScript! Just so you know, you probably should of dropped this in an ASP forum since it is a bit misleading in a JavaScript forum. Anyway.

With regard to User A and User B changing data but only User A's changes being refelected in the datastore, is the data in question the same record(s) in the database? To be clear, for example, is User A and User B both working on John Smith's contact record? Or are they working on two entirely different records within the database?

-Doug

No, it is not ASP. It is pure JavaScript as far as I know. It is certainly calling ADO functions, but not using ASP scripts.

The users are conflicting when adding new records.

dparsons · December 8th, 2009, 10:38 PM

Wow clientside ADO. Definately not a route I would choose especially if there was a webserver available, but I digress. I have absoluely 0 experience with clientside ADO but what I find curious is this:

objConn.open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=db.mdb; Persist Security Info=False");

Does the datasource point to a DB local to the machine or a network resource?

..EDIT..
Forgive I see that you say it is on a shared resource. I would be lead to believe that this is a locking issue where User A has not released the database thus causing User B's data to not hit the database. This seems unlikely though as you would most certainly get an error telling you as much. Again, I am assuming about the error since I have never used client side ADO but it stands to reason that you would.

Enable script debugging in IE while this is going on maybe it will reveal something.

hth
-Doug

gkmchardy · December 9th, 2009, 10:46 AM

Yes, not the course I would take either, but my resources are very limited. I'm doing the best I possibly can.

Yes, your assumption is correct, the database is on a shared network drive. I didn't include the path and filename as it is on a company server, and thought it best not to give that info out.

IE throws no errors while running the update.

I'm certain it is a locking issue on the DB, but since it is JET, there is no interface to open up to set any options. Can't find anything in Access. I was hoping someone would look at my connection parameters and tell me that I was using the wrong cursor or something like that.

dparsons · December 9th, 2009, 10:53 AM

Ahh. I assumed that you had your Cursor, LockType, etc set for specific reasons. I would actually change all of the properites:

javascript Code:

objRS.CursorLocation = 2; //Use the cursor supplied by the provider
objRS.CursorType = 2; //Dyanmic
objRS.LockType = 3; //LockOptimistic

hth.
-Doug