Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Unhappy Unanswered: Append query problem

    Hi- I am using Access 2002 and I am having a problem executing an append query in code. I get the error
    Run-time error '-2147217900 (80040e14)
    Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

    Here is the code and the SQL behind the query:

    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "qry_cl_sum"
    cmd.Execute

    INSERT INTO tbl_cl ( [Year], Company, LOB, COIType, WithdrawalGroup, IG, cvcd, pnpi, istm, iage_band, sexi, atage_band, dur_band, dur_band2, smoke, jtin, jage, blfq, bstl, anum, aamt, enum, eamt, xnum, xamt, polsize_ind )
    SELECT tbl_cl_all.Year, tbl_cl_all.Company, tbl_cl_all.LOB, tbl_cl_all.COIType, tbl_cl_all.WithdrawalGroup, tbl_cl_all.IG, tbl_cl_all.cvcd, tbl_cl_all.pnpi, tbl_cl_all.istm, tbl_cl_all.isage_band, tbl_cl_all.sexi, tbl_cl_all.atage_band, tbl_cl_all.dura_band, tbl_cl_all.dura_band2, tbl_cl_all.smoke, tbl_cl_all.jtin, tbl_cl_all.jage, tbl_cl_all.blfq, tbl_cl_all.bstc, Sum(tbl_cl_all.anum) AS SumOfanum, Sum(tbl_cl_all.aamt) AS SumOfaamt, Sum(tbl_cl_all.enum) AS SumOfenum, Sum(tbl_cl_all.eamt) AS SumOfeamt, Sum(tbl_cl_all.xnum) AS SumOfxnum, Sum(tbl_cl_all.xamt) AS SumOfxamt, tbl_cl_all.polsize_ind
    FROM tbl_cl_all
    GROUP BY tbl_cl_all.Year, tbl_cl_all.Company, tbl_cl_all.LOB, tbl_cl_all.COIType, tbl_cl_all.WithdrawalGroup, tbl_cl_all.IG, tbl_cl_all.cvcd, tbl_cl_all.pnpi, tbl_cl_all.istm, tbl_cl_all.isage_band, tbl_cl_all.sexi, tbl_cl_all.atage_band, tbl_cl_all.dura_band, tbl_cl_all.dura_band2, tbl_cl_all.smoke, tbl_cl_all.jtin, tbl_cl_all.jage, tbl_cl_all.blfq, tbl_cl_all.bstc, tbl_cl_all.polsize_ind;

    The query works outside of the VBA code perfectly fine, but I get the Invalid SQL statement error when executing it in the code.

    Can anyone help?

    Thanks!

  2. #2
    Join Date
    Jan 2004
    Posts
    184

    Re: Append query problem

    Try this instead:

    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command

    cmd.ActiveConnection = CurrentProject.Connection

    cmd.Execute "INSERT INTO tbl_cl ( [Year], Company, LOB, COIType, WithdrawalGroup, IG, cvcd, pnpi, istm, iage_band, sexi, atage_band, dur_band, dur_band2, smoke, jtin, jage, blfq, bstl, anum, aamt, enum, eamt, xnum, xamt, polsize_ind )
    SELECT tbl_cl_all.Year, tbl_cl_all.Company, tbl_cl_all.LOB, tbl_cl_all.COIType, tbl_cl_all.WithdrawalGroup, tbl_cl_all.IG, tbl_cl_all.cvcd, tbl_cl_all.pnpi, tbl_cl_all.istm, tbl_cl_all.isage_band, tbl_cl_all.sexi, tbl_cl_all.atage_band, tbl_cl_all.dura_band, tbl_cl_all.dura_band2, tbl_cl_all.smoke, tbl_cl_all.jtin, tbl_cl_all.jage, tbl_cl_all.blfq, tbl_cl_all.bstc, Sum(tbl_cl_all.anum) AS SumOfanum, Sum(tbl_cl_all.aamt) AS SumOfaamt, Sum(tbl_cl_all.enum) AS SumOfenum, Sum(tbl_cl_all.eamt) AS SumOfeamt, Sum(tbl_cl_all.xnum) AS SumOfxnum, Sum(tbl_cl_all.xamt) AS SumOfxamt, tbl_cl_all.polsize_ind
    FROM tbl_cl_all
    GROUP BY tbl_cl_all.Year, tbl_cl_all.Company, tbl_cl_all.LOB, tbl_cl_all.COIType, tbl_cl_all.WithdrawalGroup, tbl_cl_all.IG, tbl_cl_all.cvcd, tbl_cl_all.pnpi, tbl_cl_all.istm, tbl_cl_all.isage_band, tbl_cl_all.sexi, tbl_cl_all.atage_band, tbl_cl_all.dura_band, tbl_cl_all.dura_band2, tbl_cl_all.smoke, tbl_cl_all.jtin, tbl_cl_all.jage, tbl_cl_all.blfq, tbl_cl_all.bstc, tbl_cl_all.polsize_ind;"
    In abundance of water only the fool is thirsty. Bob Marley.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •