Transactions with ASP and MS Access

[deadfish]

Hi,

I have a ASP application with MS Access. I need to use transaction as there
are several insert and delete statement.

oConn.beginTrans

commUpdate.CommandText = "delete from tableA ..."
set rs = commUpdate.Execute()

commUpdate.CommandText = "insert into tableA ....." set rs =
commUpdate.Execute()

if Err.Number <> 0 then
oConn.RollBackTrans
oConn.close
else
oConn.CommitTrans
oConn.close
set oConn =nothing

end if

Is it a correct way to use transaction? Would the server hang if there are
around 10 (or less) concurrent users. Would the whole database being locked
while a user is performing transactioin?


Thanks in advance!

 

[Mike Brind]

Hi,

I have a ASP application with MS Access. I need to use transaction as
there
are several insert and delete statement.

oConn.beginTrans

commUpdate.CommandText = "delete from tableA ..."
set rs = commUpdate.Execute()

commUpdate.CommandText = "insert into tableA ....." set rs =
commUpdate.Execute()

if Err.Number <> 0 then
oConn.RollBackTrans
oConn.close
else
oConn.CommitTrans
oConn.close
set oConn =nothing

end if

Is it a correct way to use transaction? Would the server hang if there are
around 10 (or less) concurrent users. Would the whole database being
locked
while a user is performing transactioin?


Thanks in advance!

The Jet Oledb provider doesn't support transactions.

 

[Bob Barrows [MVP]]

The Jet Oledb provider doesn't support transactions.

That's news to me Mike. Got a cite?

 

[Bob Barrows [MVP]]

Hi,

I have a ASP application with MS Access. I need to use transaction as
there are several insert and delete statement.

oConn.beginTrans

commUpdate.CommandText = "delete from tableA ..."
set rs = commUpdate.Execute()

no,no,no - this query does not return records. There is no need for a
recordset object. Do this instead to make sure ADO doesn't create an
unnecessary recordset object:

commUpdate.Execute ,,129

commUpdate.CommandText = "insert into tableA ....."
set rs =commUpdate.Execute()

See above

if Err.Number <> 0 then
oConn.RollBackTrans
oConn.close
else
oConn.CommitTrans
oConn.close
set oConn =nothing

end if

Is it a correct way to use transaction?

Outside of the mistake of using a recordset object to run queries that
don't retrieve records, this seems reasonable. Typically, you would
check for errors after each statement.
See tip 9 on this page:
http://www.windowsdevcenter.com/pub/a/oreilly/windows/news/ado_0601.html?page=last

PS. Most of the tips on that page are applicable to VB/VBA, not vbscript
being used in ASP.

Would the server hang if
there are around 10 (or less) concurrent users.

I don't know. You'll have to test it in your environment.

Would the whole
database being locked while a user is performing transactioin?

Possibly, but probably not. Again, testing will answer this question.

 

[Mike Brind]

That's news to me Mike. Got a cite?

I stand corrected.

Mike