sql problem

[araman]

I get compile error expected end of statement on this sql ...any ideas

CurrentDb.Execute "CREATE TABLE SELECT employee.employee, Orders.[Order Date], Vendors.Vendors, Orders.Received, Orders.[Ace Number], Orders.Points INTO [Special Order Contest]
FROM Vendors INNER JOIN (Customers INNER JOIN (employee INNER JOIN Orders ON employee.[Employee id] = Orders.[Employee ID]) ON Customers.[Phone Numbers] = Orders.[Phone Numbers]) ON Vendors.Vendors = Orders.Vendor
WHERE Orders.Received Between [Enter Start Date] And [Enter End Date];"


Thanks,

Lance

[FishVal]

Hi, araman.

Your SQL statement is a strange hybrid of CREATE TABLE and SELECT INTO statements, but looks more like SELECT INTO though. Just remove CREATE TABLE from the expression and I guess it will work fine.

Regards,
Fish

[araman]

Hi, araman.

Your SQL statement is a strange hybrid of CREATE TABLE and SELECT INTO statements, but looks more like SELECT INTO though. Just remove CREATE TABLE from the expression and I guess it will work fine.

Regards,
Fish

i have a make table query that has some parameters. i was trying to do it with code from a command button on a form without being prompted with confirmation dialogs. i just copied and pasted the sql from the query adding the currentdb.execute to the begining. Is that any help?

[FishVal]

i have a make table query that has some parameters. i was trying to do it with code from a command button on a form without being prompted with confirmation dialogs. i just copied and pasted the sql from the query adding the currentdb.execute to the begining. Is that any help?

Did it help to remove CREATE TABLE from the statement?

idea #2
Just now have paid attention that you get compile error.
Is the code written in the same one line or in multiple?
If the latter is right then you need to right the string as concatenation of substrings putting "_" at the end of code lines

CurrentDb.Execute "bla-bla-bla" & _
    "bla-bla-bla" & _
    "bla-bla-bla" & _
    "bla-bla-bla"
 

[araman]

No tried both as instructed ....no joy.


is the syntax different from a query when trying to using it as an event procedure

[FishVal]

No tried both as instructed ....no joy.

Ok. Figured it out.

DAO.Database.Execute method does not support query parameters.
Use DoCmd.RunSQL instead. Or save the query and use DoCmd.OpenQuery to run it.

is the syntax different from a query when trying to using it as an event procedure

No, the syntax is the same.

[araman]

Ok. Figured it out.

DAO.Database.Execute method does not support query parameters.
Use DoCmd.RunSQL instead. Or save the query and use DoCmd.OpenQuery to run it.

No, the syntax is the same.

Thanks,
But it still doesn't like the SQL from the query

The openquery runs but i still get the make table confirmation prompts which is what i was trying to get away from.

Thanks,

Lance

[FishVal]

Use DoCmd.SetWarnings method to switch on/off the pronpts.