Re: Pass Through Query Question

From: Lynn Trapp (ltrappNoSpam_at_ltcomputerdesigns.com)
Date: 12/02/04 

Date: Thu, 2 Dec 2004 12:39:15 -0600

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

-- 
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
"Joe Williams" <JOE@ANYWHERE.com> wrote in message
news:eA4FZ1I2EHA.3064@TK2MSFTNGP10.phx.gbl...
> 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
>
>
> "Ted Allen" <TedAllen@discussions.microsoft.com> wrote in message
> news:9ACAC3FE-17AE-445D-96C3-3348DCF98263@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >>
>
>

Relevant Pages

  • Re: Invalid field size"
    ... I'm using just the ADOQuery component and a CONNECTION STRING, ... When I query a record that exists, the requested fields in the SQL are ... Not long ago MySQL/ODBC had this problem with CHAR() fields smaller ...
    (alt.comp.lang.borland-delphi)
  • Re: Could a change of SQL ports break a data reader?
    ... Can you use SQL Profiler ... to catch the statement that gets executed, paste it into query ... > Dim connectionString As String ... > ' now we have a valid connection string ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: ODBC to Oracle (table or view does not exist)
    ... ODBC driver version match version of Oracle db? ... if i create the passthrough query by writing the sql into the sql ... However if i create the query and specify the connection string - it ...
    (comp.databases.ms-access)
  • ODBC to Oracle (table or view does not exist)
    ... if i create the passthrough query by writing the sql into the sql ... builder window and selecting the connection details on opening the ... However if i create the query and specify the connection string - it ...
    (comp.databases.ms-access)
  • Re: Count Distinct Syntax
    ... Open your query in SQL view, ... > get a syntax error -check the subquery syntax and enclose subquery in ...
    (microsoft.public.access.queries)