• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Editable VIEWS (1 Viewer)

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:07
Joined
Jan 20, 2009
Messages
12,078
Another technique I have used to connect Access to SQL Server is returning a Table Valued Function via a Pass Through Query.

Code:
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

Super Moderator
Staff member
Local time
Tomorrow, 02:07
Joined
Jan 20, 2009
Messages
12,078
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 :eek:
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

AWF VIP
Local time
Today, 16:07
Joined
Jul 26, 2013
Messages
7,349
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

CopyPaster of the First Order
Local time
Today, 11:07
Joined
Apr 27, 2015
Messages
3,012
I have posted a related question here. I would really appreciate some insight/advice!
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 10:07
Joined
Feb 28, 2001
Messages
18,002
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.
 

Users who are viewing this thread

Top Bottom