Pass Through Query Question

[Joe Williams]

I thought I saw this posted somewhere, but I cannot find it. So my spologies
in advance...

I have Access hooked to SQL as the backend. Through the help from a number
of great people in these newsgroups, I have created a pass through query in
SQL that runs and returns records based on parameters.

My problem is that I have created the pass through query on the SQL side,
put the code to update the pass through SQL at runtime on a form, but when I
go to run the pass through it prompts me for DSN information.

How do I tell access the DSN connection settings to execute the pass through
query, so the user is not presented with a prompt.

Thanks

Joe

 

[Guest]

Hi Joe,

I'm not sure I completely understand your setup. You said that you created
the pass through on the SQL Server side, but if that is the case it doesn't
seem like it would be a pass-through as much as a link to a SQL Server query.

If the pass-through is actually located in the Access DB, you can
permanently store the connect info by opening the query properties in sql
view and pasting the ODBC connect string or clicking the three dots to the
right to build the string (make sure to select to save the password).

If it isn't located in the Access DB, you may want to post a little more
detail about what the process is that you are using to connect to it and
revise the sql.

HTH, Ted Allen

 

[Joe Williams]

Thanks Ted,

The pass through is on the access side, and I am passing variables to the
SQL stored procedure from an Access form. You tipped me off to the problem
when you mentioned setting the connection string in the properties of the
pass through query and that seemed to work. I was immediately faced with the
next obstacle:

The stored procedure uses tables from several different databases on the
same server. It runs with no problem using SQL query anlayzer. However, when
I run the pass though in Access I get the following error:

ODBC error: User 'alliancemfg' is not a valid user in database 'Elmoredata'
(#916)

Now, this seems like a relatively straight forward error, but the connection
string I set up does not use userid 'alliancemfg', and the userid I did use
has full access to all databases in question. In fact, I even tried
recreating the connection string using the sa account to see if I could get
it to work. I still received the same message.

Where is it getting this userid from and how can I change it to one that has
access to all databases on the server?

Thanks

Joe

 

[Guest]

Hi Joe,

I'm afraid I don't have any ideas on that one. You may want to try posting
that question in the Access ODBC Client Server group, perhaps someone there
will have experienced a similar problem.

Best of Luck, Ted Allen

 

[Lynn Trapp]

Perhaps if you post the exact connection string someone here can see a
problem with it.