Re: can't get query to run over ADO, but works fine in Access inte

Thanks for the suggestions, Richard. The application this is a part of uses
hundreds of ADO calls, actually probably thousands, most of them created on
the fly to handle dynamically created and named tables. I've rechecked that
there are no errors introduced as suggested below. I've also tried reworking
the query so that unchanging parts of it are stored as a query with the part
that varies put into a separate query, unfortunately without success. The
query text runs fine under DAO. As I've had trouble with synchronizing ADO
and DAO queries from the same application before, I'm reluctant to go with
DAO for this one query.

"Richard Mueller" wrote:

> Hi,
>
> If an SQL statement runs in Query Analyzer (or some other interface), you
> should be able to execute it using an ADO Command object. However, the
> statement must be assigned to the CommandText property of the Command object
> as a string. It can be difficult to convert a statement to a string,
> complete with line continuations. Is this the problem? If I were to run this
> with ADO, I would use something similar to (in part):
>
> adoCommand.CommandText = "INSERT INTO PersonsToOrganizations " _
> & "( OrgID, ID, [Position] ) " _
> & "SELECT Q1.OrgID, Q1.ID, Q1.Position " _
> & "FROM (SELECT PM1.IDMstPerson AS OrgID, PM.IDMstPerson AS ID,
> P2O1.Position AS Position " _
> & "FROM (([Src00001] AS S " _
> & "INNER JOIN PersonMatch AS PM " _
> & "ON S.ID = PM.IDSrcPerson) "
> .... etc, etc.
>
> As I say, this often introduces errors. You have to get the commas and
> spaces right. I use line continuations so I can read the command without
> scrolling. Note that the string can also be passed as a parameter to the
> Execute method of the ADO Connection object, but the issue is the same - you
> have to enclose it is quotes.
>
> --
> Richard
> Microsoft MVP Scripting and ADSI
> Hilltop Lab - http://www.rlmueller.net
>
> "Joe" <Joe@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:C69FF5DB-7A2C-41E2-8246-AF45D3E6E717@xxxxxxxxxxxxxxxx
> > The following query runs fine if created in the interface, but cannot be
> > executed over an ado connection. Error is -2147467259, Automation error,
> > Unspecified Error. The connection error object has no description. I've
> > also
> > tried to use adox to store it and to store it manually as a test in a
> > query
> > but get the same error when trying to save over adox or run over ado. As
> > the
> > query is generated in code and refers to dynamically created and named
> > tables, I don't know how to convert it to a parameterized query (table
> > names
> > can't be parameters so far as I know). To reiterate: pasting the text into
> > a
> > query in the interface results in a query that can be stored and run from
> > the
> > interface but not run as a stored procedure over ADO connection. What's
> > going
> > on, and how can I work around this?
> >
> > INSERT INTO PersonsToOrganizations ( OrgID, ID, [Position] ) SELECT
> > Q1.OrgID, Q1.ID, Q1.Position FROM (SELECT PM1.IDMstPerson AS OrgID,
> > PM.IDMstPerson AS ID, P2O1.Position AS Position FROM (([Src00001] AS S
> > INNER
> > JOIN PersonMatch AS PM ON S.ID = PM.IDSrcPerson) INNER JOIN
> > PersonsToOrganizations_00001 AS P2O1 ON S.ID = P2O1.ID) LEFT JOIN
> > PersonMatch
> > AS PM1 ON P2O1.OrgID = PM1.IDSrcPerson WHERE (((PM1.IDSrcPerson) Is Not
> > Null)
> > AND ((PM1.IDSource)=1) AND ((PM1.IDMaster)=0) AND ((PM.IDSource)=1) AND
> > ((PM.IDMaster)=0) AND ((PM.Status)>=50) AND ((S.MADEStatus)>=50) AND
> > ((PM1.Status)>=50))) AS Q1 LEFT JOIN PersonsToOrganizations ON
> > (Q1.Position =
> > PersonsToOrganizations.Position) AND (Q1.ID = PersonsToOrganizations.ID)
> > AND
> > (Q1.OrgID = PersonsToOrganizations.OrgID) WHERE
> > (((PersonsToOrganizations.OrgID) Is Null));
>
>
>
.

Relevant Pages