Editable VIEWS

[Galaxiom]

Another technique I have used to connect Access to SQL Server is returning a Table Valued Function via a Pass Through Query.

SELECT * FROM myTVF(myParameter)

I needed to pass a parameter to get data from the server then join to other tables. Views don't take parameters and results from Stored Procedures can't be linked. An SP can write to a table but that is pretty clumsy. Access happily joins to the function populated PTQ. The SQL property of the PTQ can be edited to change the parameter.

I also use this technique to populate RowSource tables for comboboxes in Excel.

 

[Galaxiom]

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

At least mine were on the same domain. I remember being pleasantly surprised when it finally worked as I had almost given up hope.

I also had a Linked Server to Active Directory which was fantastic. But that stopped working when we upgraded to Server 2016. Not been able to find any solution. I suspect it is related to the ticket that goes with the request for the Kerberos authentication not being supported when nominating the user whose credentials will be used to connect.

Linked servers is one of the magical features about SQL Server. We even have a Universe database (Pick derivative) running on IBM AIX (*nix based system) that we access via an ODBC linked server. Ever grateful to my brilliant network admin for getting that one to work for me.

SQL Server can even connect to Access and Excel via OLTP. The applications need to be installed on the server so I have not tried it yet. I get data sent to me on Excel and it would be really useful if the server could read it. Just seems a little obscene to be installing Office on the database server.

 

[Minty]

SQL Server can even connect to Access and Excel via OLTP. The applications need to be installed on the server so I have not tried it yet. I get data sent to me on Excel and it would be really useful if the server could read it. Just seems a little obscene to be installing Office on the database server.

I think you only need the Jet or Ace engine, which I believe MS now have made available separately without needing the full office install? (Although a quick Google means I can't find it... so maybe I imagined it?)

 

[NauticalGent]

I have posted a related question here. I would really appreciate some insight/advice!

 

[The_Doc_Man]

A side note: There was a discussion about Kerberos authentication in this thread.

IF you have to use Kerberos, the tickets are time-stamped and have limited lifetimes as well as limited windows of eligibility. The trick to handle this is that the systems on a sub-net using Kerberos authentication should have their clocks controlled using Network Time Protocol (NTP) from a single server, because a security feature of Kerberos is time-sensitivity. If you pass a ticket to a network member and the clocks are off by just a little bit from each other, they tend to barf. However, if all participating servers are NTP-linked and have reached an "equilibrium" state with respect to time adjustments, that small time window won't be a problem.

I offer no comments on the SQL Server side of this as I have no experience. But I DO have some Kerberos scars from my Navy days.