Textbook error? (1 Viewer)

[Tina49]

I am adjunct currently teaching Intro to Databases. An example in the text books for creating new views in SQL in Access 2013 is as follows

CREATE VIEW Games AS
SELECT ItemNum, Description, OnHand, Price
FROM ITEM
WHERE Category='GME'

When I run the query it gives me a syntax error in the CREATE TABLE statement

This book seems to be full of errors and so far I have been able to figure them out; however, this time I am unsure. Thanks in advance for any help!

 

[spikepl]

I am surprised by actual SQL Views supposedly being made available in A2013. Your example is most likely T-SQL for MS SQL, not SQL for Access. Removing the first line would make it workable.

 

[GinaWhipp]

I'm curious... what is the name of this book?

 

[jdraw]

Here is an example of creating a view in acc2010. I have found that you have to use the CurrentProject.Connection to get various DDL queries to execute.

'---------------------------------------------------------------------------------------
' Procedure : tryView
' Author    : mellon
' Date      : 20/09/2015
' Purpose   : Attempt to create a VIEW in MSAccess SQL
'ref: http://www.access-programmers.co.uk/forums/showthread.php?t=281123
'---------------------------------------------------------------------------------------
'
Sub tryView()
    Dim sql As String
   On Error GoTo tryView_Error

    sql = "CREATE VIEW  XProd AS " _
          & "SELECT ProductId,ProductName,UnitsInStock " _
          & "From Products WHERE CategoryID=2"
    CurrentProject.Connection.Execute sql, dbFailOnError

   On Error GoTo 0
   Exit Sub

tryView_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure tryView of Module AWF_Related"
End Sub

I have attached a jpg of the View design. (View becomes a query)

Here is result of executing the View.

Product ID	Product Name	Units In Stock
3	Aniseed Syrup	13
4	Chef Anton's Cajun Seasoning	53
5	Chef Anton's Gumbo Mix	0
6	Grandma's Boysenberry Spread	120
8	Northwoods Cranberry Sauce	6
15	Genen Shouyu	39
44	Gula Malacca	27
61	Sirop d'érable	113
63	Vegie-spread	24
65	Louisiana Fiery Hot Pepper Sauce	76
66	Louisiana Hot Spiced Okra	4
77	Original Frankfurter grüne Soße	32

I hope this is helpful.
Good luck with your project.

 

[spikepl]

I sit corrected!

 

[jdraw]

spike and others,

It seems that the DDL SQL things are "intentionally hidden".

Here are a couple of other things where CurrentProject.Connection worked:

Setting up Check Constraint:
CurrentProject.Connection.Execute "CREATE TABLE JPersons (P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes'));"


Dropping Check Constraint:
CurrentProject.Connection.Execute "ALTER TABLE JPersons DROP CONSTRAINT chk_person"

 

[arnelgp]

views can also be created using ADOX model, but you must have reference to ADO Ext x.x for DLL and Security.

Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
' Open the Catalog
cat.ActiveConnection = CurrentProject.Connection
' Create the Command object that represents the View
cmd.CommandText = "SELECT ItemNum, Description, OnHand, Price
FROM ITEM
WHERE Category='GME'"
' Create the View
cat.Views.Append "Games", cmd
' Clean up
Set cat.ActiveConnection = Nothing
Set cat = Nothing
Set cmd = Nothing
End Sub

"view" in ADOX is "querydef" in DAO. once you run the above code. "Games" will appear as in "Queries Group" on Navigation Pane.

 

[GinaWhipp]

Thanks jdraw... Just knew there couldn't be a book out there that was that inaccurate.

 

[Tina49]

Thanks everyone! I am going to include the examples given here to my students. Although programming isn't until the end of the textbook, it does give us the ability to create views which is what we are covering right now.