ADO Connection Execute and SQL Server Transactions

[markgrizzle]

Hi:
My Access 2003 application has a function which updates several sql server 2000 tables within a transaction.

I use an ADO connection for all data acess, and the connection's .Execute method for the sql statements.

Here's a sample

function foo()

LoadConnection
cnn.BeginTrans
blnTrans = True

sql = "Insert..."
cnn.Execute sql, iRecords, adExecuteNoRecords

If iRecords <> 1 Then
cnn.RollbackTrans
GoTo ExitHere
End If

sql = "Delete..."
cnn.Execute sql, iRecords, adExecuteNoRecords

If iRecords <> 1 Then
cnn.RollbackTrans
GoTo ExitHere
End If

cnn.CommitTrans
blnTrans = False

CloseConnection

End Function

I've been getting an error message at the cnn.Commit, telling me there isn't an active transaction.

My question is, do I need to use ado recordsets with transaction processing? Or is it something else causing the problem?

Thanks,
Mark

ps - If anyone knows of any existing threads discussing this problem, please point me to them.

 

[Trevil]

Sorry, but I've got to ask the "Dumb" question:
Is your "delete" query deleting the record(s) you just "inserted"?

"Have a great day today and a better day tomorrow!

 

[cmmrfrds]

You say update several tables. Would not the normal be a count of >1. Show all the code in the function. For example where is exithere located in the code.

 

[markgrizzle]

Hi Trevil:
Sorry about that, the specific function actually does the following:

LoadConnection
cnn.BeginTrans

sql = "INSERT INTO Info (Info) SELECT '" & sInfo & "';"
cnn.execute sql, iRecords, adExecuteNoRecords

'test for success...
'get new InfoID...

sql = "UPDATE ObjectInfo SET ObjectInfo.InfoID = " &
lngInfoID & " WHERE ObjectInfbjectID=" &
Me.lstObjects & ";"
cnn.execute...
'test...

cnn.CommitTrans
CloseConnection

Every example I've looked at shows recordsets looping inside a transaction, but I prefer the sql statements for simplicity, speed, and reduced network traffic.

Mark

 

[markgrizzle]

Here's the full function:

Private Sub cmdDeleteAlias_Click()

On Error GoTo LocalError

Dim lngInfoID As Long
Dim lngAliasID As Long
Dim sql As String
Dim rs As ADODB.Recordset
Dim bln As Boolean
Dim iRecords As Integer
Dim msg As String
Dim sInfo As String
Dim blnTransaction As Boolean

'create new tblInfo record
LoadConnection ("sql")
cnn.BeginTrans
blnTransaction = True

'create new Info record for alias with current
'object's info

lngAliasID = Me.lstAlias
sInfo = GetInfo(GetInfoID(lngAliasID))

sql = "INSERT INTO Info (Info) SELECT '" & sInfo
cnn.Execute sql, , adExecuteNoRecords


'get new Info.InfoID
Set rs = New ADODB.Recordset
Set rs = cnn.Execute("SELECT @@identity", ,
adCmdText)

If Not rs.EOF Then
lngInfoID = rs.Fields(0)
rs.Close
Set rs = Nothing

Else
cnn.RollbackTrans
GoTo ExitHere

End If


'update ObjectInfo record
sql = "UPDATE ObjectInfo SET ObjectInfo.InfoID = "
& lngInfoID & " WHERE ObjectInfbjectID="
& Me.lstObjects & ";"

cnn.Execute sql, iRecords, adExecuteNoRecords

If iRecords <> 1 Then
msg = "Alias not removed"
MsgBox msg, vbInformation, "Delete Alias"
cnn.RollbackTrans
GoTo ExitHere
End If

cnn.CommitTrans

CloseConnection
blnTransaction = False

'clear alias list so deleted alias reappears
'in lstAllAliases when refreshed

bln = ClearList("frmMenu", "lstAlias")
bln = RefreshLists

Me.cmdAdd.SetFocus
Me.cmdDeleteAlias.Enabled = False

ExitHere:
Exit Sub

LocalError:
Select Case Err
Case Else
'If blnTransaction Then cnn.RollbackTrans
HandleError Err, _
Err.Description, _
"frmMenu.CmdDeleteAlias.Click"
End Select

Resume ExitHere

End Sub

 

[Trevil]

At first glance, I don't see an obvious problem. Have you tried removing the "adExecuteNoRecords" and testing? I have a brain cramp, but I thought I read somewhere that you use that option to improve performance, and it does not return 'RecordsAffected". This should not matter in your case because you will either do a commit or a rollback.

Another question would be what you used in your connect string for cursor type / location?

"Have a great day today and a better day tomorrow!

 

[markgrizzle]

Thanks Trevil:
I haven't tried that, the adExecuteNoRecords is there to tell ADO not to return a recordset. Without it, an empty one would be returned, increasing network traffic and decreasing performance.

I use the iRecordsAffected variable to make sure my sql statement affected only one record. This value is returned by the connection, regardless of the adExecuteNoRecords option setting.

The connection string for the application follows (cnn is a public variable):

Function LoadConnection(sConnType As String) As Boolean

On Error GoTo LocalError

Dim cnnString As String
Dim msg As String

Select Case sConnType

Case "sql"
cnnString = "Provider = 'SQLOLEDB';
Data Source = 'DMSQL';
Initial Catalog = 'KnowledgeBase';
Integrated Security='SSPI'"
Case Else
msg = "Unknown connection type requested,
cancelling request"
MsgBox msg, vbInformation, "Load Conncetion"
GoTo ExitHere

End Select

Set cnn = New ADODB.Connection
cnn.CursorLocation = adUseServer
cnn.Open cnnString

LoadConnection = True

ExitHere:
Exit Function

LocalError:
LoadConnection = False
Select Case Err
Case 3705 'connection already open, ignore
LoadConnection = True
Exit Function
Case Else
LoadConnection = False
HandleError Err,
Err.Description,
"modUtilities.LoadConnection"
End Select
Resume ExitHere

End Function

 

[cmmrfrds]

It sounds like ADO is using default behavior of starting and ending its own transaction. You probably need to override with
Cnn.Execute "set implicit_transactions off"

Check this article.

http://support.microsoft.com/kb/q198024/

 

[markgrizzle]

Thanks cmmrfrds, and thanks Trevil. I made the changes the article suggested to BeginTrans, Rollback, and Commit, as well as including the line to set implicit transactions off.

Access still fails on the "Commit Transaction" line. At this point, I think I'll try reverting this function to an ado recordset method and see how that goes.

Thanks again,
Mark

 

[cmmrfrds]

Did you use the explicit sql server begin transaction?

Cn.Execute "BEGIN TRANSACTION"
Cn.Execute "set implicit_transactions off"

and the explicit rollback.

Cn.Execute "ROLLBACK TRANSACTION"

My interpretation is the the ADO functions
Cn.BeginTrans
Cn.RollbackTrans
will not work with the execute method, but that the first scenario would because these are Transact SQL commands.

 

[markgrizzle]

Yup, here's the new version of the procedure. I'm not sure what's going on. If I figure it all out, I'll post the solution here. Thanks again everyone.
Mark

LoadConnection ("sql")

cnn.Execute "BEGIN TRANSACTION"
cnn.Execute "Set Implicit_Transactions Off"

blnTransaction = True

'create new Info record for alias with current
object's info
lngAliasID = Me.lstAlias
sInfo = GetInfo(GetInfoID(lngAliasID))

sql = "INSERT INTO Info (Info) SELECT '" & sInfo
& "' "
cnn.Execute sql, , adExecuteNoRecords


'get new Info.InfoID
Set rs = New ADODB.Recordset
Set rs = cnn.Execute("Select @@identity", ,
adCmdText)

If Not rs.EOF Then
lngInfoID = rs.Fields(0)
rs.Close
Set rs = Nothing
Else
cnn.Execute "ROLLBACK TRANSACTION"
GoTo ExitHere
End If


'update ObjectInfo record
sql = "UPDATE ObjectInfo SET ObjectInfo.InfoID = "
& lngInfoID & " " & _
"WHERE ObjectInfbjectID=" & Me.lstObjects

cnn.Execute sql, iRecords, adExecuteNoRecords

If iRecords <> 1 Then
msg = "Alias not removed"
MsgBox msg, vbInformation, "Delete Alias"
cnn.Execute "ROLLBACK TRANSACTION"
GoTo ExitHere
End If

cnn.Execute "COMMIT TRANSACTION"

CloseConnection
blnTransaction = False