problems with rollback transaction

[Andreas Wöckl]

Hi Group!

I have the following import function of an excel sheet in my project that
drives me cracy
public sub import()
On Error GoTo Err_perform
DoCmd.RunSQL "BEGIN TRAN Import"
' Work with Recordset import data and so on
' If everything is all right:
DoCmd.RunSQL "COMMIT TRAN Import"
Exit sub
Err_perform:
DoCmd.RunSQL "ROLLBACK TRAN Import"
MsgBox "Error at importing", vbCritical
end sub

If i produce an error I always get the error message that the rollback
transcation has no suitable begin transaction - but why?

any ideas?

best regards,

andy

 

[Vadim Rapp]

Hello Andreas:
You wrote in conference microsoft.public.access.adp.sqlserver on Fri, 13
Aug 2004 11:00:21 +0200:

AW> I have the following import function of an excel sheet in my project
AW> that drives me cracy
AW> public sub import()
AW> On Error GoTo Err_perform
AW> DoCmd.RunSQL "BEGIN TRAN Import"
AW> ' Work with Recordset import data and so on
AW> ' If everything is all right:
AW> DoCmd.RunSQL "COMMIT TRAN Import"
AW> Exit sub
AW> Err_perform:
AW> DoCmd.RunSQL "ROLLBACK TRAN Import"
AW> MsgBox "Error at importing", vbCritical
AW> end sub

AW> If i produce an error I always get the error message that the rollback
AW> transcation has no suitable begin transaction - but why?

Because Access works only with client-side cursors, hence, no locks and no
transactions. You can open your own ado connection, create your own
recordset with server-side cursor, and that will do it. But not Access
methods or objects.

Runsql, by the way, is not compatible at all with ADP's. Don't use it.


Vadim

 

[Andreas Wöckl]

Dear Vadim!

You mean Docmd.runsql "BEGIN TRANSACTION" does not really work?

Concerning cursor - do you mean I am not allowed to use:

dim rs
set rs = new adodb.recordSet
rs.open "...",Connection,...

or do i have to open the recordset with specific parameteres?

cu and thanks in advance

andy

 

[Vadim Rapp]

Hello Andreas:
You wrote in conference microsoft.public.access.adp.sqlserver on Fri, 13
Aug 2004 16:53:57 +0200:

AW> You mean Docmd.runsql "BEGIN TRANSACTION" does not really work?

docmd.runsql does not work in predictable way in ADP regardless of the sql
(you can read it in the online help on runsql). Use
currentproject.connection.execute instead. But "begin transaction" won't
work with that either, because, as I said, Access objects don't support
that. It won't return an error, but it won't open transaction either (or,
rather, it won't open a transaction you could rely on).

AW> Concerning cursor - do you mean I am not allowed to use:

AW> dim rs
AW> set rs = new adodb.recordSet
AW> rs.open "...",Connection,...

You are allowed to use it, in fact that's what I meant by "your own
connection and recordset" - given that for the Connection you don't reuse
the connections created by Access (like currentproject.connection), but
instead you open your own connection:

dim cn as new adodb.connection, rs as new adodb.recordset
cn.open ...
rs.cursorlocation=aduseserver
cn.begintrans
rs.open mysql,cn

I would like to add though that heavy usage of pure ado connections instead
of letting Access manipulate the data in its own way, makes the very usage
of Access pointless - if you are not taking advantage of Access data
manipulation capabilities, then VB is better tool.


Vadim

 

[news.microsoft.com]

hi vadim!

Thanks for your answers - I will consider to implement the functionality
with a stored procedure - that would of course solve all problems!

cu

andi

 

[Andreas Wöckl]

hi vadim!

thanks for your answers - i think i will implement everything in a stored
procedure - that will of course solve all problems

cu

and

 

[¤Ñ²P]

¤j®a¦n!

«Ü«_¬Nªº¥´ÂZ§A­Ì,§Ú·Q³q¹L§A­Ì·s»D²Õ,´M§ä96¦~»{ÃѪº¤@¦ìªB¤Í,·í®É¦o¦bªZº~ªÅ¤l
¤¤¾ÇŪ®Ñ,¨º¦~¦o17©P·³.¦W¦r:·¨ÄR&?·¨±Ó Ū°ª3,¦o¬Oªe«n¶}«Ê¤H,¦oª¨ª¨¬O­¸¦æ­û,
TEL:0378-2930775-21239,²{¦b¹q¸Ü¤]¥´¤£³q,§Ú­Ì¬O¦bºû¹Å·È¦B³õ»{ÃѪº,
98¦~6¤ë,§Ú´N¨ìªF²ð¤F,¦Ü¤µ¨S¦³Ápµ¸¨ì,§Æ±æ»{ÃѩΪ̪¾¹D¦oªºªB¤Í½Ð§i¶D¦o,
¤]ªÌ©Î½Ð©M§ÚÁpµ¸.
¦pªG,·¨ÄR&·¨±Ó ¬Ý¨ì¤F¤]½Ð©p©M§ÚÁpµ¸


TEL:0769-2053911 ±M½u
mobile: 13612662102 ½Ðµo«H®§
E-maill: (e-mail address removed)
(e-mail address removed)


TKS!!!

·¨¬îµØ
2004/08/14

 

[Vadim Rapp]

Hello Andreas:
You wrote in conference microsoft.public.access.adp.sqlserver on Sun, 15
Aug 2004 09:41:30 +0200:

AW> thanks for your answers - i think i will implement everything in a
AW> stored procedure - that will of course solve all problems

No need to. It's ok to implement in code in ADP for some critical function.
I only meant not to implement it for _everything_ access does.


Vadim