CREATE VIEW in MS ACCESS with SQL returning Syntax error CREATE TABLE

Question

I am trying to CREATE VIEW in access in SQL view but I am getting a syntax error for CREATE TABLE which is highlighting the word VIEW. This is in Access 2016 via Office 365 (latest update as of 2/11/2019). The SELECT statement works by itself, but the CREATE VIEW command isn't. My book (Concepts of Database Management) is designed for use specifically alongside Access. My code is as such:

CREATE VIEW TopLevelCust AS
SELECT CustomerNum, CustomerName, Street, Balance, CreditLimit
FROM Customer
WHERE CreditLimit>=10000
;

Hi Christopher, welcome to StackOverflow. In a Microsoft Access database there are no "views", just tables, indexes and queries (and some other object types like forms and reports that can't be managed using a SQL statements). Are you sure that your statement should be executed in a Microsoft Access database? — Wolfgang Kais
– Wolfgang Kais, Commented Feb 11, 2019 at 21:30
@WolfgangKais Access does support views, as well as the CREATE VIEW DDL Statement when used with an Access DB Engine. — Lynn Crumbling
– Lynn Crumbling, Commented Feb 11, 2019 at 21:32
I see that option under Object Designers but it wont let me apply to the current databse, only select "Default for new databases" — Christopher Kai Jensen
– Christopher Kai Jensen, Commented Feb 11, 2019 at 21:43
I just tried that out: Checked the option (for new databases), created a new database, and indeed, in that database, a CREATE VIEW can be executed. It creates a query. :-) — Wolfgang Kais
– Wolfgang Kais, Commented Feb 11, 2019 at 21:50

Erik A · Accepted Answer · 2019-02-12 08:18:34Z

5

As already stated in Lynn's answer, if you want to execute this query, you can do that after turning on SQL server compatible syntax.

However, you can also execute the query using an OLEDB connection to the Access database.

You can even do this using VBA and the already preset CurrentProject.Connection object:

CurrentProject.Connection.Execute "CREATE VIEW Query1 AS SELECT 1"

Without turning on SQL server compatible syntax, DDL statements executed from Access itself are fairly limited (for example, you can also not use the Decimal data type). But these DDL statements are not really meant to be executed from Access itself, VBA provides way better tools to create queries (that also allows creating pass-through queries, for example).

answered Feb 12, 2019 at 8:18

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lynn Crumbling · Accepted Answer · 2019-02-11 22:12:09Z

4

According to the asker and other users, enabling ANSI-92 SQL in the database options will allow you to execute the DDL statement CREATE VIEW.

File > Options > Object Designers > Query Design.

According to Wolfgang, under the hood, this actually creates a query.

<SoapBox>

It surprises me that your text reference requests you to execute statements that aren't enabled by default in Access, especially without a special note screaming at you that you need to enable a special option before database creation. ¯\_(ツ)_/¯

</SoapBox>

edited Feb 11, 2019 at 22:12

answered Feb 11, 2019 at 22:04

Lynn Crumbling

13.4k8 gold badges61 silver badges98 bronze badges

Collectives™ on Stack Overflow

CREATE VIEW in MS ACCESS with SQL returning Syntax error CREATE TABLE

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related