Query action in ms access on ado.command 
Author Message
 Query action in ms access on ado.command

Hello

I have a query action (delete) in Ms Access, with a parameter of type text.
I am trying to execute an object command ado, but this one always returns an
error. See below.

<< -2147217900 "Invalid SQL statement; expected 'DELETE', 'INSERT',
'PROCEDURE', 'SELECT', or 'UPDATE'." >>

The code :

  Dim objcmd As ADODB.Command
  Dim objParm As ADODB.Parameter
  Dim lngNmbreRecordAffect As Long

  On Error GoTo Error_DeleteFixedC

    If mobjCon Is Nothing Then Exit Function
    If ((Trim(strIDFixedCost) = "") Or (Not IsNumeric(strIDFixedCost))) Then
Exit Function

    Set objcmd = New ADODB.Command
    objcmd.ActiveConnection = mobjCon
    Set objParm = New ADODB.Parameter
    objParm.Name = "KeyFXDCosts"
    objParm.Direction = adParamInput
    objParm.Type = adVariant
    objParm.Value = strIDFixedCost
    objcmd.Parameters.Append objParm
    objcmd.CommandText = "Q_DeleteFixedCost"
    objcmd.CommandType = adCmdtext
    mobjCon.CursorLocation = adUseClient
    objcmd.Execute , , adExecuteNoRecords
    Set objcmd = Nothing

    ...

If you have an idea?

thank you in advance

E.G.



Sun, 28 Dec 2003 17:19:03 GMT  
 Query action in ms access on ado.command
E.G.,

This line is wrong:
objcmd.CommandType = adCmdtext

Change it to
objcmd.CommandType = adCmdStoredProc
--
HTH,
Alexander Shirshov, MCSD


Quote:
> Hello

> I have a query action (delete) in Ms Access, with a parameter of type
text.
> I am trying to execute an object command ado, but this one always returns
an
> error. See below.

> << -2147217900 "Invalid SQL statement; expected 'DELETE', 'INSERT',
> 'PROCEDURE', 'SELECT', or 'UPDATE'." >>

> The code :

>   Dim objcmd As ADODB.Command
>   Dim objParm As ADODB.Parameter
>   Dim lngNmbreRecordAffect As Long

>   On Error GoTo Error_DeleteFixedC

>     If mobjCon Is Nothing Then Exit Function
>     If ((Trim(strIDFixedCost) = "") Or (Not IsNumeric(strIDFixedCost)))
Then
> Exit Function

>     Set objcmd = New ADODB.Command
>     objcmd.ActiveConnection = mobjCon
>     Set objParm = New ADODB.Parameter
>     objParm.Name = "KeyFXDCosts"
>     objParm.Direction = adParamInput
>     objParm.Type = adVariant
>     objParm.Value = strIDFixedCost
>     objcmd.Parameters.Append objParm
>     objcmd.CommandText = "Q_DeleteFixedCost"
>     objcmd.CommandType = adCmdtext
>     mobjCon.CursorLocation = adUseClient
>     objcmd.Execute , , adExecuteNoRecords
>     Set objcmd = Nothing

>     ...

> If you have an idea?

> thank you in advance

> E.G.



Sun, 28 Dec 2003 17:57:31 GMT  
 Query action in ms access on ado.command
Thanks,

    I already tried but it doesn't work.


Quote:
> E.G.,

> This line is wrong:
> objcmd.CommandType = adCmdtext

> Change it to
> objcmd.CommandType = adCmdStoredProc
> --
> HTH,
> Alexander Shirshov, MCSD



> > Hello

> > I have a query action (delete) in Ms Access, with a parameter of type
> text.
> > I am trying to execute an object command ado, but this one always
returns
> an
> > error. See below.

> > << -2147217900 "Invalid SQL statement; expected 'DELETE', 'INSERT',
> > 'PROCEDURE', 'SELECT', or 'UPDATE'." >>

> > The code :

> >   Dim objcmd As ADODB.Command
> >   Dim objParm As ADODB.Parameter
> >   Dim lngNmbreRecordAffect As Long

> >   On Error GoTo Error_DeleteFixedC

> >     If mobjCon Is Nothing Then Exit Function
> >     If ((Trim(strIDFixedCost) = "") Or (Not IsNumeric(strIDFixedCost)))
> Then
> > Exit Function

> >     Set objcmd = New ADODB.Command
> >     objcmd.ActiveConnection = mobjCon
> >     Set objParm = New ADODB.Parameter
> >     objParm.Name = "KeyFXDCosts"
> >     objParm.Direction = adParamInput
> >     objParm.Type = adVariant
> >     objParm.Value = strIDFixedCost
> >     objcmd.Parameters.Append objParm
> >     objcmd.CommandText = "Q_DeleteFixedCost"
> >     objcmd.CommandType = adCmdtext
> >     mobjCon.CursorLocation = adUseClient
> >     objcmd.Execute , , adExecuteNoRecords
> >     Set objcmd = Nothing

> >     ...

> > If you have an idea?

> > thank you in advance

> > E.G.



Sun, 28 Dec 2003 18:22:29 GMT  
 Query action in ms access on ado.command
Then post the SQL of your query, please.


Quote:
> Thanks,

>     I already tried but it doesn't work.




> > E.G.,

> > This line is wrong:
> > objcmd.CommandType = adCmdtext

> > Change it to
> > objcmd.CommandType = adCmdStoredProc
> > --
> > HTH,
> > Alexander Shirshov, MCSD



> > > Hello

> > > I have a query action (delete) in Ms Access, with a parameter of type
> > text.
> > > I am trying to execute an object command ado, but this one always
> returns
> > an
> > > error. See below.

> > > << -2147217900 "Invalid SQL statement; expected 'DELETE', 'INSERT',
> > > 'PROCEDURE', 'SELECT', or 'UPDATE'." >>

> > > The code :

> > >   Dim objcmd As ADODB.Command
> > >   Dim objParm As ADODB.Parameter
> > >   Dim lngNmbreRecordAffect As Long

> > >   On Error GoTo Error_DeleteFixedC

> > >     If mobjCon Is Nothing Then Exit Function
> > >     If ((Trim(strIDFixedCost) = "") Or (Not

IsNumeric(strIDFixedCost)))

- Show quoted text -

Quote:
> > Then
> > > Exit Function

> > >     Set objcmd = New ADODB.Command
> > >     objcmd.ActiveConnection = mobjCon
> > >     Set objParm = New ADODB.Parameter
> > >     objParm.Name = "KeyFXDCosts"
> > >     objParm.Direction = adParamInput
> > >     objParm.Type = adVariant
> > >     objParm.Value = strIDFixedCost
> > >     objcmd.Parameters.Append objParm
> > >     objcmd.CommandText = "Q_DeleteFixedCost"
> > >     objcmd.CommandType = adCmdtext
> > >     mobjCon.CursorLocation = adUseClient
> > >     objcmd.Execute , , adExecuteNoRecords
> > >     Set objcmd = Nothing

> > >     ...

> > > If you have an idea?

> > > thank you in advance

> > > E.G.



Sun, 28 Dec 2003 18:55:38 GMT  
 Query action in ms access on ado.command
My MS Access 97 sql query is :

    PARAMETERS KeyFXDCosts Long;
    DELETE *
    FROM tb_CostT
    WHERE tb_CostT.Id_CostT=[KeyFXDCosts];


Quote:
> Then post the SQL of your query, please.



> > Thanks,

> >     I already tried but it doesn't work.




> > > E.G.,

> > > This line is wrong:
> > > objcmd.CommandType = adCmdtext

> > > Change it to
> > > objcmd.CommandType = adCmdStoredProc
> > > --
> > > HTH,
> > > Alexander Shirshov, MCSD



> > > > Hello

> > > > I have a query action (delete) in Ms Access, with a parameter of
type
> > > text.
> > > > I am trying to execute an object command ado, but this one always
> > returns
> > > an
> > > > error. See below.

> > > > << -2147217900 "Invalid SQL statement; expected 'DELETE', 'INSERT',
> > > > 'PROCEDURE', 'SELECT', or 'UPDATE'." >>

> > > > The code :

> > > >   Dim objcmd As ADODB.Command
> > > >   Dim objParm As ADODB.Parameter
> > > >   Dim lngNmbreRecordAffect As Long

> > > >   On Error GoTo Error_DeleteFixedC

> > > >     If mobjCon Is Nothing Then Exit Function
> > > >     If ((Trim(strIDFixedCost) = "") Or (Not
> IsNumeric(strIDFixedCost)))
> > > Then
> > > > Exit Function

> > > >     Set objcmd = New ADODB.Command
> > > >     objcmd.ActiveConnection = mobjCon
> > > >     Set objParm = New ADODB.Parameter
> > > >     objParm.Name = "KeyFXDCosts"
> > > >     objParm.Direction = adParamInput
> > > >     objParm.Type = adVariant
> > > >     objParm.Value = strIDFixedCost
> > > >     objcmd.Parameters.Append objParm
> > > >     objcmd.CommandText = "Q_DeleteFixedCost"
> > > >     objcmd.CommandType = adCmdtext
> > > >     mobjCon.CursorLocation = adUseClient
> > > >     objcmd.Execute , , adExecuteNoRecords
> > > >     Set objcmd = Nothing

> > > >     ...

> > > > If you have an idea?

> > > > thank you in advance

> > > > E.G.



Sun, 28 Dec 2003 19:14:50 GMT  
 Query action in ms access on ado.command
Strange. It should work, I just tested this:

Create this query in standard nwind.mdb:

PARAMETERS OID Long;
DELETE FROM Orders
WHERE OrderID=[OID]

VB code:

  Dim cmd As New Command
  cmd.Parameters.Append cmd.CreateParameter("OID", adInteger, , , -1)
  cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=<change it to point to the nwind.mdb>
  cmd.CommandText = "Query1"
  cmd.Execute


Quote:
> My MS Access 97 sql query is :

>     PARAMETERS KeyFXDCosts Long;
>     DELETE *
>     FROM tb_CostT
>     WHERE tb_CostT.Id_CostT=[KeyFXDCosts];




> > Then post the SQL of your query, please.



> > > Thanks,

> > >     I already tried but it doesn't work.




> > > > E.G.,

> > > > This line is wrong:
> > > > objcmd.CommandType = adCmdtext

> > > > Change it to
> > > > objcmd.CommandType = adCmdStoredProc
> > > > --
> > > > HTH,
> > > > Alexander Shirshov, MCSD



> > > > > Hello

> > > > > I have a query action (delete) in Ms Access, with a parameter of
> type
> > > > text.
> > > > > I am trying to execute an object command ado, but this one always
> > > returns
> > > > an
> > > > > error. See below.

> > > > > << -2147217900 "Invalid SQL statement; expected 'DELETE',
'INSERT',
> > > > > 'PROCEDURE', 'SELECT', or 'UPDATE'." >>

> > > > > The code :

> > > > >   Dim objcmd As ADODB.Command
> > > > >   Dim objParm As ADODB.Parameter
> > > > >   Dim lngNmbreRecordAffect As Long

> > > > >   On Error GoTo Error_DeleteFixedC

> > > > >     If mobjCon Is Nothing Then Exit Function
> > > > >     If ((Trim(strIDFixedCost) = "") Or (Not
> > IsNumeric(strIDFixedCost)))
> > > > Then
> > > > > Exit Function

> > > > >     Set objcmd = New ADODB.Command
> > > > >     objcmd.ActiveConnection = mobjCon
> > > > >     Set objParm = New ADODB.Parameter
> > > > >     objParm.Name = "KeyFXDCosts"
> > > > >     objParm.Direction = adParamInput
> > > > >     objParm.Type = adVariant
> > > > >     objParm.Value = strIDFixedCost
> > > > >     objcmd.Parameters.Append objParm
> > > > >     objcmd.CommandText = "Q_DeleteFixedCost"
> > > > >     objcmd.CommandType = adCmdtext
> > > > >     mobjCon.CursorLocation = adUseClient
> > > > >     objcmd.Execute , , adExecuteNoRecords
> > > > >     Set objcmd = Nothing

> > > > >     ...

> > > > > If you have an idea?

> > > > > thank you in advance

> > > > > E.G.



Sun, 28 Dec 2003 19:51:24 GMT  
 Query action in ms access on ado.command

I tested the code but with MS access 97 and I get back the same error.

    Set objcmd = New ADODB.Command
    Set objParm = objcmd.CreateParameter("KeyTFXDCosts", adVariant, , , CLng(strIDFixedCost))
    objcmd.ActiveConnection = mobjCon
    objcmd.CommandText = "Q_DeleteAllTCostTasksByTFXDCosts"
    objcmd.Execute
    Set objcmd = Nothing

    Thanks

Quote:

> Strange. It should work, I just tested this:

> Create this query in standard nwind.mdb:

> PARAMETERS OID Long;
> DELETE FROM Orders
> WHERE OrderID=[OID]

> VB code:

>   Dim cmd As New Command
>   cmd.Parameters.Append cmd.CreateParameter("OID", adInteger, , , -1)
>   cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data
> Source=<change it to point to the nwind.mdb>
>   cmd.CommandText = "Query1"
>   cmd.Execute



> > My MS Access 97 sql query is :

> >     PARAMETERS KeyFXDCosts Long;
> >     DELETE *
> >     FROM tb_CostT
> >     WHERE tb_CostT.Id_CostT=[KeyFXDCosts];




> > > Then post the SQL of your query, please.



> > > > Thanks,

> > > >     I already tried but it doesn't work.




> > > > > E.G.,

> > > > > This line is wrong:
> > > > > objcmd.CommandType = adCmdtext

> > > > > Change it to
> > > > > objcmd.CommandType = adCmdStoredProc
> > > > > --
> > > > > HTH,
> > > > > Alexander Shirshov, MCSD



> > > > > > Hello

> > > > > > I have a query action (delete) in Ms Access, with a parameter of
> > type
> > > > > text.
> > > > > > I am trying to execute an object command ado, but this one always
> > > > returns
> > > > > an
> > > > > > error. See below.

> > > > > > << -2147217900 "Invalid SQL statement; expected 'DELETE',
> 'INSERT',
> > > > > > 'PROCEDURE', 'SELECT', or 'UPDATE'." >>

> > > > > > The code :

> > > > > >   Dim objcmd As ADODB.Command
> > > > > >   Dim objParm As ADODB.Parameter
> > > > > >   Dim lngNmbreRecordAffect As Long

> > > > > >   On Error GoTo Error_DeleteFixedC

> > > > > >     If mobjCon Is Nothing Then Exit Function
> > > > > >     If ((Trim(strIDFixedCost) = "") Or (Not
> > > IsNumeric(strIDFixedCost)))
> > > > > Then
> > > > > > Exit Function

> > > > > >     Set objcmd = New ADODB.Command
> > > > > >     objcmd.ActiveConnection = mobjCon
> > > > > >     Set objParm = New ADODB.Parameter
> > > > > >     objParm.Name = "KeyFXDCosts"
> > > > > >     objParm.Direction = adParamInput
> > > > > >     objParm.Type = adVariant
> > > > > >     objParm.Value = strIDFixedCost
> > > > > >     objcmd.Parameters.Append objParm
> > > > > >     objcmd.CommandText = "Q_DeleteFixedCost"
> > > > > >     objcmd.CommandType = adCmdtext
> > > > > >     mobjCon.CursorLocation = adUseClient
> > > > > >     objcmd.Execute , , adExecuteNoRecords
> > > > > >     Set objcmd = Nothing

> > > > > >     ...

> > > > > > If you have an idea?

> > > > > > thank you in advance

> > > > > > E.G.



Sun, 28 Dec 2003 20:18:09 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Query action in ms access on ado.command

2. Parameters on action query for MS Access data environment

3. Executing Access action query via ADO

4. MS Access parameter queries using VID and ADO

5. MS Access parameter queries using VID and ADO

6. ADO with MS Access Param Queries

7. ADO Returns less records than Query run in MS Access

8. ADO with MS Access Param Queries

9. MS Access parameter queries using VID and ADO

10. ADO: Help build this query in MS Access

11. Query works in MS Access, but not in VB6 with ADO

12. Help with action query ....ADO Newbie

 

 
Powered by phpBB® Forum Software