Rollback doesn't work

[Andrew Gabb]

I'm missing something here:

The rollback in the following doesn't work. The UPDATE appears to be
committed automatically.

I'm using Access 2003 DAO linked to MS SQL Server 2005 via ODBC.

Private Sub Test1()
Dim dbx As Database
Dim DBWS As Workspace
Set DBWS = DBEngine.Workspaces(0)
Set dbx = CurrentDb
DBWS.BeginTrans '*** TRANSACTION BEGIN
dbx.Execute "update USER set [Initial]='x' where [Username]= 'AG'",
dbFailOnError
DBWS.Rollback '*** TRANSACTION ROLLBACK
End Sub

Andrew

 

[Stefan Hoffmann]

hi Andrew,

The rollback in the following doesn't work. The UPDATE appears to be
committed automatically.

Using this works for me:

Option Compare Database
Option Explicit

Private m_CurrentDb As DAO.Database
Private m_Workspace As DAO.Workspace

Private Sub cmdBegin_Click()

m_Workspace.BeginTrans

End Sub

Private Sub cmdRollback_Click()

m_Workspace.Rollback

End Sub

Private Sub cmdInsert_Click()

m_CurrentDb.Execute _
"INSERT INTO dbo_Criterion(DisplayText, Serial) " & _
"VALUES('123', 123);", dbFailOnError

End Sub

Private Sub cmdCommit_Click()

m_Workspace.CommitTrans

End Sub

Private Sub Form_Load()

Set m_CurrentDb = CurrentDb
Set m_Workspace = DBEngine.Workspaces(0)

End Sub


Or maybe this helps:

http://msdn.microsoft.com/en-us/library/ms131281.aspx

mfG
--> stefan <--

 

[bcap]

This'll probably make you scream Andrew, but your code works for me (exact
same setup i.e. Access 2003, DAO, ODBC, SQL Server 2005)

 

[Andrew Gabb]

Thanks guys. It does more than make me scream - it gives me the
screaming willies. But, it encouraged me to do some more testing.

I now know that the rollback fails only if I connect to the DB using
VBA. If I connect via the password prompt, the rollback works.

So there's something 'wrong' with my connection (which works for
everthing else). The code is:

Public Sub Connect_SQL(DSNname As String, DBname As String)
Dim Sconnect As String, DB As Database
Sconnect = Dformat("ODBC;DSN=%%;APP=Microsoft Office 2003;" _
+ "WSID=TERMSVR1;DATABASE=%%;" _
+ "UID=xxx;PWD=yyyyyy;", DSNname, DBname)
Set DB = OpenDatabase(DSNname, False, True, Sconnect)
End Sub

Any ideas?

Andrew

This'll probably make you scream Andrew, but your code works for me (exact
same setup i.e. Access 2003, DAO, ODBC, SQL Server 2005)

I'm missing something here:

The rollback in the following doesn't work. The UPDATE appears to be
committed automatically.

I'm using Access 2003 DAO linked to MS SQL Server 2005 via ODBC.

Private Sub Test1()
Dim dbx As Database
Dim DBWS As Workspace
Set DBWS = DBEngine.Workspaces(0)
Set dbx = CurrentDb
DBWS.BeginTrans '*** TRANSACTION BEGIN
dbx.Execute "update USER set [Initial]='x' where [Username]= 'AG'",
dbFailOnError
DBWS.Rollback '*** TRANSACTION ROLLBACK
End Sub

Andrew
--
Andrew Gabb
email: (e-mail address removed) Adelaide, South Australia
phone: +61 8 8342-1021
-----

 

[Stefan Hoffmann]

hi Andrew,

So there's something 'wrong' with my connection (which works for
everthing else). The code is:

Public Sub Connect_SQL(DSNname As String, DBname As String)
Dim Sconnect As String, DB As Database
Sconnect = Dformat("ODBC;DSN=%%;APP=Microsoft Office 2003;" _
+ "WSID=TERMSVR1;DATABASE=%%;" _
+ "UID=xxx;PWD=yyyyyy;", DSNname, DBname)
Set DB = OpenDatabase(DSNname, False, True, Sconnect)
End Sub

Any ideas?

OH:
Set <database> = <workspace>.OpenDatabase (..)

So use an explicit workspace for opening your database and use a global
variable for it and your database, e.g. if the code is in one module
(untested):

Option Compare Database
Option Explicit

Private m_Database As DAO.Database
Private m_Workspace As DAO.Workspace

Public Sub Connect_SQL(DSNname As String, DBname As String)

Dim Sconnect As String

Sconnect = Dformat("ODBC;DSN=%%;APP=Microsoft Office 2003;" _
+ "WSID=TERMSVR1;DATABASE=%%;" _
+ "UID=xxx;PWD=yyyyyy;", DSNname, DBname)

Set m_Workspace = DBEngine.Workspaces(0)
Set Database = m_Workspace.OpenDatabase _
(DSNname, False, True, Sconnect)

End Sub

Private Sub Test1()

On Local Error GoTo LocalError

Dim SQL As String

m_Workspace.BeginTrans
SQL = "UPDATE [USER] SET [Initial] = 'x' WHERE [Username]= 'AG'"
m_Database.Execute SQL, dbFailOnError
m_Workspace.Rollback

Exit Sub

LocalError:
' do error handling...

End Sub


mfG
--> stefan <--

 

[Andrew Gabb]

I tried this with no luck, Stefan.

It appears to me that the problem has something to do with the
method of connection itself. If I connect manually (eg try to open a
table) there's no problem. More interestingly, if I connect manually
then run the code which connects using VBA, the rollback works. It's
as if the VBA connect is ignored.

The connect strings (using eg ?CurrentDb.TableDefs("project").Connect
are the same in either case.

Andrew

hi Andrew,

So there's something 'wrong' with my connection (which works for
everthing else). The code is:

Public Sub Connect_SQL(DSNname As String, DBname As String)
Dim Sconnect As String, DB As Database
Sconnect = Dformat("ODBC;DSN=%%;APP=Microsoft Office 2003;" _
+ "WSID=TERMSVR1;DATABASE=%%;" _
+ "UID=xxx;PWD=yyyyyy;", DSNname, DBname)
Set DB = OpenDatabase(DSNname, False, True, Sconnect)
End Sub

Any ideas?

OH:
Set <database> = <workspace>.OpenDatabase (..)

So use an explicit workspace for opening your database and use a global
variable for it and your database, e.g. if the code is in one module
(untested):

Option Compare Database
Option Explicit

Private m_Database As DAO.Database
Private m_Workspace As DAO.Workspace

Public Sub Connect_SQL(DSNname As String, DBname As String)

Dim Sconnect As String

Sconnect = Dformat("ODBC;DSN=%%;APP=Microsoft Office 2003;" _
+ "WSID=TERMSVR1;DATABASE=%%;" _
+ "UID=xxx;PWD=yyyyyy;", DSNname, DBname)

Set m_Workspace = DBEngine.Workspaces(0)
Set Database = m_Workspace.OpenDatabase _
(DSNname, False, True, Sconnect)

End Sub

Private Sub Test1()

On Local Error GoTo LocalError

Dim SQL As String

m_Workspace.BeginTrans
SQL = "UPDATE [USER] SET [Initial] = 'x' WHERE [Username]= 'AG'"
m_Database.Execute SQL, dbFailOnError
m_Workspace.Rollback

Exit Sub

LocalError:
' do error handling...

End Sub


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Andrew,

It appears to me that the problem has something to do with the method of
connection itself.

This is true, take a closer look at the OpenDatabase procedure. When the
second boolean is True, then you'll open a read only database...

So this works for me:

Dim db As DAO.Database
Dim ws As DAO.Workspace

Dim Connect As String
Dim SQL As String

Connect = CurrentDb.TableDefs.Item("dbo_cp_Account").Connect

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("NHibernate", False, False, Connect)

ws.BeginTrans
SQL = "INSERT INTO [cp_Account](idPlanningYear) VALUES(666);"
db.Execute SQL, dbFailOnError
ws.Rollback



mfG
--> stefan <--

 

[Andrew Gabb]

This is true, take a closer look at the OpenDatabase procedure.

When the second boolean is True, then you'll open a read only
database...

You're right, Stefan, and I'm a red-faced fool. Thank you!!

But, but, but what are the rules? It seems as if they're something
like ....

# If you open with write access, you can rollback OK.

# If you open with read-only access you can still write, but all
changes are committed, ie rollback doesn't work. This is more
dangerous than write access!!

Seems crazy to me. But thanks again.

Andrew

hi Andrew,

It appears to me that the problem has something to do with the
method of connection itself.

This is true, take a closer look at the OpenDatabase procedure.
When the second boolean is True, then you'll open a read only
database...

So this works for me:

Dim db As DAO.Database Dim ws As DAO.Workspace

Dim Connect As String Dim SQL As String

Connect = CurrentDb.TableDefs.Item("dbo_cp_Account").Connect

Set ws = DBEngine.Workspaces(0) Set db =
ws.OpenDatabase("NHibernate", False, False, Connect)

ws.BeginTrans SQL = "INSERT INTO [cp_Account](idPlanningYear)
VALUES(666);" db.Execute SQL, dbFailOnError ws.Rollback



mfG --> stefan <--

 

[Stefan Hoffmann]

hi Andrew,

# If you open with read-only access you can still write, but all
changes are committed, ie rollback doesn't work. This is more dangerous
than write access!!

When I set the parameter in my example to False (for read only), I'll
get an error: "The field xyz is not updateable."


mfG
--> stefan <--