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.