Q.) Jet DB error: Multiple Users able to write to MS Access DBsimutaneously?

[Billy]

Jet database errors when multiple users access the MS Access DB
created from XML code.....

I have an XML file that I use as a source to feed and build an MS
Access database on the fly using classic ASP 3.0 VB-script code. This
is all done in one ASP page where the XML data is stored in
variables,
the ADO connection and recordset to the MS Access DB is opened, and
the update of the fields creates new rows in the table. Anywhere from
1 to 500 rows gets created during the process, then I have a script
where the user downloads that data from and Excel spreadsheet that
opens with the poulated DB data, then the database records are
deleted, the recordset and connections are closed, and the script
completes.


I have an ASP form page setup where the user submits their data and
the ASP response page does what I said above. However, many users
have
complained of Jet database errors - which I have pinned down to
likely
that the database is being updated by another user when the script is
running and updating the DB by another user.


Is it possible to have multiple users open the same connection and
recordset, build rows in an MS Access table, then delete them and
close them simutaneously? I'm limited to MS Access and not SQL Server
so I need to formulate a way to make this process work. I was
thinking
of somehow indexing the user's by the use maybe of sessions and
cookies to have them each create their own DB's if required - however
this process seems extreme to me. Do you have a recommendation of how
to best handle this?


Here is a snip of the database code from two differant pages.....


'*** The UPDATE database code ***
dim objConn
dim oRSbr
dim SQLDELstr ' DELETE is used later to cleanup DB
set objConn=Server.CreateObject("ADODB.connection")
objConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & "c:
\Inetpub\wwwroot\NSCEast\Bomgar\Reporting\BomgarReports.mdb" & ";"
set oRSbr=Server.CreateObject("ADODB.Recordset")
oRSbr.Open "BomgarReports", objConn, adOpenForwardOnly,
adLockPessimistic, adCmdTable
' DELETE is used later to cleanup DB
SQLDELstr = "DELETE * FROM BomgarReports"


'*** The DELETE database code ***
dim objConnE
dim oRSe
dim SQLDELstr ' DELETE is used later to cleanup DB
set objConnE=server.createobject("adodb.connection")
objConnE.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & "c:
\Inetpub\wwwroot\NSCEast\Bomgar\Reporting\BomgarReports.mdb" & ";"
set oRSe=objConnE.execute("select * from BomgarReports")
' DELETE is used later to cleanup DB
SQLDELstr = "DELETE * FROM BomgarReports"

 

[Douglas J. Steele]

Realistically, Jet isn't appropriate for use with ASP, for just this reason.
Try using SQL Server Express instead.

 

[Billy]

So I should be installing SQL Server Express on my IIS server and then
making a connection to that from my ASP code when each user
simutaneously updates the records? The SQL will handle the multiple
simutaeous updates?

 

[Douglas J. Steele]

Yes. Unless you've got a very high volume, SQL Server Express should be able
to handle it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


So I should be installing SQL Server Express on my IIS server and then
making a connection to that from my ASP code when each user
simutaneously updates the records? The SQL will handle the multiple
simutaeous updates?