Results 1 to 15 of 15
  1. #1
    phineas629 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2011
    Posts
    47

    Transaction Handling appending records from Access to SQL Server

    Hi all,

    I have a ms access front-end with sql server in the back-end. I want to run an append query as one transaction and rollback if any part of the record fails to append.



    Should I be using a pass-through query for transaction handling? Is there a more appropriate solution?

    Thanks

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    what do you mean by 'any part of the record fails to append' typically if there's a problem (for instance trying to add a text value to a number field) the record will not get appended at all or are you batch appending (appending more than one record in a single query) and you want to identify the records that were not appended?

  3. #3
    phineas629 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2011
    Posts
    47
    I was not clear. I will be batch appending. I want to rollback all changes in the batch should any one record fail to append. I will also want an error handler to point me to the failure.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Are you appending a primary key value as well, if so you could just compare the two datasets after the fact based on that primary key value.

  5. #5
    phineas629 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2011
    Posts
    47
    These will be all new records.


    Quote Originally Posted by rpeare View Post
    Are you appending a primary key value as well, if so you could just compare the two datasets after the fact based on that primary key value.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I don't understand.

    What I mean by a batch append means you're trying to append multiple records at the same time (for instance importing a text file or copying records from one table to another).

    If this is an import from a text file where a primary key value (or other uniquely identifiable field) is not available you could use VBA to cycle through the individual lines on the text file attempt to add them one at a time and record the ones that failed in a secondary log.

    I am not a SQL wiz there may be a way to figure out 'bad' records from a particular bulk insert that didn't make the grade from the transaction log but I wouldn't know how to get to it.

    If this is not what you're doing, in other words you've got a data entry screen (for example) and you are 'saving' a record on a button click and you want to determine if that record actually got saved that's a different matter.

  7. #7
    phineas629 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2011
    Posts
    47
    Currently, I have records that will be uploaded from Access to SQL Server. I create a batch Id for the records that will be uploaded. I run through the records one by one until they are uploaded. The uploaded data then updates a local Access table for which we can perform a manual review.

    However, I want to be able to rollback all the data if an error occurs anywhere during the upload process.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Since you are using sql server as you backend, I think this info applies.
    Here is more related to batch transactions.
    I don't use sql server so can not comment on specifics.

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If you are creating a batch ID when you perform the upload, the batch ID should be available in your SQL table and you can 'rollback' the records associated with that batch can't you? A simple delete query would work with a criteria in the BATCHID field

  10. #10
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    This should help.

  11. #11
    phineas629 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2011
    Posts
    47
    The following is the basic outline of the code. I hope it will help someone answer my question.

    Code:
    Private Sub cmdClientUpload_Click()
    
    
    On Error GoTo Err_DoArchive
      Dim ws As DAO.Workspace   'Current workspace (for transaction).
      Dim db As DAO.Database    'Inside the transaction.
      Dim bInTrans As Boolean   'Flag that transaction is active.
      Dim strSql As String      'Action query statements.
    
    
    
    
      ' Initialize database object inside a transaction.
      Set ws = DBEngine(0)
      ws.BeginTrans
      bInTrans = True
      Set db = ws(0)
    
    
    
    
    
    
        ''''' If the record is created successfully, the Client will be moved to the archive table.'''''
        If MsgBox("This will create Client records. Those not passing validation will not be uploaded." & vbCrLf & vbCrLf & "Are you sure? ", vbOKCancel + vbQuestion, "Uploading") = vbOK Then
            If IsNull(txtBatchId) Then
                ' Create the Batch
                strSQL = "INSERT INTO blah blah blah"
                CurrentDb.Execute strSQL
                
                ' Write the Batch ID to records for tracking
                ' Only records ready for upload
                Dim intBatchId As Integer
                Dim intCount As Integer
                intBatchId = DMax("BATCH_ID", "tblBatch")
                strSQL = "UPDATE tblblah blah blah & _
                    " WHERE ReadyForUpload = True"
                CurrentDb.Execute strSQL
                
                ' Load the ID to the form
                txtBatchId = intBatchId
            End If
            
           '''''' Cycle through all ready records to process them one-by-one'''''
            Dim rs As Recordset     
          
            strSQL = "SELECT * FROM tbltbl blah blah blah"
            Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
            SysCmd acSysCmdInitMeter, "Processing...", rs.RecordCount
            intCount = 0
            dteEnteredDate = Now()
            
            Do While Not rs.EOF
                
                If IsNull(rs("ClientId")) Then
                    ' Determine some field values
                    dteReceivedDate = Now() 
    
    
                    ' Create the Client record 
                    strSQL = "INSERT INTO tbl blah blah blah"
                    CurrentDb.Execute strSQL
                    
                    ' Write the Client ID back to local table
                        ' Match on Description
                    intInsertedClientId = DMax("blah", "blah")
                    strSQL = "UPDATE tbl blah blah blah"
                    CurrentDb.Execute strSQL
                    
                    ' Update the Client Number
                    strSQL = "UPDATE tbl blah blah blah"
                    CurrentDb.Execute strSQL, dbSeeChanges
                    
                    ' Write to Log
                    strSQL = "tbl blah blah blah"
                    CurrentDb.Execute strSQL
                    
                End If
                
                intCount = intCount + 1
                SysCmd acSysCmdUpdateMeter, intCount
                rs.MoveNext
            Loop
            
            ' Generate all other records in SET operations
            ' Create Client Fund record
            strSQL = "blah"
            CurrentDb.Execute strSQL       
          
            ' Create Client Notes 
            strSQL = "blah"
            CurrentDb.Execute strSQL
            
            ' Create Client Characteristics
            strSQL = "blah"
            CurrentDb.Execute strSQL, dbSeeChanges
            
            ' Write uploaded timestamp and Client Number
            strSQL = "blah"
            CurrentDb.Execute strSQL
            
            SysCmd acSysCmdClearStatus
            
            MsgBox intCount & " Clients uploaded!", vbInformation
            
            Call Form_Load 'refreshes current form
        End If
     
     Exit_DoArchive:
      ' Clean up
      On Error Resume Next
      Set db = Nothing
      If bInTrans Then   'Rollback if the transaction is active.
        ws.Rollback
      End If
      Set ws = Nothing
    Exit Sub
    
    
    Err_DoArchive:
      MsgBox Err.Description, vbExclamation, "Archiving failed: Error " & Err.Number
      Resume Exit_DoArchive
      
     
    End Sub

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I'm afraid this SQL code doesn't help me much because you've taken out the actual SQL statements. Is there any chance you could provide a sample database (fill it with junk data, but leave enough fake data in there to simulate the problem), then just compact/repair, zip it up, and upload it to this site.

    I do not understand why you are doing some of the things you're doing in your code nor do I understand where you are trying to detect a 'bad' record. You have 10 db.execute commands with various INSERT/UPDATE components so it's difficult to figure out where the problem lies.

    rp

  13. #13
    phineas629 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2011
    Posts
    47
    Quote Originally Posted by rpeare View Post
    I'm afraid this SQL code doesn't help me much because you've taken out the actual SQL statements. Is there any chance you could provide a sample database (fill it with junk data, but leave enough fake data in there to simulate the problem), then just compact/repair, zip it up, and upload it to this site.

    I do not understand why you are doing some of the things you're doing in your code nor do I understand where you are trying to detect a 'bad' record. You have 10 db.execute commands with various INSERT/UPDATE components so it's difficult to figure out where the problem lies.

    rp
    Thanks for the reply. All the information has been informative. However, I'm not comfortable placing the material online.

    Thanks

  14. #14
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If you don't want to provide the SQL code, a database that has ONLY fake/test data in it, or a full version of the you're code building, any information on the table structure of the SQL tables, where in the code you are actually experiencing issues, I am not sure there is much anyone can do to help. Perhaps hiring a consultant would be a better option.

  15. #15
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I have not tried rolling back an entire batch process but, it sounds as though you want to employ dbfailonerror.

    The following example uses querydefs. I believe passing a string variable that is the SQL statement ( CurrentDB.Execute MyString) is slower than CurrentDB.Execute MYQueryObject. It looks like they include an enumeration example to provide a rollback plan.
    http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Appending records to a table
    By crowegreg in forum Programming
    Replies: 6
    Last Post: 08-13-2013, 09:54 AM
  2. Message before Appending Records
    By Lorlai in forum Programming
    Replies: 3
    Last Post: 03-19-2013, 04:51 PM
  3. Appending inventory records with current price records
    By sberti in forum Database Design
    Replies: 8
    Last Post: 11-29-2012, 10:24 PM
  4. Replies: 7
    Last Post: 11-14-2011, 05:59 PM
  5. Key violation when appending records
    By slaterino in forum Programming
    Replies: 10
    Last Post: 08-26-2010, 08:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums