Posted by Bob on 11/01/06 22:42

Aaron-

TX much for your reply!

interesting suggestion - I didn't think of that - I'll give it a try.

as for mdb ball sucking - you're preaching to the choir!!!
I had simply used up everything else I could think of, and decided to
try it diagnostically - with surprising results!

aaron.kempf@gmail.com wrote:
> Bob;
>
> I love ADP.. I use them every single day
>
> all views are 'just select statements'
>
> My gut says that you have simple cartesianing
>
> I would change from LEFT OUTER JOIN to INNER JOIN and make sure that
> the results you get are similar to what you want.
>
> and then I would evaluate whether you are missing another join
> statement.
>
> BTW; MDB sucks balls spit on anyone that uses it for anything.
>
>
>
>
>
> > SELECT codes.proj_id, answers.report_date,
> > answers.release_date, answers.notes,
> > answers.answer_date, answers.answer_person,
> > answers.answer, answers.answer_status,
> > answers.answer_person2, codes.cust_id
> > FROM dbo.codes LEFT OUTER JOIN
> > dbo.demos ON
> > dbo.codes.code = dbo.demos.code LEFT OUTER JOIN
> > dbo.answers ON dbo.codes.code = dbo.answers.code
>
>
>
>
> Bob wrote:
> > I'm running sql server ver 7.0 SP4.
> >
> > I have an access project (.adp) that runs a view which is nothing more
> > than a select statement. Access locks up solid when I try to run this
> > query - with NO error messages what-so-ever.
> >
> > If I sign onto the server, and run the view from enterprise manager - I
> > get:
> > "the data provider or other service returned an E_FAIL status" !?!?!
> > There is NO ERROR number, or further explanation of the message!!
> >
> > Now - this query DID work last week - all of a sudden I get this error
> > - I've:
> > 1) repaired / compacted this database
> > 2) rebooted the server
> > 3) spent several hours searching websites & groups for an explanation
> > a) I don't care how useless some people think enterprise manager is -
> > at least it gave me SOME kind of error; where access just locks up!
> > b) There are no issues with null values, or problem date fields
> > anywhere in these tables.
> > 4) tried MANY variations on the query, and am now more confused than
> > ever.
> > 5) I've RUN THIS QUERY IN AN ACCESS MDB WITH LINKS TO THE SQL SERVER -
> > AND IT WORKS!?!?!?!?
> >
> > The query I'm using follows; if I remove ONE field from the select
> > statement - this query RUNS!!!
> > It doesn't matter what field I remove! It's as if, I can only have so
> > many fields in the select statement!?!?!?
> > This is clearly ridiculous since the query ran fine last week with the
> > same number of fields, not that
> > that should have anything to do with this, as sql shouldn't care how
> > many fields I request.
> > I'm not actually requesting any fields in the last table - but if I
> > remove this table it works!? If I leave this table, and select fields
> > from it, but remove another table - again it works!
> >
> > So my questions:
> > 1) What [T-] is going on here?
> > 2) Why would an mdb be able to run a query that an adp can not!?!?
> > 3) When will microsoft ever release a product that provides useful
> > error messages?
> >
> > I really don't expect an answer to #3, because I know the answer is
> > NEVER - microsoft doesn't want to make solving problems easy.
> >
> > Here's the query I'm using:
> >
> > SELECT codes.proj_id, answers.report_date,
> > answers.release_date, answers.notes,
> > answers.answer_date, answers.answer_person,
> > answers.answer, answers.answer_status,
> > answers.answer_person2, codes.cust_id
> > FROM dbo.codes LEFT OUTER JOIN
> > dbo.demos ON
> > dbo.codes.code = dbo.demos.code LEFT OUTER JOIN
> > dbo.answers ON dbo.codes.code = dbo.answers.code
> >
> >
> > any help appreciated - TIA
> > Bob

[Back to original message]