UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Insert New Record-SQL Server, Any Versions    
 
   
LilAnnCC1
post May 2 2020, 10:12 AM
Post#1



Posts: 873
Joined: 31-May 04
From: Wisconsin, USA


Hello, all. I'm using SQL 2017 Express with Office 365 Access as front end.

I have just released my database to users for testing. Everything was working fine until I allowed other users in.


When adding a new order to the Sales order form or a new order to the Purchase order form, there were no issues in all of my testing. I could add new records to the form and new line items to the subform.

Now, however, when I or another user enters in a new order, once we click save, the form returns to a random older record instead of staying on the new record. Both forms' cycle properties use Current Record. Both forms are bound forms. Both forms have "header" and "detail subforms".

During my testing, I disabled all my audit triggers, and after releasing the database to users, I enabled the triggers again. Once I disabled the audit triggers, the forms behaved correctly.

This is my audit trigger that is on most every table in my SQL database that I found via the internet and customized it for my tables:
CODE
ALTER TRIGGER [dbo].[TR_Sale_AUDIT]
ON [dbo].[tSale]
FOR UPDATE, Insert, Delete
AS
           DECLARE @bit            INT,
                   @field          INT,
                   @maxfield       INT,
                   @char           INT,
                   @fieldname      VARCHAR(128),
                   @TableName      VARCHAR(128),
                   @PKCols         VARCHAR(1000),
                   @SQL            VARCHAR(2000),
                   @UpdateDate     VARCHAR(21),
                   @UserName       VARCHAR(128),
                   @Type           CHAR(1),
                   @PKSelect       VARCHAR(1000),
                   @dbUser           VARCHAR(20)        --remove domain from suser_sname

           --You will need to change @TableName to match the table to be audited.

           SELECT @TableName = 'tSale'
                set @dbUser=(substring(suser_sname(),(8),len(suser_sname())))
           SELECT @UserName = @dbUser,
                  @UpdateDate = CONVERT(NVARCHAR(30), GETDATE(), 126)

           -- Action
           IF EXISTS (
                  SELECT *
                  FROM   INSERTED
              )
               IF EXISTS (
                      SELECT *
                      FROM   DELETED
                  )
                   SELECT @Type = 'U'
               ELSE
                   SELECT @Type = 'I'
           ELSE
               SELECT @Type = 'D'

           -- get list of columns
           SELECT * INTO #ins
           FROM   INSERTED

           SELECT * INTO #del
           FROM   DELETED

           -- Get primary key columns for full outer join
           SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')
                  + ' i.[' + c.COLUMN_NAME + '] = d.[' + c.COLUMN_NAME + ']'
           FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
                  INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
           WHERE  pk.TABLE_NAME = @TableName
                  AND CONSTRAINT_TYPE = 'PRIMARY KEY'
                  AND c.TABLE_NAME = pk.TABLE_NAME
                  AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

           -- Get primary key select for insert
           SELECT @PKSelect = COALESCE(@PKSelect + '+', '')
                  + '''[' + COLUMN_NAME
                  + ']=''+convert(varchar(100),
                coalesce(i.[' + COLUMN_NAME + '],d.[' + COLUMN_NAME + ']))+'''''
           FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
                  INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
           WHERE  pk.TABLE_NAME = @TableName
                  AND CONSTRAINT_TYPE = 'PRIMARY KEY'
                  AND c.TABLE_NAME = pk.TABLE_NAME
                  AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

           IF @PKCols IS NULL
           BEGIN
               RAISERROR('no PK on table %s', 16, -1, @TableName)

               RETURN
           END

           SELECT @field = 0,
                  -- @maxfield = MAX(COLUMN_NAME)
                  @maxfield = -- FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName


                  MAX(
                      COLUMNPROPERTY(
                          OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),
                          COLUMN_NAME,
                          'ColumnID'
                      )
                  )
           FROM   INFORMATION_SCHEMA.COLUMNS
           WHERE  TABLE_NAME = @TableName


           WHILE @field < @maxfield
           BEGIN
               SELECT @field = MIN(
                          COLUMNPROPERTY(
                              OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),
                              COLUMN_NAME,
                              'ColumnID'
                          )
                      )
               FROM   INFORMATION_SCHEMA.COLUMNS
               WHERE  TABLE_NAME = @TableName
                      AND COLUMNPROPERTY(
                              OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),
          COLUMN_NAME,
  'ColumnID'
                          ) > @field

               SELECT @bit = (@field - 1)% 8 + 1

               SELECT @bit = POWER(2, @bit - 1)

               SELECT @char = ((@field - 1) / 8) + 1
              


               IF SUBSTRING(COLUMNS_UPDATED(), @char, 1) & @bit > 0
                  OR @Type IN ('I', 'D')
               BEGIN
                   SELECT @fieldname = COLUMN_NAME
                   FROM   INFORMATION_SCHEMA.COLUMNS
                   WHERE  TABLE_NAME = @TableName
                          AND COLUMNPROPERTY(
                                  OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),
                                  COLUMN_NAME,
                                  'ColumnID'
                              ) = @field



                   SELECT @SQL =
                          '
           insert into tAudit (    Type,
           TableName,
           PK,
           FieldName,
           OldValue,
           NewValue,
           EventDate,
           Employee)
           select ''' + @Type + ''','''
                          + @TableName + ''',' + @PKSelect
                          + ',''' + @fieldname + ''''
                          + ',convert(varchar(1000),d.' + @fieldname + ')'
                          + ',convert(varchar(1000),i.' + @fieldname + ')'
                          + ',''' + @UpdateDate + ''''
                          + ',''' + @UserName + ''''
                          + ' from #ins i full outer join #del d'
                          + @PKCols
                          + ' where i.' + @fieldname + ' <> d.' + @fieldname
                          + ' or (i.' + @fieldname + ' is null and  d.'
                          + @fieldname
                          + ' is not null)'
                          + ' or (i.' + @fieldname + ' is not null and  d.'
                          + @fieldname
                          + ' is null)'



                   EXEC (@SQL)
               END
           END


How can I get the forms to behave when the triggers are on? Also, can anyone help me to change the audit script to not include inserts? I haven't found a need to have a record of when the data was inserted (the table shows me a create date and by who).

Any suggestions are most appreciated!
This post has been edited by LilAnnCC1: May 2 2020, 10:13 AM

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
vtd
post May 2 2020, 10:30 AM
Post#2


Retired Moderator
Posts: 19,777
Joined: 14-July 05
From: Sydney NSW Australia


If you want to exclude inserts from the audit trail, try changing the trigger script declaration

CODE
ALTER TRIGGER [dbo].[TR_Sale_AUDIT]
ON [dbo].[tSale]
FOR UPDATE, Insert, Delete


to

CODE
ALTER TRIGGER [dbo].[TR_Sale_AUDIT]
ON [dbo].[tSale]
FOR UPDATE, DELETE


If you are confident with T-SQL, you should remove the redundant parts of the T-SQL trigger script now that INSERT is not required.

You may have the same problem if you create audit trail records on line item records in the SubForm.
Go to the top of the page
 
LilAnnCC1
post May 2 2020, 11:28 AM
Post#3



Posts: 873
Joined: 31-May 04
From: Wisconsin, USA


That fixed my form problems! Thank you so much!

No, I'm not confident in my T-SQL, so even though I tried to fiddle with it, I wound up leaving as is and just changed the trigger to what you suggested.

Again, Thank You, Thank You (I thought I was going to have to rebuild these forms!)

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
vtd
post May 2 2020, 11:57 AM
Post#4


Retired Moderator
Posts: 19,777
Joined: 14-July 05
From: Sydney NSW Australia


You're welcome...

In fact, I also need to thank you also because your post is the first post that confirms the problem that I identified a while back. I thought I was crazy at the time until I proved to myself that the problem was not in my Access application (Access Front-End / SQL Server back-end).

Could you please confirm for me that you use Identity Field (equivalent to Access AutoNumber) in both Tables [tSale] and [tAudit]?
Go to the top of the page
 
LilAnnCC1
post May 2 2020, 12:00 PM
Post#5



Posts: 873
Joined: 31-May 04
From: Wisconsin, USA


Yes I use ID int as the Primary Key in the tblSales and ID bigint in the tAudit table.

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
vtd
post May 2 2020, 12:22 PM
Post#6


Retired Moderator
Posts: 19,777
Joined: 14-July 05
From: Sydney NSW Australia


Thanks, LilAnnCC1.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th July 2020 - 05:38 AM