New book: Microsoft Access 2010 VBA Programming Inside Out

We’re pleased to announce that Microsoft Access 2010 VBA Programming Inside Out (ISBN 978-0-7356-5987-2; 736 pages) is available for purchase from Microsoft Press Store, from Amazon.com, and from Barnes & Noble.

This supremely organized reference is packed with hundreds of time-saving solutions, troubleshooting tips, and workarounds. It's all muscle and no fluff. Discover how the experts use VBA to exploit the power of Access—and challenge yourself to new levels of mastery!

• Enhance your application with VBA built-in functions and SQL code
• Use the Access Object Model to work with data in forms and reports
• Manipulate data using SQL, queries, and recordsets with Data Access Objects (DAO)
• Create classes for handling form and control events
• Connect your Access database to different sources of data
• Effectively plan how to upsize an existing Access database to Microsoft SQL Server
• Dynamically update Microsoft Excel® spreadsheets from the database
• Migrate your Access database directly to the cloud using SQL Azure

Please enjoy this excerpt from the book:

Chapter 16 - Using SQL Azure

Microsoft SQL Azure offers an opportunity for developers who are using Microsoft Office for either reporting management information or developing full applications linked to SQL Server to extend the reach of their applications by placing the Microsoft SQL Server database in the Cloud with SQL Azure. As a result, you can continue to use the existing desktop Office applications after they are relinked directly to SQL Azure. Furthermore, you can consider using Azure and .NET to provide a browser-based interface to your applications later.

There are numerous applications of this technology. One example is to provide remote users with an opportunity to test out an application or product without the need to install the SQL backend. Another example is to build a full, multi-tenanted product in the cloud. This technology also provides a cloud-based SQL Server that is synchronized to your on-premise applications, using the newest version of Data Sync.

In this chapter, we begin by showing you how to get started with SQL Azure. Next, we discuss the structural requirements for the SQL Server database and the migration process. We then move on to discuss the developer tools for working with SQL Azure and address the issue of planning and implementing security.

After reading this chapter, you will:

· Understand how to get started and develop with SQL Azure.

· Understand how to migrate an on-premise SQL Server database to SQL Azure.

· Be able to construct multi-tenanted solutions.

· Understand how to utilize the Data Sync Service.

· Plan and implement a security model in SQL Azure.

· Experience how Microsoft SQL Server Migration Assistant can migrate directly from Microsoft Access to SQL Azure.

Note

As you read through this chapter, we encourage you to also use the companion content sample script files, which can be downloaded from the book’s catalog page.

Introducing SQL Azure

It is possible to have your own SQL Server on the internet and connect to it directly from your desktop Microsoft Access application. If you have an ISP that provides this service, this is one option for extending the reach of an application. If you try to do this yourself on your own dedicated server, you might find that it’s quite challenging and realize that you need to read up about a wide range of technologies, such as configuring firewalls.

One advantage of SQL Azure is that you can have this functionality without the associated difficulty of configuring this for yourself and be up and running very quickly. SQL Azure is not SQL Server running on a Server on the internet; it is a service provided by Microsoft to give SQL Server functionality within the context of cloud computing.

Gradually, SQL Azure is supporting more of the functionality associated with an on-premise SQL Server, and the ease and simplicity of using Azure is continuously improving as Microsoft adds support for an ever increasing subset of SQL Server functionality. You will find that the basic database technology is now well established in SQL Azure. New, additional services include a Data Sync, which can synchronize on-premise databases to cloud databases, and support for Report Services.

Creating Databases

Once you have set up your SQL Azure account, you will be able to use the browser-based management interface for managing your account and databases, as is shown in Figure16-1.

Figure 16-1 Viewed in a browser, the summary page provides the details that you need for connecting to the databases on your server (https://sql.azure.com/ProjectViewer.aspx).

On the Databases tab, in the lower-right part of this screen, click the Create Database button to set up a new database called Northwind. Figure 16-2 shows the result of creating two databases in Azure (the master system database is part of the SQL Azure environment).

Figure 16-2 You can manage your SQL Azure databases on the Databases tab.

Click the Northwind database you just created and then click the Connection Strings button to display the corresponding connection strings. This displays the following connection string for use with ADO.NET:

Server=tcp:b0d.database.windows.net;Database=Northwind;User ID=AndrewCouch@b0d;Password=myPassword;Trusted_Connection=False;Encrypt=True;

The connection string for ODBC will be similar to the following:

Driver={SQL Server Native Client 10.0};Server=tcp:b0d.database.windows.net;Database=Northwind;Uid=AndrewCouch@b0d;Pwd=myPassword;Encrypt=yes;

In the ODBC connection string, the SQL Server Native Client 10.0 driver is used; earlier versions of the ODBC drivers will not work when connecting to SQL Azure. You will find details of how to download these drivers on the Microsoft sites. If you have performed a local installation of SQL Server 2008 R2, the drivers were automatically installed at that time. If you are planning to allow other users to work with the database, then you will need to download and install these drivers on each desktop machine.

Firewall Settings

When working with SQL Azure, there are two basic levels of security: the first is a SQL Server Login (as of this writing, SQL Azure does not support Windows Authentication); the second level of security is the firewall, which will only allow access from a remote location with an IP address that falls within ranges defined in the firewall rules. Figure 16-3 shows an example of firewall rules.

Figure 16-3 Managing the firewall rules for the server.

In this example, we have opened up the firewall to allow access from any IP address (0.0.0.0 – 255.255.255.255). The danger in doing this is that you effectively lose the protection of the firewall and must rely solely on the security provided by the SQL Server Login and password. The advantage in setting a wide IP address range is that if you have a dynamic IP address, you will always be able to connect to the server. For example, if you have a database that is used for product demonstrations, then losing this additional security might be acceptable. If you have fixed IP addresses, then you would only enter the appropriate address ranges.

Using the Management Studio

To work from your local machine with the Microsoft SQL Server Management Studio, you will need to configure the SQL Server 2008 R2 Management Tools; you can download and install the FREE Express version of SQL Server 2008 R2 to ensure that you have the required tools.

After starting Management Studio, you are prompted to connect to a SQL Server, Figure 16-4 shows the details that you need to enter for your SQL Azure server. Notice that the Options button has been clicked to display the expanded set of tabs for the connection dialog.

Figure 16-4 Entering the details for connecting to the server.

Your server has a code; in our example the code is b0d, which means that the server name to be entered is b0d.database.windows.net. The authentication needs to be set for SQL Server Authentication, and then enter your user name as Username@b0d.

Before clicking the Connect button, click the Connection Properties tab, as shown in Figure 16-5.

Figure 16-5 Enter the name of the database to which to connect in the Connect To Database field.

Type in the name of the database to connect to (the drop-down menu will not be populated). In Figure 16-5, the selected database is Northwind, but you might decide to connect to Master. If you choose to connect to Master, you will also be able to see and manage all the other databases on the server, as shown in Figure 16-6.

Figure 16-6 Connecting to Master allows you to manage all the databases on the server.

INSIDE OUT SQL Azure and the graphical interface

When you are working with Management Studio and SQL Azure, you might notice that not all of the graphical support is available. For example, typically, when you expand a database in the Object Explorer and locate the Tables folder, if you right-click New Table, you will see a graphical interface for designing a table. With Azure, however, you will see a template script for creating a table that is displayed in the Query window. If you right-click an existing table, you will not have the option to use the graphical interface to change the design of the table, or view or edit the data (you need to type in the SQL in the query window to do this).

For many of the actions for which you have previously used the GUI interface, the only option with Management Studio is to write TSQL to execute these tasks. If you find that this is difficult to do, then you will want to look at using a new online graphical tool that Microsoft is developing, which as of this writing is called Houston (This is described in the following section.

Developing with the Browser Interface

SQL Azure is an evolving technology and Microsoft has been adding an increasing number of features to the services. Of particular note is the way in which Management Studio has been able to offer an ever increasing amount of support for the management and design tasks required to develop SQL Azure databases.

A separate area for which Microsoft has provided an additional tool is in the browser-based management of the database structure. As of this writing, you can go to https://www.sqlazurelabs.com/houston.aspx to launch this new feature. After selecting your data center, enter the credentials, as shown in Figure 16-7.

Figure 16-7 The Houston Database Manager window.

Figure 16-8 presents an example of the browser interface being used to change the design of a table.

Figure 16-8 Houston offers an interface that complements those features normally found in Management Studio when working with an on-premise database that are not available when connected to SQL Azure.