Create View?

[dwa]

How does one create a VIEW in Access/Jet?

The Access Jet referrence implies that you can do this using the CREATE VIEW
statement:

=====

Syntax
CREATE VIEW view [(field1[, field2[, ...]])] AS selectstatement

The CREATE VIEW statement has these parts:

Part Description
view The name of the view to be created.
field1, field2 The name of field or fields for the corresponding
fields specified in selectstatement.
selectstatement A SQL SELECT statement. For more information, see
SELECT Statement.


===

But when I try "CREATE VIEW TestView AS select * from sometable" from an
Access Query Window, I get this error:

"Syntax error in CREATE TABLE statement"

Can anyone shed any light on this mystery?

Thanks,

-- dwa

 

[Norman Yuan]

If the database is Jet DB (*.mdb), you cannot create "View", Jet DB does not
have View. If you use Access as SQL Server's front end (*.adp), then you can
create View in a SQL Server database.

 

[Arvin Meyer]

Using Jet (an MDB) you click on Queries, then New. For a DDL query (Create
Table syntax) you then use the menubar to choose SQL Specific, then Data
Definition. The correct syntax for creating a table in like:

CREATE TABLE tblContacts
([ContactID] counter,
[LastName] text (30),
[FirstName] text (30),
[ContactDate] date,
[Notes] memo,
CONSTRAINT [ContactID] PRIMARY KEY ([ContactID]));
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

How does one create a VIEW in Access/Jet?

The Access Jet referrence implies that you can do this using the CREATE VIEW
statement:

=====

Syntax
CREATE VIEW view [(field1[, field2[, ...]])] AS selectstatement

The CREATE VIEW statement has these parts:

Part Description
view The name of the view to be created.
field1, field2 The name of field or fields for the corresponding
fields specified in selectstatement.
selectstatement A SQL SELECT statement. For more information, see
SELECT Statement.


===

But when I try "CREATE VIEW TestView AS select * from sometable" from an
Access Query Window, I get this error:

"Syntax error in CREATE TABLE statement"

Can anyone shed any light on this mystery?

Thanks,

-- dwa

 

[Arvin Meyer]

I might add, that you save a simple SQL statement to create a view (in
Access/Jet called a query)

You can just type the SQL statement into an SQL window, or use the QBE grid.
Save it with a name when you close.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Using Jet (an MDB) you click on Queries, then New. For a DDL query (Create
Table syntax) you then use the menubar to choose SQL Specific, then Data
Definition. The correct syntax for creating a table in like:

CREATE TABLE tblContacts
([ContactID] counter,
[LastName] text (30),
[FirstName] text (30),
[ContactDate] date,
[Notes] memo,
CONSTRAINT [ContactID] PRIMARY KEY ([ContactID]));
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

How does one create a VIEW in Access/Jet?

The Access Jet referrence implies that you can do this using the CREATE VIEW
statement:

=====

Syntax
CREATE VIEW view [(field1[, field2[, ...]])] AS selectstatement

The CREATE VIEW statement has these parts:

Part Description
view The name of the view to be created.
field1, field2 The name of field or fields for the corresponding
fields specified in selectstatement.
selectstatement A SQL SELECT statement. For more information, see
SELECT Statement.


===

But when I try "CREATE VIEW TestView AS select * from sometable" from an
Access Query Window, I get this error:

"Syntax error in CREATE TABLE statement"

Can anyone shed any light on this mystery?

Thanks,

-- dwa

 

[James Goodman]

Are you trying to create a view in access which is connected to SQL Server
or create the equivilent of a 'view' in an Access MDB?

If the latter, CREATE VIEW wont work as Access doesnt have views. You can
create queries, which can be the approximate equivilent of either Views or
SP's...

 

[dwa]

James,

Thanks - yes, this is what I did. I realized that if I save a query, that
I can then issue queries against that query, using the same syntax that I
would use to query any other table. (But then, what's all this Jet
Documentation about? Just make-believe?<g>)

I'm assuming the query can be queried in this fashion from ADO/C++ (that is,
outside of the Access IDE) since it is contained within the MDB. We'll
see...

-- dwa

Are you trying to create a view in access which is connected to SQL Server
or create the equivilent of a 'view' in an Access MDB?

If the latter, CREATE VIEW wont work as Access doesnt have views. You can
create queries, which can be the approximate equivilent of either Views or
SP's...



--
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/

How does one create a VIEW in Access/Jet?

The Access Jet referrence implies that you can do this using the CREATE VIEW
statement:

=====

Syntax
CREATE VIEW view [(field1[, field2[, ...]])] AS selectstatement

The CREATE VIEW statement has these parts:

Part Description
view The name of the view to be created.
field1, field2 The name of field or fields for the corresponding
fields specified in selectstatement.
selectstatement A SQL SELECT statement. For more information, see
SELECT Statement.


===

But when I try "CREATE VIEW TestView AS select * from sometable" from an
Access Query Window, I get this error:

"Syntax error in CREATE TABLE statement"

Can anyone shed any light on this mystery?

Thanks,

-- dwa

 

[Brendan Reynolds]

I *think* CREATE VIEW was one of those new features that were added in JET
4, which are only available via ADO. If I'm right, it will work if you
execute the DDL query via an ADO connection. I'm not sure whether there
would be any advantage in doing it that way, though. I don't *think* the
result is any different from a select query created using the other methods
available.

--
Brendan Reynolds (MVP)

James,

Thanks - yes, this is what I did. I realized that if I save a query, that
I can then issue queries against that query, using the same syntax that I
would use to query any other table. (But then, what's all this Jet
Documentation about? Just make-believe?<g>)

I'm assuming the query can be queried in this fashion from ADO/C++ (that is,
outside of the Access IDE) since it is contained within the MDB. We'll
see...

-- dwa

Are you trying to create a view in access which is connected to SQL Server
or create the equivilent of a 'view' in an Access MDB?

If the latter, CREATE VIEW wont work as Access doesnt have views. You can
create queries, which can be the approximate equivilent of either Views or
SP's...



--
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/

How does one create a VIEW in Access/Jet?

The Access Jet referrence implies that you can do this using the

CREATE
VIEW

statement:

=====

Syntax
CREATE VIEW view [(field1[, field2[, ...]])] AS selectstatement

The CREATE VIEW statement has these parts:

Part Description
view The name of the view to be created.
field1, field2 The name of field or fields for the corresponding
fields specified in selectstatement.
selectstatement A SQL SELECT statement. For more information, see
SELECT Statement.


===

But when I try "CREATE VIEW TestView AS select * from sometable" from an
Access Query Window, I get this error:

"Syntax error in CREATE TABLE statement"

Can anyone shed any light on this mystery?

Thanks,

-- dwa