Views in SQL; whats in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I was just getting started using SQLand came accross a construct I plan to
use:-

CREATE VIEW xyz
AS
SELECT *
FROM abc
WHERE condition

This does'nt seem to work in Access? I could just make a table using a
similar query but then that Table would have to be manually updated by
running a query every time I start the database right? I just need to look at
a particular subset of records based on the condition above.

Is there any other way out?

-Rahul
 
A query is dynamic: if you make a change in what's displayed through the
query, the underlying table is updated.
 
This does'nt seem to work in Access? I could just make a table using a
similar query but then that Table would have to be manually updated by
running a query every time I start the database right?
Click to expand...

No, in fact any query you run, or in fact save in ms-access can be thought
as the same thing as a view.

All a view is for sql server is saved query that you run. It for all
purposes works on the live data.

The same concept applies to ms-access, and simply saving a query in the
query builder. No data is saved when you do this..and the query is operating
on the base live tables.

So, for all things...think of a saved select query in the ms-access query
builder as same thing as a view......as it is same concept...
 
It seems to work for me.

What version of Access are you using?
How are you running the SQL?
What do you mean by 'doesn't work?'


That syntax is not valid in A97/DAO 3.51

Queries created using that syntax are not visible in the A2000 database
view.

If you need to use Access 97, or if it is important that your queries are
visible in the A2000 database window, there are other ways to create
queries.

(david)
 
Hi,

Thank you all for your help; but I still havent figured it all out!
A query is dynamic: if you make a change in what's displayed through the
query, the underlying table is updated
Click to expand...

Doug, the query doesn't seem to be updating the dependant tables
automaticallyfor me! Perhaps I'm doing something wrong? But I figured out a
workaround -- I manually ran all the queries in series and it worked; but
thats not the most elegant way of doing things I guess! :-)

To answer Davids questions, I'm running Access2002 SP3. What I meant by
does'nt work was Access gave me a syntax error the moment I tried to save the
query. It seems it does NOT consider "CREATE VIEW" as a valid syntactic
construct.

The Idea I got while reading up on SQL was a view should be something that
updates itself automatically whenever any data in the underlying tables
changes; how do I achieve this effect in Excel?

Any more tips?

Thanks again!

-Rahul
 
You'll need to explain a little more what you're doing.

If you've got a query and open the query and update what's displayed, the
updates you make will be made in the table. Queries are simply "windows"
into tables: they don't have any existence of their own.
 
a syntax error the moment I tried to save the query.
It seems it does NOT consider "CREATE VIEW" as a valid
syntactic construct.
Click to expand...

You do not have the Access interface running in 'ANSI'
mode, so it is restricted to a more limited set of
DDL.

Since you are attempting to use the Access interface,
what is the point of using advanced DDL at all? Simply
save "SELECT abc.* FROM abc WHERE condition". When
you save that, Access will ask you what you wish to
name the view, and you can enter 'xyz' instead of the
default name 'Query1'

Or, if you wish to run the Access interface in 'ANSI' mode:

1) Make a back up copy of your database
2) Open your database
3) Go to the menu item Tools|Options,
4) Select the page Tables/Queries
5) Enable SQL Server Compatible Syntax

If you do not wish to use the Access GUI interface,
run (execute) the DDL against an ADO object (for example
CurrentProject.Connection).


(david)
 
Back
Top