Editable VIEWS

[NauticalGent]

Good morning AWF.

Being new to SQL Server, I was under the impression that SQL Views were only useful in returning a RecordSet that was read-only. This was disappointing because I really wanted to take advantage of Server-Side Filtering.

I came across this article yesterday and couldn't wait to test it out. Bottom line, it works - BIG game changer for me!

I searched the forum to see if this solution had been posted before and could not find one. If I am simply stating something that is common-knowledge, my apologies. If not, I hope this gets someone as excited as it did me!

It's the little things....

 

[isladogs]

Having an editable view is something I only do rarely but I agree that it can be very useful.

As the article says, views will only be editable provided that
1. They are editable in SQL Server.
2. They have a primary key field in the table to be edited.
It is possible to assign the primary key in Access either using the wizard (if linking to the view using ODBC) or by using VBA code.

BTW the article also suggests using a timestamp field. I've not found that to be necessary

 

[NauticalGent]

BTW the article also suggests using a timestamp field. I've not found that to be necessary

I was JUST getting ready to add that bit of info but, as usual, CID is way ahead of me!

 

[isladogs]

LOL
I meant to thank you for posting the article as I'm not sure it is common knowledge.
Indeed a few years ago, Albert Kallal emphatically and repeatedly stated it couldn't be done in code.
He backed off when I provided code like that in the article.
However, Albert was the person who mentioned the ODBC link wizard method.
As I only use DSN less connections, I had been unaware of that approach to adding a unique view index from Access.

One final thing:
I first needed an editable view several years ago to improve the efficiency of a very complex and slow routine with one of my schools databases.
As part of this I had been using make table queries, editing those then updating the original SQL tables. As well as causing database bloat, it was VERY slow.
Eventually, I tried using an editable view. It took even longer to complete the procedure.

You may find passthrough queries a useful alternative

 

[Minty]

It can be very handy, even if simply to hide fields you don't need or want someone to see in a table.

 

[cheekybuddha]

>> You may find passthrough queries a useful alternative <<
A recordset based on a view which is returned by a passthrough will not be editable.

(I know you guys already know that, but since NG's original post is about using editable views ...)

 

[NauticalGent]

>> You may find passthrough queries a useful alternative <<
A recordset based on a view which is returned by a passthrough will not be editable.

(I know you guys already know that, but since NG's original post is about using editable views ...)

Exactly. When I went looking, my intent was to find a way to leverage the power of SQL Server for use in Access with editable Forms. I found one lengthy article about using PTQs to execute a stored procedure the return an editable RecordSet, but there were a LOT of moving parts!

If anyone has any feedback, advice on that issue, I am ALL ears(eyes)...

 

[Minty]

Neither a stored procedure or a PT query will return an editable data set as such, however, you could load the results into a recordset or temp table, edit them, then update the results back to SQL.

This can be handy when you are using a remote server(Azure or off-premises) and speed and reliability of the connection is an issue.

 

[NauticalGent]

Thanks Minty...I hadn't really thought about that. I'm not in love with the idea just on the whole multi-user paradigm but speed is becoming a HUGE issue with my users in the Middle East. Also, I rarely have more than 10 users online at anytime so it write conflicts might not be that big of a deal.

The folks out there do have there own SQL Server and I am contemplating a linked server solution but as DBG and Doc will confirm, the Request For Change (RFC) process is slow and tedious.

Do you have any experience with linked servers?

 

[Minty]

Do you have any experience with linked servers?

Not directly but I do work with remote azure servers a lot.
This explains quite well https://www.mssqltips.com/sqlservertip/6083/understanding-sql-server-linked-servers/
When working with slower connections the stored procedure/PT query/Local Table route is really effective, as it removes the need for the connection to the remote data to be maintained. It does add complexity, and you need to be careful with controlling multiple access to the same records. (Effectively lock or sign records out and then back in)

 

[Galaxiom]

Exactly. When I went looking, my intent was to find a way to leverage the power of SQL Server for use in Access with editable Forms.

I make use disconnected recordsets returned from ADODB Commands sent to SQL Server. The recordset can be set as an Access Form recordset where edits on the form can be stored. A loop through the form recordset is used to update back to the server.

One of my favourite tricks is to use a single record in a bit column to join to other data as the control source for a record selection checkbox on the form. Disconnecting makes an otherwise non-updateable recordset editable and allows it to store a value separately for each record despite being derived from a single record on the server.

 

[NauticalGent]

Thanks Galaxiom, if you have any sample code to share, I would like to see it.

 

[cheekybuddha]

Leigh Purvis has examples of using disconnected recordsets on his website (See the last 2 examples on that page).

The examples are a bit more involved, but the principle is the same when it comes to binding/using the disconnected rs's.

hth,

d

 

[Galaxiom]

Thanks Minty...I hadn't really thought about that. I'm not in love with the idea just on the whole multi-user paradigm but speed is becoming a HUGE issue with my users in the Middle East. Also, I rarely have more than 10 users online at anytime so it write conflicts might not be that big of a deal.

Massive speed improvements can be had with subforms. I use the Current Event of the main form to populate the subform using an ADODB command to return the subform recordset from a parameterized Stored Procedure. This is literally orders of magnitude faster than linked tables used for subforms.

Do you have any experience with linked servers?

I have worked with Linked Servers. Passing the Kerberos authentication between them is the most complex part. It is easy to set them up while leaving the whole server wide open to the network if you don't do it right.

 

[Galaxiom]

Leigh Purvis has examples of using disconnected recordsets on his website (See the last 2 examples on that page).

The examples are a bit more involved, but the principle is the same when it comes to binding/using the disconnected rs's.

If it is what I think it is the Transacted Form is a really good technique to know. All the advantages of a bound form with updates allowing multiple records to be added or edited on a form and updated or abandoned as an entire batch.

I've never actually used the technique but I believe that ADODB disconnected recordsets (as long as they are conventionally updateable) used on forms can be reconnected to the server and updated. This reduces the number of concurrent connections to the server.

 

[NauticalGent]

The examples are a bit more involved

That's what I meant by "moving parts"! I downloaded them and will give it a look over with all this free time I have. Thanks for the link.

Massive speed improvements can be had with subforms. I use the Current Event of the main form to populate the subform using an ADODB command to return the subform recordset from a parameterized Stored Procedure. This is literally orders of magnitude faster than linked tables used for subforms.

I do something similar with tabs now. I will look at that as well...

I have worked with Linked Servers. Passing the Kerberos authentication between them is the most complex part. It is easy to set them up while leaving the whole server wide open to the network if you don't do it right.

I can only imagine. All IT support, including Server Admin is done with contracted technicians, in this case General Dynamics. The group in the ME are under the same contract as the one here in Europe. You would not know this by they way the (do not) work together. Getting is set up will be tricky.

 

[Minty]

Massive speed improvements can be had with subforms. I use the Current Event of the main form to populate the subform using an ADODB command to return the subform recordset from a parameterized Stored Procedure. This is literally orders of magnitude faster than linked tables used for subforums.

This can make a Mahoosive difference.

I have worked with Linked Servers. Passing the Kerberos authentication between them is the most complex part. It is easy to set them up while leaving the whole server wide open to the network if you don't do it right.

And this can be a nightmare, I think I gave up the last time (SQL Server 2008 if I remember) it was beyond me at the time.

 

[isladogs]

>> You may find passthrough queries a useful alternative <<
A recordset based on a view which is returned by a passthrough will not be editable.

(I know you guys already know that, but since NG's original post is about using editable views ...)

Thanks for clarifying that point as I should have done originally.
My purpose in mentioning P-T was purely related to their potential speed benefits. Glad to see the various comments made in later replies

 

[Galaxiom]

And this can be a nightmare, I think I gave up the last time (SQL Server 2008 if I remember) it was beyond me at the time.

IIRC it was reasonably straightforward with earlier server versions but the trouble began when one of the servers was upgraded and it demanded or defaulted to Kerberos authentication. Then I needed to support cross server queries and I had to enable delegation for the SQL Engine service user in Active Directory.

I pretty much forgot about it all when we got all the databases onto the same server.

 

[Minty]

It was the delegation between 2008 and 2008R2 that caused me issues, 2 different domains etc, I just had nowhere near enough knowledge at the time.
And have subsequently tried to block it from my memory