Transactions with ASP and MS Access

12 Nov 2007 2:27 PM

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!

12 Nov 2007 7:26 PM

Mike Brind

Show quote Hide quote

"deadfish" <deadf***@discussions.microsoft.com> wrote in message
news:795ABDC6-BD10-4473-A7F7-A4536E62BAAF@microsoft.com...
> 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.

--
Mike Brind

12 Nov 2007 7:56 PM

Bob Barrows [MVP]

Mike Brind wrote:

>
> The Jet Oledb provider doesn't support transactions.
>

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

12 Nov 2007 9:47 PM

Mike Brind

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23uNvvYWJIHA.5764@TK2MSFTNGP06.phx.gbl...
> Mike Brind wrote:
>>
>> The Jet Oledb provider doesn't support transactions.
>>
>
> That's news to me Mike. Got a cite?
>

I stand corrected.

Mike

12 Nov 2007 8:08 PM

Bob Barrows [MVP]

deadfish wrote:

> 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.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.