|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Views in SQL; whats in Access?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. -- Show quoteDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Rahul" <Ra***@discussions.microsoft.com> wrote in message news:E5CF750A-6902-45FE-A919-8D7159BCE936@microsoft.com... > 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 > This does'nt seem to work in Access? I could just make a table using a No, in fact any query you run, or in fact save in ms-access can be thought > similar query but then that Table would have to be manually updated by > running a query every time I start the database right? 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... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com http://www.members.shaw.ca/AlbertKallal 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) Show quote "Rahul" <Ra***@discussions.microsoft.com> wrote in message news:E5CF750A-6902-45FE-A919-8D7159BCE936@microsoft.com... > 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 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 Doug, the query doesn't seem to be updating the dependant tables >query, the underlying table is updated 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 Show quote "david epsom dot com dot au" wrote: > 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) > > "Rahul" <Ra***@discussions.microsoft.com> wrote in message > news:E5CF750A-6902-45FE-A919-8D7159BCE936@microsoft.com... > > 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 > > > 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. -- Show quoteDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Rahul" <Ra***@discussions.microsoft.com> wrote in message news:D6715EC8-68C9-4225-A258-D4E30B01987D@microsoft.com... > 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 > > 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 > > "david epsom dot com dot au" wrote: > >> 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) >> >> "Rahul" <Ra***@discussions.microsoft.com> wrote in message >> news:E5CF750A-6902-45FE-A919-8D7159BCE936@microsoft.com... >> > 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 syntax error the moment I tried to save the query. You do not have the Access interface running in 'ANSI'> It seems it does NOT consider "CREATE VIEW" as a valid > syntactic construct. 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) |
|||||||||||||||||||||||
Other interesting topics