none
Insert records in parent-child tables within transaction: error 3155

    Question

  • Guys, I'm completely out of ideas!

    2 tables: tAssign, tAssignDetails.

    tAssign.ID - PK, tAssignDetails.ID - PK, tAssignDetails.AssignID - FK. Other fields do not matter. It's a little program for planning trainings, assigning them to both trainers and trainees, tracking the process etc.

    One very simple piece of code which should add a record about the training to tAssign table and then all the records about attendees to tAssignDetails table. The code is smth like that:

    BeginTrans Set rs = CurrentDb.OpenRecordset("tAssign", dbOpenDynaset, dbSeeChanges) With rs .AddNew !TrainingID = Me.lst_training.Value ... 'field values setting .Update .Bookmark = .LastModified ID = .Fields("ID").Value .Close

    End With

    Set rs = CurrentDb.OpenRecordset("tAssignDetails", dbOpenDynaset, dbSeeChanges) With rs For i = 0 to UBound(u) 'u is just a string with attendees separated with comma .AddNew !AssignID = ID 'bla-bla .Update ' the error appears here Next i

    .Close

    End With

    CommitTrans 'error handling with Rollback of course

    Immediately after trying to update the first child record the system hangs and run-time error 3155 appears "ODBC--insert on a linked table 'tAssignDetails' failed".

    I still can insert manually in table or a single record via VBA, but not within transaction. Ideas?


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru



    Thursday, January 31, 2013 12:09 PM

Answers

  • And now the BINGO itself!!! You will never guess. 2 characters which killed my 3 working days!!!

    I don't know the reason but the difference was in connection string.

    DRIVER={SQL SERVER} - does not work

    DRIVER=SQL SERVER -works!

    I've been using the first connection string for years for another app and have never faced such issue. Who knows...


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru


    Monday, February 04, 2013 1:30 PM

All replies

  • Hi Andrey,

    Could it be that before the committrans the parent record is not really inserted in the parent table and so you can't add a child.

    HTH Paolo

    Thursday, January 31, 2013 1:08 PM
  • That was my exact same thought. Try removing the BeginTrans and Commit and see what happens
    Thursday, January 31, 2013 3:26 PM
  • Hi Paolo and ATGNWT,

    it shouldn't be so. That's why:

    1. It worked as expected a couple of days ago. I've done some minor changes since that time and they have not related to this part of procedure. So I'm really wondering.

    2. It is a piece of code. There is another one part of it where I modify the existing record in the parent table and add/delete child records. Again, all fails after trying to add a record. Note, this time the parent record exists.

    Additionally, I don't believe that it's impossible to do such a simple action within transaction. 

    If we look at this article http://msdn.microsoft.com/en-us/library/office/bb178042(v=office.12).aspx, we can see 3 possible reasons. They are not true for my case. But there is a good comment about possible lock by another user or process. I had a thought about making a real mistake while testing code and stopping the code with neither commited nor rolled back transaction. But I've deleted and recreated tAssignDetails table. Nothing changes.

    If no one comes with an idea in the next 12 hours (I have access to this application only at my workplace), I think the next step is trying to move all this logic to the server side, make a stored proced with server side transaction. But who knows, maybe somebody has already faced this situation.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Thursday, January 31, 2013 4:37 PM
  • I have had problems in the past with timing issues with things like this because the records are not inserted immediately. Maybe if you insert a slight delay between these two statements?

    End With

    Set rs = CurrentDb.OpenRecordset("tAssignDetails", dbOpenDynaset, dbSeeChanges)

    Thursday, January 31, 2013 6:35 PM
  • BTW, delay related issues often do not appear when yousing step-by-step with F8. And I still continue to see this annoying error. 

    At least, now I'm sure it is possible within transaction http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d06766c0-6893-4f7c-845f-4f4d5b40f8c8 see Naomi's post.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Thursday, January 31, 2013 7:25 PM
  • Finally, I'm lost. It works extremely well at the server side. It would be rather difficult for me to transfer all the logic from VBA to T-SQL, but if no one has any ideas, I will do it. New knowledge is always useful.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Friday, February 01, 2013 7:12 AM
  • Hi Andrey

    I'm not sure if this works as you do it. You should instantiate an explicit workspace object, in there instantiate an explicit database object and run your transaction in there:

    Dim ws as Workspace Dim db as Database Set ws = DBEngine.Workspaces(0) Set db = ws.Databases(0) ws.BeginTrans Set rs = db.OpenRecordset(....

    'now run your transactions here using the db variable, never use CurrentDB in here! ws.CommitTrans set db = nothing set wb = nothing

    Finally you should be aware to always use dbFailOnError Option if you use db.Execute and in addition you should add an error handler that does the rollback if something went wrong.

    HTH

    Henry

    Friday, February 01, 2013 7:46 AM
  • Thanks Henry!

    I've been always aware of this full (explicit) type if reference to objects, but haven't faced any issues with my style as of yet. Unfortunately, I did all the changes you recommended, but the result is still the same.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Friday, February 01, 2013 9:10 AM
  • How does your code look now?

    Henry

    Friday, February 01, 2013 9:15 AM
  • Also check what errors are in the Errors() Collection, for example:

    ? Errors(0).Description

    Could be you miss something else.

    HTH

    Henry

    Friday, February 01, 2013 9:18 AM
  • Ok, full code of this routine

    Private Sub btn_Save_Click() 
    Dim i%, rs As DAO.Recordset, ID As Long, u As Variant, caller$, _ 
    array1$, array2$, strSQL$, rs_Edit As DAO.Recordset, db As DAO.Database, ws As DAO.Workspace 
    On Error GoTo err_lbl 
        caller = getByName("Caller", Nz(Me.OpenArgs)) 
        If caller = "NewDate" And _ 
        (Me.txt_Date.Tag = DateAdd("n", Me.txt_Minutes.Value, DateAdd("h", Me.txt_Hours, Me.txt_Date.Value)) _ 
        And Me.txt_End.Tag = Me.txt_End.Value) Then 
            MsgBox "Change date!!", vbCritical, "MyApp" 
            Exit Sub 
        End If 
        btn_CheckResources_Click 
        If IsConflicted Then 
            MsgBox "Resource conflict detected!", vbCritical, "MyApp" 
            Exit Sub 
        End If 
        Me.lst_ChosenUsers.Requery 
        If IsNull(Me.lst_ChosenUsers.Column(0, 0)) Then 
            MsgBox "choose attendees!", vbExclamation, "MyApp" 
            Exit Sub 
        End If 
        If Me.cmb_Room.Value & vbNullString = vbNullString Then 
            MsgBox "Choose room!", vbExclamation, "MyApp" 
            Exit Sub 
        End If 
        If Me.cmb_Trainer.Value & vbNullString = vbNullString Then 
            MsgBox "Choose trainer!", vbExclamation, "MyApp" 
            Exit Sub 
        End If 
        Set ws = DBEngine.Workspaces(0) 
        Set db = ws.Databases(0) 
        ws.BeginTrans 
        Set rs = db.OpenRecordset("tAssign", dbOpenDynaset, dbSeeChanges) 
        With rs 
            Select Case caller 
            Case "NewDate", "New": 
                .AddNew 
            Case "Edit": 
                .FindFirst "[ID] = " & getByName("AssignId", Nz(Me.OpenArgs)) 
                .Edit 
            End Select 
            !TrainingID = Me.lst_training.Value 
            !BeginDate = DateAdd("n", Me.txt_Minutes.Value, DateAdd("h", Me.txt_Hours, Me.txt_Date.Value)) 
            !EndDate = Me.txt_End.Value 
            !TrainerID = Me.cmb_Trainer.Value 
            !RoomID = Me.cmb_Room.Value 
            !FullStatusID = 1 
            .Update 
            Select Case caller 
            Case "New", "NewDate": 
                .Bookmark = .LastModified 
                ID = .Fields("ID").Value 
            Case "Edit": 
                ID = getByName("AssignId", Nz(Me.OpenArgs)) 
            End Select 
            .Close 
        End With 
        u = Split(Me.txt_UserList.Value, ",") 
        strSQL = "SELECT Login FROM tAssignDetails WHERE AssignID = " & ID 
        Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot) 
        array1 = GetString(rs, , ",", ",") 
        array2 = Replace(Me.txt_UserList.Value, """", vbNullString) 
        rs.Close 
        Set rs = db.OpenRecordset("tAssignDetails", dbOpenDynaset, dbSeeChanges) 
        With rs 
            Select Case caller 
            Case "New", "NewDate": 
                For i = 0 To UBound(u) 
                    .AddNew 
                    !AssignId = ID 
                    !Login = Replace(u(i), """", vbNullString) 
                    !StatusID = 1 
                    .Update '<--- the process is being locked here 
                Next i 
            Case "Edit": 
                strSQL = "exec bicolumn_symmetric_difference '" & array1 & "','" & array2 & "'" 
                Set rs_Edit = GetPTQ(strSQL, True).OpenRecordset(dbOpenSnapshot) 
                While Not rs_Edit.EOF 
                    If Not IsNull(rs_Edit.Fields("Delete").Value) Then 
                        .FindFirst "[Login] = """ & rs_Edit.Fields("Delete").Value & """" 
                        .Delete 
                    End If 
                    If Not IsNull(rs_Edit.Fields("Add").Value) Then 
                        .AddNew 
                        !AssignId = ID 
                        !Login = rs_Edit.Fields("Add").Value 
                        !StatusID = 1 
                        .Update '<---- or here in Edit scenario 
                    End If 
                    rs_Edit.MoveNext 
                Wend 
                rs_Edit.Close 
            End Select 
            .Close 
        End With 
        If caller = "NewDate" Then 
            strSQL = "UPDATE tAssign SET [FullStatusID] = 4 WHERE ID = " & getByName("AssignId", Nz(Me.OpenArgs)) 
            db.Execute strSQL, dbFailOnError + dbSeeChanges 
            strSQL = "UPDATE tAssignDetails SET [StatusID] = 4 WHERE AssignID = " & getByName("AssignId", Nz(Me.OpenArgs)) 
            db.Execute strSQL, dbFailOnError + dbSeeChanges '<---BTW, this part works well if I comment the previous .Update statement 
        End If 
        ws.CommitTrans 
        Select Case caller 
        Case "New": 
            MsgBox "bla-bla1", vbInformation, "MyApp" 
        Case "Edit": 
            MsgBox "bla-bla2!", vbInformation, "MyApp" 
        Case "NewDate" 
            MsgBox "bla-bla3!", vbInformation, "MyApp" 
        End Select 
        DoCmd.Close acForm, Me.Name, acSaveNo 
    exit_lbl: 
    On Error Resume Next 
        rs.Close 
        Exit Sub 
    err_lbl: 
        ws.Rollback 
        MsgBox UDM, vbCritical, "MyApp" 
        Resume exit_lbl 
    End Sub 


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Friday, February 01, 2013 10:50 AM
  • As for Errors(0) item, I saw it earlier but didn't mention. Nothing interesting - Query timeout expired. Obviously, the table is locked! But why and by which process?

    P.S. It's extremely difficult to write all this stuff from my phone, sorry in advance for kinda errors, typos and other strange things on my text. :)


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Friday, February 01, 2013 10:56 AM
  •         !TrainingID = Me.lst_training.Value 
            !BeginDate = DateAdd("n", Me.txt_Minutes.Value, DateAdd("h", Me.txt_Hours, Me.txt_Date.Value)) 
            !EndDate = Me.txt_End.Value 
            !TrainerID = Me.cmb_Trainer.Value 
            !RoomID = Me.cmb_Room.Value 
            !FullStatusID = 1 
            .Update 
            Select Case caller 
            Case "New", "NewDate": 
                .Bookmark = .LastModified 
                ID = .Fields("ID").Value 
            Case "Edit": 
                ID = getByName("AssignId", Nz(Me.OpenArgs)) 
            End Select 
            .Close 
    

    Hi Andrey,

    It does not solve your problem, but in my opinion you could simplify your program a little bit, by re-placing the assignment of ID:

    !TrainingID = Me.lst_training.Value !BeginDate = DateAdd("n", Me.txt_Minutes.Value, DateAdd("h", Me.txt_Hours, Me.txt_Date.Value)) !EndDate = Me.txt_End.Value !TrainerID = Me.cmb_Trainer.Value !RoomID = Me.cmb_Room.Value !FullStatusID = 1 ID = !ID.Value .Update .Close

    Imb.

    Friday, February 01, 2013 11:20 AM
  • I see.I had similar problems some time ago.IIRC it was  lock as you guess. Not sure anymore what I did.

    I'll try to find it on Monday if you dudn't find a solution yet

    Friday, February 01, 2013 12:50 PM
  • Thanks Henry! I will be interesting in the solution anyway, all these "WHILE @@FETCH_STATUS = 0"s drive me crazy. But I've almost finished with making a stored procedure with the same logic. Frankly, VBA seems to be a bit easier for me.

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Friday, February 01, 2013 1:10 PM
  • Hi Andrey

    I tried everything but I couldn't get into the same lock situation as you did. I posted my code below, maybe you find a difference in there to your code.

    The only reason I can imagine is that you get or hold somehow a lock to your details table from somewhere else, for example from a form. The best way to investigate this issue is to run the SQL Server Management Studio, in there go to the Management and then use the Activity Monitor to examine the Process Info and the Locks. Then go through your code, step by step and check where the lock is established.

    Just one simple question: Do your tables have a TimeStamp (RowNumber) column? I've seen many situation where this helped, but not sure if it helps here, too, but sure is a must for all linked tables to Access when you are doing updates to these tables.

    If you can't solve it this way then probably the best (and most stable) way to do it is how you did it, using a stored procedure and T-SQL.

    Sorry for not being able to help directly but without your data constellation (including tables, indexes) I'm not able to reproduce it.

    Henry

    Here my code:

    Public Function testTransactions(Optional EditID As Variant)
    On Error GoTo PROC_ERR
      Dim ws As Workspace
      Dim db As Database
      Dim rs As Recordset
      Dim ID As Long
      Set ws = DBEngine.Workspaces(0)
      Set db = ws.Databases(0)
      ws.BeginTrans
      ' add new master record
      If IsMissing(EditID) Then
        Set rs = db.OpenRecordset("tAssign", dbOpenDynaset, dbSeeChanges)
        rs.AddNew
      Else
        Set rs = db.OpenRecordset("SELECT * FROM tAssign WHERE ID = " & EditID, dbOpenDynaset, dbSeeChanges)
        rs.Edit
      End If
      rs!MasterText = "New Master Text of " & Now()
      rs.Update
      If IsMissing(EditID) Then
        rs.Bookmark = rs.LastModified
        ID = rs!ID
      Else
        ID = EditID
      End If
      rs.Close
      
      ' add detail record to the new master record
      If IsMissing(EditID) Then
        Set rs = db.OpenRecordset("tAssignDetails", dbOpenDynaset, dbSeeChanges)
        rs.AddNew
        rs!assignid = ID
      Else
        Set rs = db.OpenRecordset("SELECT * FROM tAssignDetails WHERE assignID = " & ID, dbOpenDynaset, dbSeeChanges)
        rs.Edit
      End If
      rs!DetailText = "New Detail Text for ID " & ID & " of " & Now()
      rs.Update
      rs.Close
      Set rs = Nothing
      If MsgBox("Commit changes to database?", vbQuestion + vbYesNo) = vbYes Then
        ws.CommitTrans
      Else
        ws.Rollback
      End If
      
    PROC_EXIT:
      On Error Resume Next
      Set db = Nothing
      Set ws = Nothing
      Exit Function
      
    PROC_ERR:
      MsgBox Err.Description & vbCrLf & Errors(0).Description
      On Error Resume Next
      ws.Rollback
      GoTo PROC_EXIT
      
    End Function
    tested it in the immediate window using testTransactions without parameter to add records and with the ID of the master record to edit records.

    Monday, February 04, 2013 4:00 AM
  • Just some another ideas:

    I see you always get a recordset based on the complete table without a WHERE condition. You then jump to the record you want to edit with "FindFirst". This doesn't make sense. You should (as I did in my code) use a WHERE condition instead. It could be,  that with "FindFirst", you establish a lock on the table that later is blocking the update.

    Your passthrough query is based on the stored procedure "bicolumn_symetric_difference", correct? I maybe wrong but normally stored procedures are not updateable within Access, so I don't know if this can work at all. Is there any chance that you - instead of using a passthrough query rs_edit use a sql statement that is based on the linked tables instead?

    Henry

    Monday, February 04, 2013 4:38 AM
  • Hi Henry,

    thanks a lot for trying! Here are the results:

    1. The fact you can't reproduce the issue proves that it is possible that it worked for me. And the issue really appeared suddenly.

    2. I don't see any difference in our codes as well.

    3. Timestamp. No, I don't. But I added it to both tables, but still no difference.

    4. Where clause in recordsets. Yes, my fault, forgot to change it back while testing. Now I made changes, but still no result.

    5. Pass-through query. Maybe the name rs_Edit has mislead you, but this recordset is not being update. It's a kind of helper recordset, it has info about which attendees I should remove from the list and which - add. Symmetric difference is a set of elements which are in either of two sets and not in their intersection (the union without the intersection). It's really easy to make this with SQL (full join) rather than bother with arrays in VBA.

    Finally, unfortunately, I have no time to test further and I've already completed the stored proc which I will post later. Was a good lesson of T-SQL for me. :) Thanks anyway for your support! 


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Monday, February 04, 2013 5:20 AM
  • And here is the first part of BINGO!

    The issue is file dependent. I created a simple sub which updates 4 records one-by-one in one database in one table. It works immediately in another .mdb, but fails again in my original .accdb.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Monday, February 04, 2013 1:12 PM
  • The list of settings which do not affect the issue:

    1. Decompilation.

    2. Default record locking.

    3. Record-level locking.

    4. Access 2010 Cache features.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Monday, February 04, 2013 1:23 PM
  • And now the BINGO itself!!! You will never guess. 2 characters which killed my 3 working days!!!

    I don't know the reason but the difference was in connection string.

    DRIVER={SQL SERVER} - does not work

    DRIVER=SQL SERVER -works!

    I've been using the first connection string for years for another app and have never faced such issue. Who knows...


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru


    Monday, February 04, 2013 1:30 PM
  • For my great surprise, rewriting this logic to a stored proc took a couple of hours only. For those who is interested in T-SQL realisation, here it is:

    /****** Object:  StoredProcedure [dbo].[AssignTraining]    Script Date: 04.02.2013 18:40:43 ******/ 
    SET ANSI_NULLS ON 
    GO 
    SET QUOTED_IDENTIFIER ON 
    GO 
    -- ============================================= 
    -- Author:      Andrey Artemyev 
    -- Create date: 2013-02-04 
    -- Description:         
    -- ============================================= 
    ALTER PROCEDURE [dbo].[AssignTraining] 
            -- Add the parameters for the stored procedure here 
            @caller nvarchar(20), 
            @TrainingID int, 
            @BeginDate datetime, 
            @EndDate datetime, 
            @TrainerID nvarchar(8), 
            @RoomID int, 
            @emplist nvarchar(max), 
            @AssignID int 
    AS 
    BEGIN 
            -- SET NOCOUNT ON added to prevent extra result sets from 
            -- interfering with SELECT statements. 
            SET NOCOUNT ON; 
            DECLARE @T table (id int) 
            DECLARE @diff table ([add] nvarchar(8), [delete] nvarchar(8)) 
            DECLARE @myID int 
            DECLARE @empTable table (login nvarchar(8)) 
            DECLARE @add nvarchar(8) 
            DECLARE @delete nvarchar(8) 
            DECLARE @currEmp nvarchar(8) 
            DECLARE @emplist_old nvarchar(max) = '' 
            BEGIN TRY 
                    INSERT INTO @empTable SELECT * FROM dbo.CSV2TempTable(@emplist) 
                    BEGIN TRANSACTION T1 
                    IF @caller = 'New' OR @caller = 'NewDate' 
                    BEGIN 
                            INSERT INTO dbo.tAssign ([TrainingID],[BeginDate],[EndDate],[TrainerID],[RoomID],[FullStatusID]) 
                            OUTPUT inserted.ID INTO @T 
                            VALUES (@TrainingID,@BeginDate,@EndDate,@TrainerID,@RoomID,1) 
                            SELECT @myID = id FROM @T 
                            DECLARE details_cursor CURSOR FOR 
                            SELECT [Login] FROM @empTable 
                            OPEN details_cursor 
                            FETCH NEXT FROM details_cursor INTO @currEmp 
                            WHILE @@FETCH_STATUS = 0 
                            BEGIN 
                                    INSERT INTO dbo.tAssignDetails ([AssignID],[Login],[StatusID]) 
                                    VALUES (@myID,@currEmp,1) 
                                    FETCH NEXT FROM details_cursor INTO @currEmp 
                            END 
                            CLOSE details_cursor 
                            DEALLOCATE details_cursor 
                    END 
                    IF @caller = 'Edit' 
                    BEGIN 
                            SET @myID = @AssignID 
                            UPDATE dbo.tAssign SET [TrainingID]=@TrainingID,[BeginDate]=@BeginDate,[EndDate]=@EndDate, 
                            [TrainerID]=@TrainerID,[RoomID]=@RoomID 
                            WHERE dbo.tAssign.id = @myID 
                            DECLARE array_cursor CURSOR FOR 
                            SELECT Login FROM tAssignDetails WHERE AssignID = @myID 
                            OPEN array_cursor 
                            FETCH NEXT FROM array_cursor INTO @currEmp 
                            WHILE @@FETCH_STATUS = 0 
                            BEGIN 
                                    SET @emplist_old = @emplist_old + @currEmp + ',' 
                                    FETCH NEXT FROM array_cursor INTO @currEmp 
                            END 
                            CLOSE array_cursor 
                            DEALLOCATE array_cursor 
                            SET @emplist_old = LEFT(@emplist_old,LEN(@emplist_old)-1) 
                            INSERT INTO @diff exec dbo.bicolumn_symmetric_difference @emplist_old, @emplist 
                            DECLARE details_cursor CURSOR FOR 
                            SELECT * FROM @diff 
                            OPEN details_cursor 
                            FETCH NEXT FROM details_cursor into @delete, @add 
                            WHILE @@FETCH_STATUS = 0 
                            BEGIN 
                                    IF @delete IS NOT NULL 
                                    BEGIN 
                                            DELETE FROM dbo.tAssignDetails 
                                            WHERE [Login] = @delete 
                                            AND [AssignID] = @myID 
                                    END 
                                    IF @add IS NOT NULL 
                                    BEGIN 
                                            INSERT INTO dbo.tAssignDetails (AssignID, [Login], StatusID) 
                                            VALUES(@myID, @add, 1) 
                                    END 
                                    FETCH NEXT FROM details_cursor into @delete, @add 
                            END 
                            CLOSE details_cursor 
                            DEALLOCATE details_cursor 
                    END 
                    IF @caller = 'NewDate' 
                       BEGIN 
                            UPDATE dbo.tAssign SET [FullStatusID] = 4, [NewDateRef] = @myID WHERE ID = @AssignID 
                            UPDATE dbo.tAssignDetails SET [StatusID] = 4 WHERE AssignID = @AssignID 
                    END 
                    COMMIT TRANSACTION T1 
                    SELECT 'OK' AS [Result] 
            END TRY 
            BEGIN CATCH 
                    SELECT 
                            'ERROR' AS [Result], 
                            ERROR_NUMBER() AS ErrorNumber, 
                            ERROR_SEVERITY() AS ErrorSeverity, 
                            ERROR_STATE() AS ErrorState, 
                            ERROR_PROCEDURE() AS ErrorProcedure, 
                            ERROR_LINE() AS ErrorLine, 
                            ERROR_MESSAGE() AS ErrorMessage; 
                    ROLLBACK TRANSACTION T1 
            END CATCH 
    END 


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Monday, February 04, 2013 4:20 PM
  • You are dns-less ODBC connections, correct?

    Anyway that's strange. This was the only difference in the ODBC connect string? When I look at the documentation {...} should be ok or at least was ok in previous versions. Maybe something changed here.

    This newer KB article of MS doesn't include the {}: http://support.microsoft.com/kb/892490

    But this KB article does include it: http://support.microsoft.com/kb/147875

    Henry

    Tuesday, February 05, 2013 2:02 AM
  • Yes, I see. Are they really different drivers?

    And yes, this is the only difference. That's how I found it:

    I have a DSN just to quickly link new tables if any. I linked these 2 my tables to another Access 2003. And all was OK. Then I run the Sturtup form which relinks all tables with DSN-less connection and get the same error. DSN has no {}, but my connection string for DSN-less connection has. I change my INI file where this connection string resides by removing {} around SQL Server and it begins to work as expected. I add them again - the same error again. This behaviour is valid for both A2003 and A2010. What's interesting, I used transaction before in this A2003 file with {} in its connection string and had no issues. But all these transactions had no parent-child tables included in UPDATE/INSERT process, as far as I remember. 


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Tuesday, February 05, 2013 4:06 PM
  • Hi Andrey

    I really don't know the difference using {} or not. I didn't find it in any documentation. Some use it others don't. Same drivers? I don't know. I - same as you did - just become aware of this difference.

    Henry

    Wednesday, February 06, 2013 2:02 AM