CODEWITHSUNDEEP
sqlms-accesssyntax-error
Christopher Kai Jensen
Christopher Kai Jensen

Reputation: 41

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

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
;

Upvotes: 4

Views: 5508

Answers (2)

Erik A
Erik A

Reputation: 32642

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

Upvotes: 5

Lynn Crumbling
Lynn Crumbling

Reputation: 13357

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>

Upvotes: 4

Related Questions