1

I'm using an MS Access Database 2013 as frontend to connect to a MS SQL Server 2017. 

Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim conn As New ADODB.Connection


With conn
     .CursorLocation = adUseClient
     .ConnectionString = strSQLOLEDB
     .Open
End With

cmd.CommandType = adCmdStoredProc
cmd.CommandText = strStoredProcedure
cmd.ActiveConnection = conn
cmd.CommandTimeout = 120

cmd.Parameters.Append cmd.CreateParameter(strID_Bez, adInteger, adParamInput, , intID)

rst.Open cmd, , adOpenKeyset, adLockOptimistic

This worked fine for me and I also could use an Trigger to set the changing date/time as a manual timestamp:

CREATE TRIGGER [dbo].[tr_Emailadress_After_Update]
ON [dbo].[Emailadress] AFTER Update, Insert

AS

BEGIN

SET NOCOUNT ON;

UPDATE  Emailadress
SET     Email_TS = sysdatetime()
WHERE   Email_ID IN     (   SELECT  Email_ID
                            FROM    inserted
                        )

Now I'm trying to use the OLTP In-Memory-Technic of MS SQL Server (that's also the reason for the manual timestamp as OLTP does not offer a timestamp) and the Trigger does not work any more.

I searched a lot for a solution and found out, that this will work for me:

CREATE TRIGGER [dbo].[tr_Emailadress_After_Update] ON [dbo].[Emailadress] WITH NATIVE_COMPILATION, SCHEMABINDING AFTER Update , INSERT

AS 

BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'german')

DECLARE @intEmail_ID    int

SELECT  @intEmail_ID = I.Email_ID
FROM    Inserted I

UPDATE  dbo.Emailadress
SET     Email_TS = sysdatetime()
WHERE   Email_ID = @intEmail_ID

END
GO

But now I'm having another problem. I can change data in my Access form, but because of the Trigger the change will not be committed. Without Trigger it works fine... but with Trigger not. If I'm sending an Update directly at SSMS to the table trigger & change will work fine. If I delete the trigger Access will work fine.

I tried to change the TRANSACTION ISOLATION LEVEL but did not found a solution for the problem.

Parfait
  • 104,375
  • 17
  • 94
  • 125
Stefan Bauer
  • 41
  • 7
  • This is not a solution, but be careful, you are assuming the insert will only be one record, but the insertion could potentially have many records, so it would be better to rewrite as an `UPDATE` statement joining EmailAdress with Inserted. – Alan Dec 23 '17 at 00:36
  • I'am aware of that but when I try `UPDATE dbo.Emailadressen SET dbo.Emailadressen.Email_TS = sysdatetime() FROM dbo.Emailadressen INNER JOIN Inserted I ON dbo.Emailadressen.Email_ID = I.Email_ID` SSMS tells me, that "FROM"-Clause is not supported on Update-Triggers and OLTP. ??? – Stefan Bauer Dec 23 '17 at 09:27
  • This is described also here: [link](https://technet.microsoft.com/en-us/library/dn246937(v=sql.120).aspx). Search for "UPDATE with FROM clause". I dont know how to solve this? – Stefan Bauer Dec 23 '17 at 09:35
  • What is the reason for use of "In Memory"? – Alex Dec 25 '17 at 04:42
  • It is faster... I tried that out and the queries are working very fast. – Stefan Bauer Dec 28 '17 at 08:43

0 Answers0