Creating a View

[IronRocket]

I'm retraining myself in SQL and I'm trying to create a view. I am using the following "script" to build a view in Access:

Create VIEW NewCustomer ([Customer ID], [Customer Name], [Last Year's Salary])
AS SELECT [Customer ID], [Customer Name], [Last Year's Salary]
FROM Customer;


When I do this...Access comes back and says that there is a "Syntax error in Create Table Statement".

After I received this error statement, I used the online help and keyed in "Create View" and it says the following:

"The Microsoft Jet database engine does not support the use of CREATE VIEW, or any of the DDL statements, with non-Microsoft Jet database engine databases.".

Sooo, what the heck is this trying to tell me. I can't create views in Access. I don't know Access that well, and I'm just trying to sharpen my SQL. I use to write SQL in TSO querying against DB2. Just wondering if anyone has any ideas?

 

[lespaul]

no you can't create views in access...

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[IronRocket]

Bummer. I got this book called "SQL for Microsoft Access" and has a whole chapter devoted to Creating Views.

 

[lespaul]

I found this definition of a view which sums it up nicely:
[tt]
In a database management system, a view is a way of portraying information in the database. This can be done by arranging the data items in a specific order, by highlighting certain items, or by showing only certain items. For any database, there are a number of possible views that may be specified. Databases with many items tend to have more possible views than databases with few items. Often thought of as a virtual table, the view doesn't actually store information itself, but just pulls it out of one or more existing tables. Although impermanent, a view may be accessed repeatedly by storing its criteria in a query.[/tt]

You could do a MAKE TABLE command in access if you need to create a physical representation of a view to manipulate...but basically a view is a subset of data from a table, just like a query....

 

[r937]

a "view" in ms access is simply a stored query

take this query and store it (hmmm, sounds like a country song...)

SELECT [Customer ID]
     , [Customer Name]
     , [Last Year's Salary]
  FROM Customer

now you can simply reference this stored query in another query...

SELECT ...
  FROM [b]NewCustomer[/b]

simple, yes?



r937.com | rudy.ca

 

[IronRocket]

Simple r937. Thanks a lot 4 your help! Now I can continue on w/ my SQL.

IronRocket

http://www.FuzzySnake.com

 

[r937]

i'd be interested in knowing the ISBN of your book with the chapter on creating views in Access, just out of curiosity, to see if they weren't perhaps talking about stored queries after all...

r937.com | rudy.ca

 

[ZmrAbdulla]

http://msdn2.microsoft.com/en-us/library/aa139977.aspx

may be one to see

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[r937]

from that article:

The CREATE VIEW and DROP VIEW statements can be executed only through the Jet OLE DB provider and ADO. They will return an error message if used through the Access SQL View user interface or DAO. Also note that views created with the CREATE VIEW statement are saved in the database, but are not exposed as saved queries in the Access user interface. You can work with them only in ADO and ADOX programming code.

thanks, ZmrAbdulla




r937.com | rudy.ca

 

[jrbarnett]

The big difference of course is that an Access stored query can contain an ORDER BY clause, whereas in a "view" in SQL Server can't. Not sure about other database engines though.

John

 

[r937]

actually, a sql server view can contain an ORDER BY

this is the reason why so many views are written with the absurd SELECT TOP 100 PERCENT ...

r937.com | rudy.ca

 

[r937]

i should add: they can, but shouldn't -- the ORDER BY belongs in the outer SELECT

r937.com | rudy.ca

 

[jrbarnett]

Just tried it - I get

Server: Msg 1033, Level 15, State 1, Procedure testview, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

on SQL 2000 and

Msg 1033, Level 15, State 1, Procedure testview2, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

on SQL 2005.

Agree that the "Top n" syntax isn't particularly useful.

Off topic - my recommendation is to use a stored procedure instead on SQL Server, they can return data sorted with order by clauses.

John