MS Access VBA SQL insert statement SOMETIMES runs twice, duplicating the record.

I'm using Access 2013 with a SQL Server 2008 backend.  The application is used simultaneously by up to 6 users.  Every few days I find duplicated records with the same timestamp, down to the exact second.  I've developed several rather large applications this way but have never seen this issue before.  It might occur once every 1000 executions or so. I thought at first that perhaps it was being caused by some kind of Wi-Fi network 'hiccup' but I'm really not sure.  Please help!  Here's the code I'm using for the insert:

Dim db As Database
Dim strSQL As String
Set db = CurrentDb()

strSQL = "Insert Into dbo_tblQMSAuditMaster (Plant, Dept, Area, Shift, Auditor, Series, UnitType) values ('" & Me!txtPlant & "', '" & Me!Dept & "', '" & Me!Area.Column(1) & "', '" & Me!txtShift & "', '" & Me!txtAuditor & "', '" & Me!Series & "', '" & Me!UnitType & "')"
CurrentDb.Execute strSQL

Me.Requery

DoCmd.GoToRecord , , acLast

Dim strSQL2 As String

strSQL2 = "Insert Into dbo_tblQMSAudits (Series, UnitType, IPoint, Task, AuditMasterID, InspectID, MatrixID) SELECT '" & Me!txtSeries & "', UnitType, IPoint, Inspect, '" & Me!txtAuditMasterID & "', InspectID, MatrixID FROM dbo_tblQMSAuditMatrix WHERE UnitType = '" & Me!txtUnitType & "' AND IPoint = '" & Me!txtArea & "'"
CurrentDb.Execute strSQL2, dbSeeChanges

Me!fsubAudits.Requery

Open in new window

Jeff HeilmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BitsqueezerCommented:
Hi,

that's no good idea to find the inserted ID and to insert that in another database, especially if you use a requery between that. A lot of things other users does at the same time could result in a wrong ID to be written.

If you want to be sure that nothing comes in between, create a stored procedure on the server where you include both INSERT commands in one transaction and also a TRY-CATCH construct so you can be sure that in case of any error everything will roll back.

There you can use the function Scope_Identity() to get the inserted ID (if it is a PK ID autonumber column) and then you can insert that into the other table.
That can be executed with a Pass-Through-Query in Access so you only need to provide the parameters needed to execute the INSERTs.

Cheers,

Christian
Russell FoxDatabase DeveloperCommented:
Can you add a "TOP 1" to the 2nd query to ensure you're only getting one record?
Dim db As Database
Dim strSQL As String
Set db = CurrentDb()

strSQL = "Insert Into dbo_tblQMSAuditMaster (Plant, Dept, Area, Shift, Auditor, Series, UnitType) values ('" & Me!txtPlant & "', '" & Me!Dept & "', '" & Me!Area.Column(1) & "', '" & Me!txtShift & "', '" & Me!txtAuditor & "', '" & Me!Series & "', '" & Me!UnitType & "')"
CurrentDb.Execute strSQL

Me.Requery

DoCmd.GoToRecord , , acLast

Dim strSQL2 As String

strSQL2 = "Insert Into dbo_tblQMSAudits (Series, UnitType, IPoint, Task, AuditMasterID, InspectID, MatrixID) SELECT TOP 1 '" & Me!txtSeries & "', UnitType, IPoint, Inspect, '" & Me!txtAuditMasterID & "', InspectID, MatrixID FROM dbo_tblQMSAuditMatrix WHERE UnitType = '" & Me!txtUnitType & "' AND IPoint = '" & Me!txtArea & "'"
CurrentDb.Execute strSQL2, dbSeeChanges

Me!fsubAudits.Requery

Open in new window

Gustav BrockCIOCommented:
You should use DAO for this. Here is how to copy a record to a new:

Private Sub btnNavn_Click()

  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
  
  If Me.NewRecord = True Then Exit Sub
    
  Set rstInsert = Me.RecordsetClone
  Set rstSource = rstInsert.Clone
  With rstSource
    If .RecordCount > 0 Then
      ' Go to the current record.
      .Bookmark = Me.Bookmark
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              ElseIf
                ' Some other condition to exclude fields.
                .Name = "FieldNameToExclude"
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
        ' Go to the new record and sync form.
        .MoveLast
        Me.Bookmark = .Bookmark
        .Close
      End With
    End If
    .Close
  End With
  
' Then proceed creating the subrecord.

  Set rstInsert = Me!fsubAudits.Form.RecordsetClone

  With rstInsert
    .AddNew
        .Fields("Series").Value =  Me!txtSeries 
        ' Set other field values.
    .Update

  Set rstInsert = Nothing
  Set rstSource = Nothing

End Sub

Open in new window

/gustav
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Jeff HeilmanAuthor Commented:
Thank you for the suggestions everyone.  Christian, could you possibly elaborate a little bit on your comment?  I understand the logic, but I'm not sure how to make it happen from the form where the query is currently run.  Thank you!
BitsqueezerCommented:
Hi,

a stored procedure which could do the same without the possibility that anyone can disturb the complete action could be something like this:

CREATE PROCEDURE dbo.procInsert   --  <-- choose a more meaningful name that fits in your environment
(
	@strPlant		AS	nvarchar(50), -- <-- you must adjust the datatypes according to your tables
	@strDept		AS	nvarchar(50),
	@strArea		AS	nvarchar(50),
	@strShift		AS	nvarchar(50),
	@strAuditor		AS	nvarchar(50),
	@strSeries		AS	nvarchar(50),
	@strUnitType	AS	nvarchar(50),
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- SET XACT_ABORT ON will cause the transaction to be uncommittable
	-- when the constraint violation occurs. 
	SET XACT_ABORT ON;

	BEGIN TRY
		BEGIN TRANSACTION

			INSERT INTO dbo.tblQMSAuditMaster (Plant, Dept, Area, Shift, Auditor, Series, UnitType)
			VALUES (@strPlant, @strDept, @strArea, @strShift, @strAuditor, @strSeries, @strUnitType);
			

			INSERT INTO dbo.tblQMSAudits (Series, UnitType, IPoint, Task, AuditMasterID, InspectID, MatrixID)
			SELECT @strSeries, UnitType, IPoint, Inspect, SCOPE_IDENTITY(), InspectID, MatrixID 
			FROM dbo_tblQMSAuditMatrix 
			WHERE UnitType = @strUnitType AND IPoint = @strArea;
		
		COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
		-- add code for error handling here, sample see "TRY...CATCH" in SQL Server Help	
		
		-- Test XACT_STATE:
        -- If 1, the transaction is committable.
        -- If -1, the transaction is uncommittable and should 
        --     be rolled back.
        -- XACT_STATE = 0 means that there is no transaction and
        --     a commit or rollback operation would generate an error.

		-- Test whether the transaction is uncommittable.
		IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION;
		RETURN -1;
	END CATCH;
	RETURN 0;
END
GO

Open in new window


Now you can create a Pass-Through-Query in Access which could for example be something like this:
EXECUTE dbo.procInsert	@strPlant = N'{1}', @strDept = N'{2}', @strArea = N'{3}',
						@strShift = N'{4}', @strAuditor = N'{5}', @strSeries = N'{6}', @strUnitType = N'{7}'

Open in new window


If saved as "qryPT" you can execute that and also avoid mega string constructs by filling the parameters and execute it with a code like this:
    Dim strSQL As String
    With CurrentDb.QueryDefs("qryPT")
        strSQL = .SQL
        .SQL = Replace(.SQL, "{1}", Me.txtPlant)
        .SQL = Replace(.SQL, "{2}", Me.txtDept)
        .SQL = Replace(.SQL, "{3}", Me.Me!Area.Column(1))
        .SQL = Replace(.SQL, "{4}", Me.txtShift)
        .SQL = Replace(.SQL, "{5}", Me.txtAuditor)
        .SQL = Replace(.SQL, "{6}", Me.Series)
        .SQL = Replace(.SQL, "{7}", Me.UnitType)
        .Execute
        .SQL = strSQL ' restore for the next execution
    End With

Open in new window


That's of course not tested as I do not have your environment so you maybe need to adjust this a little bit. There are also other possibilities to execute that, i.e. you could execute the stored procedure using ADO where you would not need to create a local PT query and also no "replace" to insert the parameters, there you can use a parameters collection which could be filled with Parameters.Refresh and then each parameter can be filled similar to a paramterarized QueryDef.
The solution shown above is a pure DAO solution.

The advantage of the stored procedure is that both INSERT commands are executed inside of a transaction where nothing can get between the first and the second INSERT. Moreover the SCOPE_IDENTITY() function used in the second INSERT returns the ID which was created by the first INSERT so there is no need to find it out with SELECTs (which a requery is in your initial way) where x users could have inserted something in the meantime.

You can then (after executing the stored procedure) requery the form and see the inserted values.

Cheers,

Christian

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
You can still use the @@Identity even if you are using an Access DAO insert.

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
 ShowIdentity = rs!LastID

Open in new window

This will get the Identity value entered during your current session.  The only time you might have a problem with this is if you have triggers that might cause inserts.  Look up Scope_Identity() and Ident_Current("tablename") for other options.

I tried to use Scope_Identity and couldn't get it to work so since I don't have any triggers, I used @@Identity.
BitsqueezerCommented:
Hi Pat,

@@IDENTITY is a global variable in SQL Server so any session can read it out. But if another INSERT happens it is overwritten so this is not very secure. In opposite to that SCOPE_IDENTITY() gets the ID which was inserted by the same session so to use it you need to run that in the same session which does the INSERT, like in my stored procedure sample above. Opening a new recordset is of course a new session.

Cheers,

Christian
PatHartmanCommented:
Christian,
According to the documentation -
will return the last identity value entered into a table in your current session

I'm not sure why this is a problem if you don't have any triggers.  That seems to be what this is saying -
will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

Do you know the syntax that will make the Scope_Identity work after you've done an .AddNew on a DAO connection?  I could only get the @@Identity to work.  I'm not switching to ADO or to a stored procedure.
Russell FoxDatabase DeveloperCommented:
I still think his SELECT on the INSERT is returning multiple rows, fixable with a TOP 1.
Gustav BrockCIOCommented:
Using DAO as shown removes the issue completely as you deal with the recordset of the form and not with a separate process.

/gustav
BitsqueezerCommented:
Hi Pat,

I think my above description was not exact enough, here is a good article which explains the difference:
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

But in your case there comes another thing in between: Since Jet4.0 "@@IDENTITY" can be read out with DAO independent of the backend. That means, it is not only a variable on SQL Server, it is now also a "variable" in Jet. When you work with DAO you get no access to variables on the server, you are always querying JET/ACE with Access SQL. The database engine then tries to get the right value from the source, dependent of if it is a local or remote table and if it is Access or i.e. SQL Server or another database server.
To use "Scope_Identity()" you would need to use a) a stored procedure on the server or b) ADO or c) a Pass-Through-Query in Access. While ADO can execute more than one command so Scope_Identity would read out the generated value inside of the same session/scope a Pass-Through-Query can only execute one command at the same time as far as I know so you would need a second PT-Query to read out the value with Scope_Identity - but that would not be the same scope so this would not return the expected result.

That's the reason why you get a value with @@IDENTITY (which is DAO @@IDENTITY = Access SQL) and not Scope_Identity() which is T-SQL.

Cheers,

Christian
Jeff HeilmanAuthor Commented:
Hi Russell, it does return several rows but it is intended to.  It is meant to select the rows that match the UnitType and IPoint (Area on Production Line) and insert the rows into dbo_tblQMSAudits.  They then appear in a subform and are answered during the quality audit.  I've attached a picture to help clarify what the form is intended to do.  Thanks!
Criteria.JPG
Inserted.JPG
Jeff HeilmanAuthor Commented:
It took me a while but I got it working.  Thanks for your time, I learned a lot!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.