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

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