ADO Execute or command execute?

[Norman Fritag]

Hi there

The below code executes some queries.
As newbie I was wondering weather you are better of using connection execute
or command execute to execute queries?

I am asking as Gcnndoh.Execute "AppendDataEntr ySummaryLevel2" causes a
Run-time error '-2147217900 (80040e14).
If I execute the query from the query editor it's working ok!! No
messages!!!


"*****code snipple********
Set Gcnndoh = CurrentProject. Connection
'append missing data entered "=Queryname s"
Gcnndoh.Execute "CreateTempdata check"
Gcnndoh.Execute "AppendDataEntr ySummaryLevel1"
Gcnndoh.Execute "AppendDataEntr ySummaryLevel2"
DoEvents

Gcnndoh.Close
set Gcnndoh = nothing

any suggestions are much appreciated.

Norman

[MacDermott]

Re: ADO Execute or command execute?

While I don't immediately recognize this error number, I'm going to guess
that your query has some references which can be resolved in the Query
Editor, but not by ADO. For example, if you reference a control on a form
directly in the query, ADO won't be able to resolve that reference.
It won't make a bit of difference whether you use connection or command
execute.

If you'd like to post the SQL of "AppendDataEntr ySummaryLevel2" , perhaps we
can make further suggestions.

BTW, are you working in an ADP, or an MDB? Is your back end data in Access?

HTH
- Turtle

"Norman Fritag" <muenchr@ozemai l.com.au> wrote in message
news:gqJkc.305$ Iq5.13790@nnrp1 .ozemail.com.au ...[color=blue]
> Hi there
>
> The below code executes some queries.
> As newbie I was wondering weather you are better of using connection[/color]
execute[color=blue]
> or command execute to execute queries?
>
> I am asking as Gcnndoh.Execute "AppendDataEntr ySummaryLevel2" causes a
> Run-time error '-2147217900 (80040e14).
> If I execute the query from the query editor it's working ok!! No
> messages!!!
>
>
> "*****code snipple********
> Set Gcnndoh = CurrentProject. Connection
> 'append missing data entered "=Queryname s"
> Gcnndoh.Execute "CreateTempdata check"
> Gcnndoh.Execute "AppendDataEntr ySummaryLevel1"
> Gcnndoh.Execute "AppendDataEntr ySummaryLevel2"
> DoEvents
>
> Gcnndoh.Close
> set Gcnndoh = nothing
>
> any suggestions are much appreciated.
>
> Norman
>
>
>
>[/color]

[Norman Fritag]

Re: ADO Execute or command execute?

Turtle,
thanks for your reply!
I am working in Mdb, the backend data is in access.
Here is the error message and the sql of "AppendDataEntr ySummaryLevel2:
I am getting the following error message:
Run-time error '-2147217900 (80040e14)':
Invalid SQL statement;
expected 'DELETE', 'INSERT', 'PROCEDURE','SE LECT',
or 'UPDATE'.

Below is the sqlstatement, which causes this error when call by ado:
'************** *************** *****
INSERT INTO TblEntrySummary Level2 ( Dohid, [School Name],
SumOfTotNumberS tudentsIMMUNISE D, [Date Entered], Ok, [Area
Health Service] )
SELECT TblSCHOOLS.DOHI D, TblSCHOOLS.[School Name], Sum
(TblDataEntrySt ep2.TotNumberSt udentsIMMUNISED ) AS
TotNumberStuden tsIMMUNISED, TblDataEntrySte p2.[Date
Entered], 0 AS OK, TblSCHOOLS.[Area Health Service]
FROM TblSCHOOLS INNER JOIN TblDataEntrySte p2 ON
TblSCHOOLS.DOHI D = TblDataEntrySte p2.DOHID
GROUP BY TblSCHOOLS.DOHI D, TblSCHOOLS.[School Name],
TblDataEntrySte p2.[Date Entered], 0, TblSCHOOLS.[Area
Health Service];

I hope that asists.

regards Norman

"MacDermott " <macdermott@nos pam.com> wrote in message
news:Z7Mkc.929$ a47.368@newsrea d3.news.atl.ear thlink.net...[color=blue]
> While I don't immediately recognize this error number, I'm going to guess
> that your query has some references which can be resolved in the Query
> Editor, but not by ADO. For example, if you reference a control on a form
> directly in the query, ADO won't be able to resolve that reference.
> It won't make a bit of difference whether you use connection or command
> execute.
>
> If you'd like to post the SQL of "AppendDataEntr ySummaryLevel2" , perhaps[/color]
we[color=blue]
> can make further suggestions.
>
> BTW, are you working in an ADP, or an MDB? Is your back end data in[/color]
Access?[color=blue]
>
> HTH
> - Turtle
>
> "Norman Fritag" <muenchr@ozemai l.com.au> wrote in message
> news:gqJkc.305$ Iq5.13790@nnrp1 .ozemail.com.au ...[color=green]
> > Hi there
> >
> > The below code executes some queries.
> > As newbie I was wondering weather you are better of using connection[/color]
> execute[color=green]
> > or command execute to execute queries?
> >
> > I am asking as Gcnndoh.Execute "AppendDataEntr ySummaryLevel2" causes a
> > Run-time error '-2147217900 (80040e14).
> > If I execute the query from the query editor it's working ok!! No
> > messages!!!
> >
> >
> > "*****code snipple********
> > Set Gcnndoh = CurrentProject. Connection
> > 'append missing data entered "=Queryname s"
> > Gcnndoh.Execute "CreateTempdata check"
> > Gcnndoh.Execute "AppendDataEntr ySummaryLevel1"
> > Gcnndoh.Execute "AppendDataEntr ySummaryLevel2"
> > DoEvents
> >
> > Gcnndoh.Close
> > set Gcnndoh = nothing
> >
> > any suggestions are much appreciated.
> >
> > Norman
> >
> >
> >
> >[/color]
>
>[/color]