ADO qstn: How to allow multi-user input to Access MDB

[ahmun]

Hi all,

I've got a data-entry web page written with ASP/VBscript. I'm running into the serious problem of being able to have multiple users add data at exactly the same time. Many users in my intranet have complained about an error, which I guess is a multi-user problem.

Here's the specifics:

[ul]
[li]I'm stuck using Access, so there's no point in suggesting MS SQL server or some other nice db system
*sniff*[/li]
[li]using ADODB connection and recordset[/li]
[li]I'm opening my recordset with the parameters as follows:
RS.open sSQL, myConnection, 2, 3 (OpenKeyset, LockOptimistic)[/li]
[li]I run a RS.addnew, set the values of the fields, and then do an RS.update command[/li]
[li]The Addnew/update sequence happens in a for loop, so I'm assuming that multiple users will have this recordset open. (that's why I used lock optimistic)[/li][/ul]

I've tried to similuate this scenario by having one of my co-workers fill out a form, and I do the same, and we do a simultaneous submit of the form and see who error's out.

The error message that MS IIS 5.0 returns is:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E2F)
[Microsoft][ODBC Microsoft Access Driver]Error in row 
/EmployeeBios/Add.asp, line 109

Line 109 mentioned in the error message is the line where I call the RS.update command

Any insights?

Earnie Eng
If you are born once, you will die twice
If you are born twice, you will die once

 

[ahmun]

5 hours later...
I look like this
and feel like this

I think I found a solution...

the problem isn't in the multi-user issues in Access, but had to do with the cursor location in my recordset.

See thread333-553233 about how to retrieve an autonumber value after running .addnew

I would still welcome any insights to what problems I might encounter using Access 2000 dabase for backend with my ASP frontend.

Thanks

Earnie Eng
If you are born once, you will die twice
If you are born twice, you will die once

 

[dilettante]

This issue takes us far outside the scope of the VBScript Forum. First, it's really a Microsoft Jet and ADO question. Secondly, it's an ASP environment issue and relevant whether the script is written in VBScript, JScript, or some other odd-on scripting engine such as Perl. So it might better be asked in the ASP or Jet (Access) forums instead of here. That's where you'll probably find more relevant expertise.

But you asked for some thoughts. Several things occur to me in looking at what you've presented above:

Why ODBC? This is an obsolete and no longer enhanced interface to Jet. Still works, but you are using an old limited API through an extra translation layer (OLEDB to ODBC conversion). I suggest you use the Jet OLEDB Provider instead.

Take a look at your locking strategy, and you may even find that indexes on your table are hurting you rather than helping for structures that are mostly updated. See:

http://msdn.microsoft.com/library/d...MicrosoftAccessMicrosoftJetDatabaseEngine.asp

Lots of broad-brush performance suggestions there.

Are you using explicit transactions? A lot of "hack" ASP programmers haven't even heard of the ADO Connection methods BeginTrans( ) and CommitTrans( ). Very scary. You can limp along without them, but updates can end up clunking into locking problems a lot if you try to do much OLTP against databases without taking this minor extra trouble. Take a look at:

http://msdn.microsoft.com/library/?url=/library/en-us/ado270/htm/mdamth01_4.asp?frame=true

If you are using Jet (Access) be sure you aren't wasting resources and losing performance by erroneously specifying client-side cursors.

"In most cases, using client-side cursors with an Access database only adds overhead to data access, because data is being cached twice on the same machine: once in the Jet database engine and once in the Cursor Service. However, you must use client-side cursors for some advanced functionality. In particular, client-side cursors are necessary if you want to call the Sort method on a recordset, or if you want to create a disconnected recordset that will later reconnect to the database and update multiple records in a batch.

"In general, you should use the default server-side cursors unless you discover that the additional functionality of client-side cursors is necessary for your application.

"Tip When using Jet to provide your data (as opposed to using SQL Server/MSDE or some other OLE DB provider), you'll want to use a server-side cursor whenever possible. You'll get better performance, and you won't have an extra layer of ADO (the client-side cursor is provided by ADO) between you and Jet. On the other hand, there are some things you simply cannot do with a server-side cursor. The Sort method, for example, only works with client-side cursors. Still, whenever possible, use the default, server-side cursor when working with Jet data."

See:

http://msdn.microsoft.com/library/d.../acsybex_chap6.asp?frame=true#acsybex_topic11

An update For loop in an ASP page? Hmm. Sounds very likely that page-locking is going to result in collisions. I'm not sure, but maybe batch updates using UpdateBatch would make a difference:

http://msdn.microsoft.com/library/?url=/library/en-us/ado270/htm/mdamth03_19.asp?frame=true

For updating that doesn't need to be synchronous, consider using MSMQ to have your ASP pages queue the updates to another process. That other queue-listener process can single-thread the updating to your Jet database helping you avoid most of your locking woes. Note that queued updating does not imply delayed updating, though MSMQ can support that as well if desired.

http://www.microsoft.com/windows2000/technologies/communications/msmq/default.asp

Many of these same issues apply even if you use a database technology besides Jet. Pretty scary when an application breaks with two users though. ;-) Glad you figured out the problem.

 

[Bullschmidt]

<<
I'm running into the serious problem of being able to have multiple users add data at exactly the same time. Many users in my intranet have complained about an error, which I guess is a multi-user problem.
>>

How about this so that only one user accesses the database at a time:

Buffer That Output by Charles Carroll

http://www.learnasp.com/advice/whybuffer.asp

Best regards,
J. Paul Schmidt

http://www.Bullschmidt.com

- Freelance ASP Web Developer

http://www.Bullschmidt.com/DevTip.asp

- ASP Developer Tips