How can I use CREATE VIEW?

[Mette Dencker]

Hi,
I am trying to create a view by using the create view-command, but it
does not work. For instance I have a table called names, and I want to
create a view selecting all tuples:
CREATE VIEW testview AS
SELECT * FROM names WHERE 1
The error message I get is "Syntax error in the CREATE VIEW statement".
Please help me!
Thanks!
/Mette

 

[Alessandro Baraldi]

Mette Dencker ha scritto:

Hi,
I am trying to create a view by using the create view-command, but it
does not work. For instance I have a table called names, and I want to
create a view selecting all tuples:
CREATE VIEW testview AS
SELECT * FROM names WHERE 1
The error message I get is "Syntax error in the CREATE VIEW statement".
Please help me!
Thanks!
/Mette

Currentproject.Connection.Execute "Create VIEW ViewName AS Select *

From

TableName WHERE Condition(s)....."

@Alex

 

[Mette Dencker]

Hi Alex,
thanks for your very quick answer, but I don't see the difference
between what you suggest and what I did myself, except for the
Currentproject.Connection.Execute-part, and I really don't know where to
put it! Could you specify exactly what I should enter?
Thanks!
/Mette

 

[Alessandro Baraldi]

Mette Dencker ha scritto:

Hi Alex,
thanks for your very quick answer, but I don't see the difference
between what you suggest and what I did myself, except for the
Currentproject.Connection.Execute-part, and I really don't know where to
put it! Could you specify exactly what I should enter?
Thanks!
/Mette

The code i wrote is to create the view by code....

I don't know what are you doing and what you need ....!

If you need to create the view by QueryBuilder on ADP project select
Query and with the template New View.... here you can create it by
Visual Object

If you need to create by code use rhe example on Click_Button Event to
make the test...

@Alex

 

[Mette Dencker]

Ok... I am using SQL-statements in MS Access.
I have created the table names by the following statement:
create table names(
name varchar(20),
age varchar(2),
ID char(5),
primary key (ID))

Now I want to create a view, to begin with just copying the names-table
(to see if I get things right) and I state:
create view newnames as
select * from names where 1

This is the cause of my "Syntax error in the CREATE VIEW
statement"-error message and I don't know what I am doing wrong!!! Maybe
I have to specify the attribute names in the view table (for instance
with create view newnames (newname, newage, newID) as ...) but I don't
know how...
Thanks!

 

[Brendan Reynolds]

You have to either execute the CREATE VIEW SQL statement programmatically
via ADO, as in Alessandro's example, or enable the 'SQL Server Compatible
Syntax (ANSI 92) option. To find that option, select Options from the Tools
menu and select the Tables/Queries tab. However, there is at least one known
issue with that option (see the Microsoft Knowledge Base) so I'd go with
executing the SQL statement programmatically.

 

[John Vinson]

This is the cause of my "Syntax error in the CREATE VIEW
statement"-error message and I don't know what I am doing wrong!!!

What you're doing wrong is using the JET database engine - which does
not support Views.

Access is NOT SQL/Server (though of course it can link to SQL/Server).
It uses a distinctly different version of SQL.

You can run a PassThrough query to a SQL (or MySQL, or Oracle, or
other) database engine, if that's where your tables reside; or, if
you're working in the MDB environment, you can create Queries and use
them in very much the same way that you would use SQL Views. Unlike
SQL/Server, many two or multi-table queries are updateable and you
don't need triggers to update them (and for that matter, triggers have
only JUST become available).

John W. Vinson[MVP]

 

[aaron.kempf]

John

Access _IS_ SQL Server.. because everyone knows that MDB is obsolete.

open Access, File, New, Project (existing data) and choose your server
and database.

go under the 'queries' tab and hit 'create query'
at which time you get a wizard.. you can select 'create view' there

or of course..
Docmd.Runsql "create view dbo.myview as select * from sysobjects"

should work just find

btw, under Access 2000; these 'queries' are seperate into views and
stored procedures; I miss the Access 2000 interface; it also has a
better interface for creating tables

-Aaron

 

[aaron.kempf]

I strongly agree.. sprocs and views are available under MDB; they just
dont show up in the UI ROFL

Access Data Projects sure don't have any problems writing views.
And with ADP your queries don't throw a tissyfit when they 'get too
complex'

 

[John Vinson]

Oh yes it does! See:

Apologies. I haven't used this feature and had forgotten that it is
now available.

John W. Vinson[MVP]

 

[aaron.kempf]

yeah yet another symptom that Access MVPs are a bunch of fucking
retards.

Tow the party line; and cement your skillset as a decade obsolete;
agree with everything that Microsoft says-- and DONT SAY ANYTHING
NEGATIVE

bunch of flaming pussies you MVPs are

maybe if you kids grew up and learned ADP you wouldn't have to deal
with a crappy ass engine that doesn't support stored procedures through
the UI.

Stored Procedures are 100 times more powerful than ANYTHING you can see
in the UI.
Stored Procedures are 100 times more powerful than ANYTHING you can see
in the UI.
Stored Procedures are 100 times more powerful than ANYTHING you can see
in the UI.
Stored Procedures are 100 times more powerful than ANYTHING you can see
in the UI.
Stored Procedures are 100 times more powerful than ANYTHING you can see
in the UI.
Stored Procedures are 100 times more powerful than ANYTHING you can see
in the UI.
Stored Procedures are 100 times more powerful than ANYTHING you can see
in the UI.
Stored Procedures are 100 times more powerful than ANYTHING you can see
in the UI.
Stored Procedures are 100 times more powerful than ANYTHING you can see
in the UI.

 

[John Vinson]

[Access MVPs] Tow the party line; and cement your skillset as a decade obsolete;
agree with everything that Microsoft says-- and DONT SAY ANYTHING
NEGATIVE

Hey, take that back! I get negativity from plenty of MVPs <g> (in all
seriousness, John Vinson isn't one of them.)

I don't even see Aaron's posts unless someone quotes them. And I don't
let them bother me if I do.


John W. Vinson[MVP]

 

[aaron.kempf]

hey MDB wuss

if you can't stand the heat; get out of the kitchen

you guys are the fucking retards that are reccomending that newbies
learn TWO DIFFERENT DIALECTS OF SQL

[Access MVPs] Tow the party line; and cement your skillset as a decade obsolete;
agree with everything that Microsoft says-- and DONT SAY ANYTHING
NEGATIVE

Hey, take that back! I get negativity from plenty of MVPs <g> (in all
seriousness, John Vinson isn't one of them.)

I don't even see Aaron's posts unless someone quotes them. And I don't
let them bother me if I do.


John W. Vinson[MVP]

 

[aaron.kempf]

if you can't hang with the BIG DOGS then get back on the PORCH

eat shit MDB wimps

hey MDB wuss

if you can't stand the heat; get out of the kitchen

you guys are the fucking retards that are reccomending that newbies
learn TWO DIFFERENT DIALECTS OF SQL

[Access MVPs] Tow the party line; and cement your skillset as a decade obsolete;
agree with everything that Microsoft says-- and DONT SAY ANYTHING
NEGATIVE

Hey, take that back! I get negativity from plenty of MVPs <g> (in all
seriousness, John Vinson isn't one of them.)

I don't even see Aaron's posts unless someone quotes them. And I don't
let them bother me if I do.


John W. Vinson[MVP]

 

[aaron.kempf]

and when you fucking assholes stop SPREADING MIS-INFORMATION about ADP
is when I stop talking dirty

ADP has a brighter future than MDB and your DAO-revival _CRAP_

hey MDB wuss

if you can't stand the heat; get out of the kitchen

you guys are the fucking retards that are reccomending that newbies
learn TWO DIFFERENT DIALECTS OF SQL

[Access MVPs] Tow the party line; and cement your skillset as a decade obsolete;
agree with everything that Microsoft says-- and DONT SAY ANYTHING
NEGATIVE

Hey, take that back! I get negativity from plenty of MVPs <g> (in all
seriousness, John Vinson isn't one of them.)

I don't even see Aaron's posts unless someone quotes them. And I don't
let them bother me if I do.


John W. Vinson[MVP]

 

[punjab_tom]

why do you talk so rude?

be nice and people might be nice back

-Tom

and when you fucking assholes stop SPREADING MIS-INFORMATION about ADP
is when I stop talking dirty

ADP has a brighter future than MDB and your DAO-revival _CRAP_

hey MDB wuss

if you can't stand the heat; get out of the kitchen

you guys are the fucking retards that are reccomending that newbies
learn TWO DIFFERENT DIALECTS OF SQL

On 1 Nov 2006 00:33:43 -0800, "Jamie Collins"

[Access MVPs] Tow the party line; and cement your skillset as a decade obsolete;
agree with everything that Microsoft says-- and DONT SAY ANYTHING
NEGATIVE

Hey, take that back! I get negativity from plenty of MVPs <g> (in all
seriousness, John Vinson isn't one of them.)

I don't even see Aaron's posts unless someone quotes them. And I don't
let them bother me if I do.


John W. Vinson[MVP]