"Syntax error in CREATE TABLE statement" while trying to create CREATE VIEW

Question

I keep getting the "Syntax error in CREATE TABLE statement" error message when I try to run the following CREATE VIEW statement in Access:

CREATE VIEW SeanWalshOrders (cNo, cName, street, city, county, discount) AS SELECT cNo, cName, street, city, county, discount FROM orders WHERE saleRepName = "Sean Walsh"; 

Answer 1

The CREATE VIEW statement will work if it is executed using an OLEDB connection, for example, from an external application or through the CurrentProject.Connection object in Access VBA. However, Access does not accept CREATE VIEW statements when entering the query designer into the SQL panel inside Access itself (which uses a DAO connection, not OLEDB). In this context, it expects a CREATE TABLE statement (as indicated by the error message).

See the related question for more details:

Trying CREATE VIEW in Access yields "Syntax error in CREATE TABLE statement"