Subform linking to SQL Server View (1 Viewer)

CNorway

Registered User.
Local time
Today, 16:35
Joined
Feb 13, 2018
Messages
33
Hi,

I recently discovered that I have been making a design mistake when using SQL Server views. I previously would have a view and allow the user in an Access form to change data in the view.

However, I learned that this is not a good solution. A better solution is to have the user make their updates on a table.

My question is that I have a form in Access that is a "Continuous Form." The user enters the data into this form which is linked to the MS SQL Server table. However, they want to see the calculated values that result of changes made in the table.

Is the best way of doing this to have 2 subforms where one is linked to the SQL Server table and the other is linked to the SQL Server view and then they are synchronized and whenever a change is made to a record in the table, it updates the subform showing the view for that record? And then returns to the exact record where the change was made?

It isn't completely clear what the correct solution here is. Though, it is clear that linking directly to the view for edits and data entry can and does result in issues and confusion for SQL Server. Thanks!

Best Regards,
c
 

Minty

AWF VIP
Local time
Today, 23:35
Joined
Jul 26, 2013
Messages
10,355
If the view with the calculations is based on the same table as your edits are being made, then you probably don't need to do any clever updating, the view will reflect the changes with a simple refresh.

Views by and large should be considered to be live data, and as you discovered can be edited in certain circumstances.

The easiest way to prevent changes to a view is to simply only grant select privilege on it in the server.
 

CNorway

Registered User.
Local time
Today, 16:35
Joined
Feb 13, 2018
Messages
33
Hi, OK, I think we are close. So, for best practice, it is OK to use a view that is based upon a single table.

Though, if the view is using joins and such (like many of my Access forms are based upon), then is the solution to use 2 subforms or maybe have a continuous form at the top which is based upon the table and then at the bottom of the screen have a subform that shows the data from the complex view?

Thanks,
c
 

Minty

AWF VIP
Local time
Today, 23:35
Joined
Jul 26, 2013
Messages
10,355
So, for best practice, it is OK to use a view that is based upon a single table.
Not really, the whole point of a view used in access is to perform some complex querying on the server to remove the load from access, and generally speed things up.

You can probably join the view to the table using a left join in a local query, and still be able to edit the table on the continuous form, depending on the data set.

That would save you having the sub forms, and make the results appear instantly for the calculations.

And despite Access complaining you can embed a sub form on a continuous form, so your second option would also work, and may be functionally/UI better if there are a lot of calculations to display.
 

Users who are viewing this thread

Top Bottom