Question Creating a View in SQL

[Shingen]

I entered the following SQL statements using Access SQL view, but Access says there is an error in the CREATE statement and highlights the VIEW command withing the text. Does anyone know why?

CREATE VIEW Housewares AS
SELECT PartNum, Description, OnHand, Price
FROM Part
WHERE Class = 'NW'
;

Note: Table Part is opened prior to entering these lines.

 

[SOS]

Welcome to AWF.

Is this an ADP?

 

[ByteMyzer]

Hello, Shingen, and welcome to AWF.

The CREATE VIEW statement does not work within the Access SQL Pane. You can, however, execute the statement from VBA:

1) From the Database window, press CTRL-G. This will open the Visual Basic Debug window.

2) In the Debug window, type the following line and press Enter:

CurrentProject.Connection.Execute "CREATE VIEW Housewares AS SELECT PartNum, Description, OnHand, Price FROM Part WHERE Class = 'NW';"

 

[Banana]

Just as a FYI:

You can use SQL pane, at least in Access 2003, by creating a new query, switching to SQL View and type in the statement, then execute it.

But all it'd do is create a new saved querydef, which is kind of pointless because um, we've already opened a new query and could just have put in the SELECT statement and saved it.

I believe CREATE VIEW (as well as CREATE PROCEDURE) was created to provide a interface for developers using Jet without Access environment, but if you have Access, it's not that terribly useful.

Hope that helps.

 

[ByteMyzer]

Banana,

Actually, no, you can't, not within an MDB file, anyway. If you try this with an MDB file, in the SQL Pane, you get the following error:

Syntax error in CREATE TABLE statement

...and the word VIEW is highlighted in the SQL Pane.

 

[Banana]

I just tried it and it worked not once but several times.

IIRC, Jet didn't support CREATE VIEW or CREATE PROCEDURE until 4.0 (e.g. Access 2000 and afterward). I tested it in Access 2003 using 2000 format.

 

[ByteMyzer]

I did the same test (A2003, file in A2000 format) and it DIDN'T work, so there's some other variable we're not accounting for here.

(And yes, my system has all the latest patches/service packs)

 

[Banana]

Apparently.

Let's see if we can compare...

I have Access 2003, SP 3 11.8166.8221.



The specific steps I did.

1) In a scratch database (e.g. db1.mdb), I click on "Create Query in Design View"
2) Close the "Show table" dialog
3) Choose View -> SQL View
4) Enter the statement 'CREATE VIEW test AS SELECT 1;' in the pane.
5) Click Execute (e.g. Red Exclamation) button. The query's title bar should now change to "Query X: Data Definition Query"
6) Close the query without saving it.
7) Click Table tab then back to Query tab to refresh the view.
8) See the newly created query named "Test" there, open it in design view and see that it has the SQL statement "SELECT 1;"

What were your steps?

 

[ByteMyzer]

Same here: Access 2003(11.8166.8221) SP3

I attempted to create a view for an existing table, but I tried replicating your steps to make certain. However, the moment I clicked on the Execute button, I got the Syntax error in CREATE TABLE statement error.

I even went back and set the Query as a Data-Definition Query first (from the SQL Menubar: Query > SQL Specific > Data Definition), and pasted in the statement, and got the same error.

 

[Banana]

I went back and look at the options pane, and found out that I've forgotten that I had my .mdb set to use ANSI-92 SQL by default.

When I turn off ANSI-92 SQL (can be found in Table/Queries pane in Tools -> Option), I get the same behavior as you describe (e.g. Syntax error in CREATE TABLE statement). Turn it back on and it executes fine.

I was puzzled as I thought I had it disabled because other query used # delimiters which isn't valid in ANSI-92 SQL. Testing it, it works in either mode, but I can't use ' delimiters even with ANSI-92 SQL. No wonder why I was confused.

My apologies.

 

[ByteMyzer]

Banana,

No need to apologize; you've actually provided a very useful tidbit of information. I hadn't thought to check that option.


Shingen,

This should work if you have your options set as Banana described:
1) From the Menu Bar, go to Tools > Options.
2) On the [Tables/Queries] tab, click on SQL Server Compatible Syntax (ANSI 92) > This database.
3) Click on the [OK] button. When the Warning prompt appears, click on the [OK] button to apply the change.

 

[djmorrison]

THANK YOU ! ! ! I've looked for 3 hours all over the net and microsoft to find this answer. Thank you Shingen for asking and thank you Banana and ByteMyzer for answering! djm