How do I do a CREATE VIEW in Access without Getting Syntax Error?

G

Guest

We are attempting to create a view using Access 2003. THe help file shows the
format but everytime we run the command, the following error pops up:

Syntax error in CREATE TABLE statement.

The command we are using is:
CREATE VIEW Test1
AS Select * FROM Book;
 
A

Albert D.Kallal

You can't use the interface to execute this command (but, then again, why
would you with the query builder!).

Remember, ANY select query you build in the query builder is really the
function equivalent of a view (and, in fact, when you use the create view
command in code, the result is a simple and normal saved query in
ms-access).

To create a view in code, you MUST use ado, and not dao. So, while the
following will work:

currentdb.Execute "update tblCustomers set City = 'New York' where city =
'NY'"

This one will not:
currentdb.Execute "CREATE VIEW Test1 AS Select * FROM Book"

The above will not work, but you *can* use:

currentproject.Connection.Execute "create view testv1 as select * from
tblAnswers"

If you do the above, you will see the standard select query is created in
the query builder.

Do note that all of the above commands could be typed direct in the debug
window, and I often do this when I am in a hurry, and don't want to write
code.
 
G

Guest

Thank you Albert.

As to why we would do this, it was called for in the DBMS course I am taking.

What mislead us about the command not working was its clear documentation in
both the online and offline help. According to those, there is no reason not
to expect the command to work. I question whether or not that command should
be documented as it is; perhaps it could be made clearer that it will not
work in the SQL view window.

Cheers,
TerryD
 
A

Albert D.Kallal

TerryD said:
Thank you Albert.

As to why we would do this, it was called for in the DBMS course I am
taking.

What mislead us about the command not working was its clear documentation
in
both the online and offline help. According to those, there is no reason
not
to expect the command to work. I question whether or not that command
should
be documented as it is; perhaps it could be made clearer that it will not
work in the SQL view window.
Click to expand...

I would bet bottoms to dollars you were reading "jet" documentation, and not
ms-access documentation. You can use ms-access with MySql, and till very
recently, you did not have the create view feature (I don't know if it is
implemented yet). So, when you read about using triggers with the database,
that has little, or nothing to do with a development tool like VB, c++, or
ms-access. You don't want to confuse the concept of a database like Oracle,
or JET with that of a developers tool like VB, or ms-access. They are much
different tools.

The "create-view" command was added to jet4, and is really only a feature
for developers writing code, and using JET, it is not a ms-access
feature....

Since you can write code in VB or ms-access, and not use jet, nor have
anything to do with JET..then it is kind of a separate issue. Features of
the particular database you use with VB, or a programming system ms-access
is separate issue of that of the database.

People don't call VB a database, but for some strange reason, they often
call a developers tool like ms-access a database, and that is plan wrong.

You can't create forms, or reports with sql-server, but you can with VB, or
ms-access.
 
A

Albert D.Kallal

The other posters question/point in this thread does show that if you turn
on ansi92 support, then you can indeed use create view in a query...

So, it is very possible that you were reading about ms-access, and not
specially JET.....

Do note, that even with ansi92 supprot turned on, you still have to use ADO
in code, but at least you *can* use the query builder also....
 
D

Dirk Goldgar

TerryD said:
Thank you Albert.

As to why we would do this, it was called for in the DBMS course I am
taking.

What mislead us about the command not working was its clear
documentation in both the online and offline help. According to
those, there is no reason not to expect the command to work. I
question whether or not that command should be documented as it is;
perhaps it could be made clearer that it will not work in the SQL
view window.
Click to expand...

I think your point is valid. Although the different help files apply to
different components of the product -- and Jet isn't really a component
of Access itself, but a separate product -- it would be good if there
were something, somewhere in the Access documentation, that identified
elements that aren't supported directly by Access.
 
A

Albert D.Kallal

As mentioned in my other post here is that if you do turn on ansi92 then you
*can* use create view

(you still can't use it with dao, but with the query builder...you can!).

I did not know this!!

One great reason that I spend time here is that continue to learn something
weekly, if not daily in these newsgroups!
 
D

Dirk Goldgar

Albert D.Kallal said:
As mentioned in my other post here is that if you do turn on ansi92
then you *can* use create view

(you still can't use it with dao, but with the query builder...you
can!).
Click to expand...

Yes, I saw that, and I didn't know it either. I still think these
issues should be documented somewhere in the Access help file.
One great reason that I spend time here is that continue to learn
something weekly, if not daily in these newsgroups!
Click to expand...

Ain't it the truth!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create View? 6
Create table statement syntax error in access 2000 0
RunSQL syntax error 2
Access Syntax Question 3
CREATE VIEW 4
Can't create a report from my query in Access 1
From Access how to create stored procedure in SQL server 4
SQL Syntax Error??? 1

Top