SQL Transactions in Access

[gray]

Hi All

Access 2002/2007
WinXPPro

I've been experimenting with BeginTrans, CommitTrans, RollbackTrans. I have successfully created a short but sweet copy procedure but I wondered if one of you, more experienced, chaps could have a quick proof read?? I'm particularly keen to ensure that the ID of a new record number is correctly returned when in a multi-user environment?

The proc copies a record within my 'Addrs' table, recovers the ID of the new record and then writes a record to my history table, 'Adt_Recs'. It does all this within one transaction so as to maintain the integrity of the history table... i.e. copy fails, no history record gets written and the newly created record gets discarded.

Please note: My 'Addrs' Autonumbered field is called 'Unique_No' rather then the more usual 'ID'. Also I pass variables to ny proc but I've hardcoded them below to aid readability...

Any tips much appreciated!!

Public Function My_Copy_Record() As Long
On Error GoTo Err_My_Copy_Record
 
Dim ADODBconn As ADODB.Connection
Dim rsRead As New ADODB.Recordset
Dim SQLLINE As String
Dim Fld_Str As String
Dim Extg_Record_Unique_No As Long
Dim New_Record_Unique_No As Long
Dim Curr_User_Unique_No As Long
 
'Set Up Connection and begin SQL Transaction
Set ADODBconn = CurrentProject.AccessConnection
ADODBconn.BeginTrans
 
'Build COPY SQL
SQLLINE = " INSERT INTO Addrs (Name, Address_Line1, Address_Line2, Address_Line3, Added_by_Unique_No)" _
            & " SELECT 'TestRecord', Address_Line1, Address_Line2, Address_Line3, 7 FROM Addrs WHERE Unique_No = 15"
 
'create new copy of record
ADODBconn.Execute SQLLINE, dbFailOnError
 
'Build new ID extract SQL
SQLLINE = "SELECT @@identity AS New_Record_Unique_No FROM Addrs WHERE Added_By_Unique_No=7"
 
rsRead.Open SQLLINE, ADODBconn, adOpenStatic, adLockReadOnly
If rsRead.EOF Then
    MsgBox "COPY FAILED!!"
    GoTo Err_My_Copy_Record
Else
    New_Record_Unique_No = rsRead!New_Record_Unique_No
End If
rsRead.Close
 
'Build History Record SQL
SQLLINE = " INSERT INTO Adt_Recs (Description,Added_By_Unique_No)" _
        & " VALUEs('New Record Created =" & New_Record_Unique_No & "',7)"
 
'Create History Record
ADODBconn.Execute SQLLINE, dbFailOnError
 
ADODBconn.CommitTrans
ADODBconn.Close
 
Exit_Here:
Set ADODBconn = Nothing
Exit Function
 
Err_My_Copy_Record:
    If Err.Number = -2147467259 Then
        MsgBox Err.Description
        Resume Exit_Here
    Else
        MsgBox Err.Description
            ADODBconn.RollbackTrans
            ADODBconn.Close
            Set ADODBconn = Nothing
       Exit Function
    End If
End Function

Thanks

 

[Banana]

1) Yes, @@identity should be multi-user safe. The only consideration is that you should be sure to immediately fetch it before you do any other actions because it's only good for the most recent executed update statement. (I'm not sure if it'll be still available after you do a select statement but why wait?)

2) Just so everyone knows - we can use transactions in DAO, and since this is on Access backend, it may be simpler to just use DAO instead of ADO. You may have already used ADO for whatever reasons so don't feel compelled to change.

3) Why are you trapping for the error -2147467259?

 

[gray]

Hi Banana

Thanks very much for the advice...

Just to clarify, am I correct in thinking that, even whilst using the proc's connection set to 'CurrentProject.AccessConnection', it is still given a unique connection to the table? Thus @@identity only ever returns the latest ID on that specific connection?? That is to say, if UserB creates a record at the same time as UserA the ID's will not be interposed?

Wish I'd known BeginTrans was available in DAO... I ripped apart a DAO proc to use ADO cos' I'd read somewhere it wasn't available in DAO... oh well.. older and wiser!

Re- Error No -2147467259? .. I copied the error handling from a thread at http://www.vbaexpress.com/forum/showthread.php?t=23652

Blindly using an error code without fully understanding it? Guilty as charged I'm afraid!

Cheers

 

[Banana]

RE: unique connection -- Yes, that's what I meant by "mult-user safe" in my reply, and same remains true with DAO, though I think if you wanted to have two different @@identity in parallel for same user, you'd need a new workspace but who really need that anyway?

As for error code, I can't remember what it was, but I'd suggest disabling it so when you test and if you do get that error, you know what it is exactly and handle it appropriately. The reason I ask about that is because it's good to explicitly rollback or commit the transaction in the error handler -- you don't want to exit the procedure without having had committed or rollbacked the pending transactions.

Hope that help.

 

[gray]

Hi All

I bet you could see my next question coming a mile off..

If the INSERT statement inserts more then one record how do I return the @@IDENTITY value for ech insertion in Access please?

I've seen threads on SQL Server (using SCOPE_IDENTITY?) and other Db variants but Access does not seem so obvious?

Much appreciated....

 

[Banana]

Hmm, I actually assumed that your INSERT INTO were adding only one row per execution. If your INSERT INTO statement actually add more than one row, then you only get the very last ID.

As for other variants I should note that SCOPE_IDENTITY is specific to SQL Server and if you're using SS, then you do want to use that. But with Access, one reason why you don't see this so often is because Access (well, actually DAO) already had LastModified since probably first version. Something like this for example:

With rs
   .AddNew
   .Fields("blah") = "blah blah"
   .Update
   .Bookmark = .Lastmodified
   Debug.Print .Fields("myPrimaryKey") 'equivalent to @@identity
End With

To be sure, though, I do prefer doing set-based operations over iterative operations. This is probably what I'd do if I had to synchronize a set of related records between two databases for some reasons:

INSERT INTO d (a, b, c)
SELECT s.a, s.b, s.c
FROM source AS s
LEFT JOIN destination AS d
ON s.pk = d.pk
WHERE d.pk IS NULL;

(where pk are primary key for both tables)

INSERT INTO dc (d, e, f)
SELECT sc.d, sc.e, sc.f
FROM sourceChild AS sc
LEFT JOIN destinationChild AS dc
ON sc.fk = dc.fk
WHERE dc.fk IS NULL;

(where fk are foreign keys to the keys we just inserted in the first query)

INSERT INTO dg (g, h, i)
SELECT sg.g, sg.h, sg.i
FROM sourceGrandchildren AS sg
INNER JOIN (
  sourceChildren AS sc
  LEFT JOIN destinationChildren AS dc
    ON sc.fk = dc.fk
) ON sc.pk = sg.fk
WHERE dc.fk IS NULL;

(where sc.pk is primary key and sg.fk is foreign key relating back to the children table)

The three queries above allow me to add records that do not exist in source currently without having to worry about duplicating or hunting down each keys. The technique used is named "frustrated join". You may notice the last query is different because since the grandchildren may not be using the topmost table's primary key so we need to look up the values that's stored as foreign key in the children table to ensure we still receive only those records that we just inserted.

I hope that helps.

 

[gray]

Hi Banana

Yes, I think see what you are doing here by joining on NULL... I shall experiment further ..... thanks for the tip... cheers

 

[gray]

Update - MOVED to "Queries" Forum

http://www.access-programmers.co.uk/forums/showthread.php?p=1044357#post1044357
----------------------------------------

Hi All!

And just to round my SQL Trans thread off... does anyone know how to return the number of rows affected when doing an update?... I want to detect if an update to a boolean field actually represents a change... sooo..

If my SQL tries to:
Set the value to True when the Column was False, rows_affected = 1
Set the value to True but Column was already True, rows_affected = 0

and vice-versa? Tried the code below but i got a -2147217900 error - Missing Operator in Query Expression @@Rowcount ??

Thanks

Dim ADODBconn As ADODB.Connection
Dim rsRead As New ADODB.Recordset
 
'Set Up Connection and begin SQL Transaction
Set ADODBconn = CurrentProject.AccessConnection
ADODBconn.BeginTrans
 
'Build Update SQL
SQLLine = "UPDATE Addrs SET  Record=True, Date_Record_Last_Updated='21/02/2011 14:07:00', Record_Last_Updated_By_Unique_No=7 
WHERE Unique_No = 15"
 
'Execute the SQL
ADODBconn.Execute SQLLine, dbFailOnError
 
'Build SQL to check the update actually took place
SQLLine = "SELECT @@RowCount AS Rows_Changed FROM Addrs WHERE Record_Last_Updated_By_Unique_No=7"
 
'Execute 'Check' SQL
rsRead.Open SQLLine, ADODBconn, adOpenStatic, adLockReadOnly