?
Solved

Access pass-through query to sql

Posted on 2016-08-26
4
Medium Priority
?
57 Views
Last Modified: 2016-08-29
I have a query functional and it uses a userdsn.  My problem is that the query in access continuously asks for a dsn validatioin everytime I do something to the results.  if I try to filter... it prompts.  if I try to sort... it prompts.
very annoying.

how can I set this up so that everything will continuously run in this access environment without always asking me for credentials.
the dsn is using a sql account that has read only access.

everything works great except for this
0
Comment
Question by:jamesmetcalf74
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 11

Assisted Solution

by:Craig Yellick
Craig Yellick earned 500 total points
ID: 41772075
Is the user DSN using a "trusted connection" or does is require a user name and password? If it needs a password, that would have to be part of the connection string.
0
 
LVL 2

Accepted Solution

by:
Antonio Salva Ripoll earned 1000 total points
ID: 41772105
Hi jamesmetcalf74.

You can store the dsn, the username and the password in the query properties.

Open the query in the SQL view and select the Design tab. At the right you'll see the "Properties sheet" (I dont know the name it will have in your Access version, I use the Spanish version of Access 2010).

When you open the properties, you can modify the ODBC Connection String property.

In that connection string you can add the username and password adding the following
Uid=myUsername;Pwd=myPassword;

Open in new window


Also, check if you have the 'Trusted_Connection=Yes;' option.

Best regards.

Antonio.
0
 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
ID: 41772232
Here's a picture of a connection string in a pass through query.
 ConnectionString.JPG
0
 

Author Closing Comment

by:jamesmetcalf74
ID: 41774837
Thanks gents.
its working
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question