March 3, 2021
Hot Topics:

Quick Start Guide to SQL Server 7 -- Part 3

  • By Karl Moore
  • Send Email »
  • More Articles »

Views are essentially the same as Microsoft Access queries.

You tell the database what information you want to retrieve, then give that query ('view') a name.

The data from this view can then be retrieved via a piece of code such as that in the 'Accessing from VB' section. Instead of doing something like this in Visual Basic code:

objRS.Open "Select * from tblCustomers", objConn

You simply alter the SQL Statement to the name of the view, like this:

objRS.Open "MyView", objConn

This means you're not simply storing all your SQL statements within your application, but rather in one central depository, your database.

Also, you can grant permission for certain users to access your view, but not the underlying table. That enables you to stop unauthorised users gaining access to privileged information, such as the salary field in an employee table.

To create a View:

  • View your Database in Enterprise Manager (as before)
  • Click 'Views'
    • The existing views are used by SQL Server to maintain your database. Do not remove them.
  • Right-click 'Views', then select 'New View'
    • Depending on which version of SQL Server you are using, the screen you see may vary.
  • Type your SQL statement in the box currently containing the skeletal "SELECT FROM"
    • Alternatively, you may use the Access-like tools on the same screen to build your view
    • To test your SQL statement, hit the exclamation button on the toolbar
  • Hit ALT-F4 to close the design view window
  • Respond to any prompts to change, saving with an appropriate name

To edit a View:

  • View your Database in Enterprise Manager (as before)
  • Click 'Views'
  • To re-enter the design view, right-click and select 'Design View'
    • Alternatively, double-click to edit the raw SQL statement, without all the visual frills
  • Press ALT-F4 when finished, responding to any prompts

To grant View access to a user:

  • Follow all the steps for granting Table access to a user (as before), but using your view as opposed to a table

Page 2 of 6

This article was originally published on November 20, 2002

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date