SQL Server triggers + MS Access forms

Nic33 0
Viewed 60

My application is a front end MS Access application linked to a SQL Server database.

I have a form in MS Access for the Orders table and a sub form for Orderslines table

In the OrdersLines table, there is a trigger which calculates the total sum (Quantity x unit price) and more.

The "funny" thing, in MS Access, when I create a new order, I cannot modify the Orders table, because the database and access have not the same data.

So when I run me.requery in MS Access after the process of new order creation, the me.requery sends me to a new record.

This is not happening when I modify this command.

I have tried many things but I can't get it working to keep the current record with a new command.

Any idea will be welcome

Nico

triggers ms-access sql-server
1 Answers

The easiest way to solve this problem is to add a single TimeStamp field to each of your SQL Server tables.

Microsoft Access can track the changes to records in SQL by the TimeStamp field, and it will automatically requery the data from SQL Server and eliminate the "The Data has been modified by another User" message.

The field you add can have any name you wish (I use the name tsJET as this field specifically helps the JET/ACE engine track record changes in this case) and the type for the field is TimeStamp. You don't have to include this field in any queries or forms, it simply needs to exist in the table.

Be sure to refresh the table links after adding this field to your SQL Server tables so that Access can "see" the structural changes to the tables.

NOTE: You cannot modify the data in the TimeStamp field. SQL Server handles that automatically.

Jericho Johnson0
Related