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, Feb 11 '19 at 21:30
@WolfgangKais [Access does support views, as well as the CREATE VIEW DDL Statement when used with an Access DB Engine.](https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/create-view-statement-microsoft-access-sql) — Lynn Crumbling, Feb 11 '19 at 21:32
@LynnCrumbling That's new to me, thanks for the update.. What type of object will be created by such a statement? — Wolfgang Kais, Feb 11 '19 at 21:39
@LynnCrumbling I am in an introduction to database techniques course so I'm still pretty new to this. But I am executing this in Access via Create Query Design in SQL View — Christopher Kai Jensen, Feb 11 '19 at 21:39
Also, try [turning on ANSI-92 SQL in the database options](https://stackoverflow.com/q/6382064/656243) — Lynn Crumbling, Feb 11 '19 at 21:39
you can create a select query to be used as View. Views in sql server are basically a select statement. — Kashif Qureshi, Feb 11 '19 at 21:41
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, Feb 11 '19 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, Feb 11 '19 at 21:50
Cool, I am operating on databases supplied as a supplement with my text, I'll have to create a new database and import them over I suppose. — Christopher Kai Jensen, Feb 11 '19 at 21:55
It's worth noting that the "normal" way to create new queries programmatically is with VBA and DAO: http://allenbrowne.com/func-DAO.html#CreateQueryDAO — Andre, Feb 11 '19 at 22:26

score 5 · Answer 1 · answered Feb 12 '19 at 08:18

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).

Lynn Crumbling · Answer 2 · 2019-02-11T22:12:09.413

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>

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

2 Answers2