Append query error

[Guest]

Hi - I have Access 2002 and I am getting an error when I try to run an append query in code. Just running the query itself works but when I try to execute it from code, I get the error "invalid sql statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. The query works fine outside of the code but not from within the code. Can anybody help?

 

[Allen Browne]

Looks like Access is not getting past the first word in your query.

One of the differences is that the expression service is not availble in
code with the Execute method. So if your query statement includes a
references such as:
"... WHERE MyId = Forms!MyForm!MyTextBox ..."
try concatenating the value into the string:
" ... WHERE MyId = " & Forms!MyForm!MyTextBox & " ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi - I have Access 2002 and I am getting an error when I try to run an

append query in code. Just running the query itself works but when I try to
execute it from code, I get the error "invalid sql statement; expected
'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. The query works
fine outside of the code but not from within the code. Can anybody help?

 

[Allen Browne]

We might need to see the SQL string to make further suggestions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, but all my query is doing is grouping by a bunch of

variables and summing by a few others and then appending to a table. Any
other ideas? Thanks!

 

[Guest]

Hi - here is the code
Dim cmd As ADODB.Comman

Set cmd = New ADODB.Comman
cmd.ActiveConnection = CurrentProject.Connectio
cmd.CommandText = "qry_cl_sum"
cmd.Execut

and here is the SQL of the query

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_in
FROM tbl_cl_al
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.

Thanks

 

[Allen Browne]

The query statement looks fine.

I don't use ADO, but I suspect the CommandText has to be a SQL statement,
not the name of a query. You could point the command to the member of the
Procedures() and execute that. Alternatively, put the entire query statement
in a string, and set that to the CommandText.

As another alternative, exeucte it under DAO:
With DBEngine(0)(0)
.Execute .QueryDefs("qry_cl_sum"), dbFailOnError
End With

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi - here is the code:
Dim cmd As ADODB.Command

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

and here is the SQL of the query:

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, tb
l_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.