Results 1 to 4 of 4
  1. #1
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119

    ERROR: You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server...

    Hi friend,


    i get an error saying "You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column" when trying to run the following vba code:

    Code:
    Private Sub ArchivTrans()
    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.
    Dim strMsg As String      'MsgBox message.
    Dim CurrentCLient As String 'The current client
    
    
    On Error GoTo Err_DoArchive
      
    'Step 1: Initialize database object inside a transaction.
    Set ws = DBEngine(0)
    ws.BeginTrans
    bInTrans = True
    Set db = ws(0)
    
    CurrentCLient = DLookup("[Client]", "[DBO_CLIENT]", "ClientID_PK= " & Me.ClientID_FK & "")
    
    
    'Step 2: Move the record to the Archive Table.
    StrSQL = "INSERT INTO DBO_SALE_ARCHIVE (SaleID,SaleDate,ClientID,Client,ProdID,ProductName,ProductPrice,QuantityOrdered) " & _
    " SELECT dbo_SALE_DETAIL.SaleID_FK, dbo_SALE.SaleDate, dbo_SALE.ClientID_FK, dbo_CLIENT.Client, dbo_SALE_DETAIL.ProdID_FK, dbo_PRODUCT.ProductName, dbo_PRODUCT.ProductPrice, dbo_SALE_DETAIL.QuantityOrdered " & _
    " FROM dbo_PRODUCT INNER JOIN (dbo_CLIENT INNER JOIN (dbo_SALE INNER JOIN dbo_SALE_DETAIL ON dbo_SALE.SaleID_PK = dbo_SALE_DETAIL.SaleID_FK) ON dbo_CLIENT.ClientID_PK = dbo_SALE.ClientID_FK) ON dbo_PRODUCT.ProdID_PK = dbo_SALE_DETAIL.ProdID_FK " & _
    " WHERE dbo_SALE.SaleID_PK =" & Me.SaleID_PK & " AND ClientID_FK =" & Me.ClientID_FK & ""
    db.Execute StrSQL, dbFailOnError
    
    
    'Step 3: Delete the record
    StrSQL = "DELETE FROM dbo_SALE WHERE SaleID_PK =" & Me.SaleID_PK & " AND ClientID_FK = " & Me.ClientID_FK & ""
    db.Execute StrSQL, dbFailOnError
    
    
    'Step 4: Get user confirmation to commit the change.
      strMsg = "Do you want to end the sale of  " & CurrentCLient & " ?"
      If MsgBox(strMsg, vbYesNo + vbQuestion, "Confirm") = vbYes Then
        ws.CommitTrans
        bInTrans = False
           DoCmd.GoToRecord acForm, Me.Name, acNewRec
           Me.LstItem.Requery
      End If
    
    
    Exit_DoArchive:
      'Step 5: 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
    What am i doing wrond??

    Note: Am using MS SQL SERVER 2014 as backend db and MS ACCESS as frontend.

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    It means what it says, add the dbSeeChanges switch to your action queries.
    There is a thread here with some more verbiage https://access-programmers.co.uk/for...d.php?t=270053

  3. #3
    ezybusy is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2015
    Posts
    119

  4. #4
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You are welcome.

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

Similar Threads

  1. Replies: 1
    Last Post: 08-08-2016, 04:42 PM
  2. Replies: 3
    Last Post: 07-08-2015, 02:33 PM
  3. dbSeeChanges does not fix openrecordset error
    By lringstad in forum Access
    Replies: 1
    Last Post: 12-03-2013, 03:33 PM
  4. Replies: 7
    Last Post: 07-24-2013, 02:01 PM
  5. Use dbseechanges option with openrecordset
    By spleewars in forum Programming
    Replies: 3
    Last Post: 05-22-2012, 04:19 PM

Tags for this Thread

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