By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

sql problem

P: 53
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
Jan 10 '08 #1
Share this Question
Share on Google+
7 Replies


FishVal
Expert 2.5K+
P: 2,653
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
Jan 10 '08 #2

P: 53
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?
Jan 10 '08 #3

FishVal
Expert 2.5K+
P: 2,653
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

Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute "bla-bla-bla" & _
  2.     "bla-bla-bla" & _
  3.     "bla-bla-bla" & _
  4.     "bla-bla-bla"
  5.  
Jan 10 '08 #4

P: 53
No tried both as instructed ....no joy.


is the syntax different from a query when trying to using it as an event procedure
Jan 10 '08 #5

FishVal
Expert 2.5K+
P: 2,653
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.
Jan 10 '08 #6

P: 53
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
Jan 12 '08 #7

FishVal
Expert 2.5K+
P: 2,653
Use DoCmd.SetWarnings method to switch on/off the pronpts.
Jan 12 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.