Microsoft Access VBA Programming for the Absolute Beginner

The ADP file can be opened like any other Access file. However, notice the title bar on the top of the Database window, as shown in Figure 19-2. It reflects the fact that Access is now linked to a SQL Server database.

Figure 19-2: The Database window in the ADP file

Access uses this ADP file to serve as the front-end GUI for SQL Server. At first, the differences may not seem like much, but there are many. As an example, if you click on the Queries category under Objects, you will notice several operations you have not seen in a standard MDB file. This is shown in Figure 19-3.

Figure 19-3: The Queries category

Stored Procedures

As an example, you can create stored procedures. A stored procedure is precompiled SQL code that is stored within the SQL Server database. Doing it this way makes the query considerably faster and more secure.

If you select the Create Stored Procedure in Designer option, you are presented with a screen similar to the query builder screen in an MDB file. This is shown in Figure 19-4. You select tables as you would in building a query. You even drag a field from one table to another in order to create a link. The only difference is that you can either check the boxes or drag them to a vertical column to select them. You can even set sort order and criteria as you would in a query.

Figure 19-4: The stored procedure designer

Unlike queries, a stored procedure cannot be run unless it is saved first. To do this, just click on the Save icon and enter the name of the stored procedure in the Save As dialog box, as shown in Figure 19-5. Once you do that, you can run it as you would a query by simply clicking on the Exclamation icon.

Figure 19-5: Saving a stored procedure

Using buttons on the toolbar, you can turn the three sections of the designer window on and off. The three sections are

• The diagram that shows the tables

• The grid that shows the design grid

• The SQL window, which shows the SQL code

Views

A view is a way to access your data. It allows you to create a table containing a subset of your data on-the-fly. You would create a view from the queries window as you would a stored procedure. However, there is one additional step in order to make the data editable. You need to select View | Properties (see Figure 19-6).

Figure 19-6: The View Properties dialog box

So what does this mean to you? Let’s look at actual SQL code from a recent project I did:

SELECT store.storename, store.address, store.city, store.state, store.zipcode, store.manager, store.storeNumber, sales.storesales, sales.Itemssold FROM store INNER JOIN sales ON store.storeNumber = sales.storeID

By saving this code as a view, the only front-end SQL that would be needed is

SELECT vw_sales.* FROM Vw_Sales

In addition to being more efficient, it hides (programmers use the word “encapsulates”) the actual SQL. This lends itself to greater efficiency.

You can set a permission to update the data by selecting the Update Using View Rules option.