Can I wrap an Access 2007 ADO transaction statement around a SQL Server stored procedure (that may contain a transaction itself) and have the various transactions successfully rollback/commit as would be expected. Or does weirdness ensue? (ie implicit auto-commits, etc)
Having recently gained an understanding of Stored Procedures/Inline Table Valued Functions/etc/etc. I have started making moves to migrate much of my business logic into SQL Server (Express 2008).
With some disgust however I have discovered that passing table-valued parameters from Access 2007 seems to be crippled. (Or rather, just not-implemented) ( http://msdn.microsoft.com/en-us/library/dd721896.aspx
My main use for this functionality was to pass a set of user-selected values (say primary keys) through to the stored procedure as a table (along with a couple of other parameters) and having the procedure perfom any inserts/deletes/updates within a server-side transaction. (Returning error-codes, etc)
After some thrashing around I am considering keeping the same logic and just looping through the user-selected keys passing each individually to the stored procedure for processing and then wrapping an ADO transaction around the whole thing.
Is this going to work?
See made-up code below:
(I bashed this out pretty quick so I may have missed a few things in my setup/teardown. Please feel free to point them out since I am still a bit new to ADO)
Sub SomeProcedure(CollOfIds as collection)
On Error GoTo Error_Handler
Dim cnn As ADODB.Connection
Dim cmd as ADODB.Command
Dim rst As ADODB.Recordset
Dim id as variant
Dim boolInTrans As Boolean
boolInTrans = False
Set rst = New ADODB.Recordset
Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
cnn.ConnectionString = "Some-Connection-String"
.ActiveConnection = con
.CommandText = "Stored-Procedure-Name"
.CommandType = adCmdStoredProcedure
' Possibly other parameters here
boolInTrans = True
For Each id in CollofIDs
.Parameters("@ID") = id
' Probably redundant in this example since Stored Proc code
' should generate an actual error
' This is really to demonstrate that I want to return values (perhaps an identity)
If .Parameters("@ErrCode") <> 0 Then
boolInTrans = False
Set cnn = Nothing
Set rst = Nothing
msgBox "Error # " & Err.number & ": " & Err.Description
If boolInTrans Then