X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Pass-through Queries With Dsn/tnsnames Less Connection String    
post Dec 18 2016, 08:16 PM

Posts: 1
Joined: 18-December 16

In an attempt to use Access to deliver custom queries in a familiar tool to most, I have found using excel tables connected to and Access DB on a network drive that has Access queries that are querying pass-through queries that are connected to our Oracle database. The problem I run in to is that this requires each user to have a System DSN set on their machine to allow the underlying pass-through query to work. If they have it and the TNSNAMES file, then its all good. However, I am in a company where IT has other priorities than loading DSN on people's machines and it is seen as a security risk. I thought this Access solution would be perfect if I could get the connection string to work, then all the users would do is open Excel which is pre-connected to Access queries and the rest is done for them.

I tried toying for hours on putting a manual connection string in the Properties sheet for my pass-through query, but could not get it to work. I could use linked tables I guess and I did see there was code out there to make a dsn less linked table, but it is much easier for me in my environment to just write the SQL and then put it in a pass through query.

I wish I could get around some of the restrictions I have, but that is likely not possible for quite some time. Any ideas here?

Basically, I want to write my SQL to Oracle, put it in an Access DB pass-through query, make an access query that queries that, have the users pull data from that query in excel which prompts the re-running of the pass-through query (all without having to create a DSN on their machines). My users also do not have Access or a TNSNAMES file. Ughh, I get tired just thinking of what it would be like working at a place that had better BI tools :-)
Go to the top of the page
Doug Steele
post Dec 18 2016, 11:05 PM

UtterAccess VIP
Posts: 22,223
Joined: 8-January 07
From: St. Catharines, ON (Canada)

Exactly what did you try? What error message(s) did you get?

See whether my Using DSN-less Connections is of any use to you.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    22nd November 2019 - 12:25 AM