Linking to SQL Azure - MS Access

Before reading this section, you might want to read Chapter , “Using SQL Azure,” for a detailed explanation of how to create a SQL Azure database. This section assumes that you have created SQL Azure databases and that you need to understand more about how to link to an Azure database and change your links between a development and test or production system.

SQL Azure DSN

When using SQL Azure, you don't want to use your main Azure account information in the application, so the first step is to use Management Studio to create a test account. The companion content includes two SQL Azure databases, Northwind and Northwind- Testing, as shown in Figure. If you select the Logins folder and then press the New Query button on the toolbar, you can create a new query window for executing T-SQL in the Master database.

The SQL Azure server showing the Northwind and NorthwindTesting databases.

The SQL Azure server showing the Northwind and NorthwindTesting databases.

You can then execute the following Script in Master to create a new SQL Server login account:

For each of the test databases, you register the login by creating a user account and assigning the user a database role (normally, you would create an appropriate role, but to get started you will assign the user the db_owner role). The easiest method for doing this is to click the Database in Object Explorer, and then click New Query and execute the following script (verify that you are executing this in the test database and not in Master):

SQL Azure supports security stored procedures

SQL Azure supports the stored procedures sp_addrolemember, sp_droprolemember, and sp_helprole for managing security. When you create new user accounts, these accounts will not be able to directly connect to the Master database. This means that when you set up DSN connections by using the ODBC Manager, you need to use your primary login account.

At this point, it is useful to close and re-open Management Studio by connecting with the logon details for the newly created user account (notice that the new account can connect to Northwind and NorthwindTesting, but not to Master).

You can then follow a similar set of steps to those described in the SQL Server section of this chapter, using the sample database NorthwindAzure.accdb. In this section, we will only show the important steps. Go to External Data | Import & Link | ODBC Database to create a new file DSN. In the Create A New Data Source dialog box, select the ODBC Driver shown in Figure.

Select the latest ODBC drivers for use with SQL Azure.

Select the latest ODBC drivers for use with SQL Azure.

When entering the server name, enter your Azure server details in the format shown in the following line of code and in Figure :

ServerName.database.windows.net

Enter your server name for SQL Azure.

Enter your server name for SQL Azure.

When entering the SQL Server Login, use the format shown in the following line of code and in Figure:

MainAccountName@ServerName

Use your main account when creating the DSN connection.

Use your main account when creating the DSN connection.

After completing these details, follow through the subsequent screens to enter the appropriate database name (Northwind or NorthwindTesting), and then test the connection.

Connecting to SQL Azure

When making a connection to SQL Azure, you have the option to save password information in the links, if you do not select the option, then when you open the first table, you will be prompted to enter the SQL Azure login name and password.

However, it is very simple to make this initial connection in code and supply a user name and password, and even when the table links do not contain this information, this will no longer involve any prompting, because the connection will already be established. Go to External Data | Import & Link | ODBC Database and link to SQL Azure using the DSN. You are prompted to log in, as shown in Figure.

Login with the test account when using the DSN.

Login with the test account when using the DSN.

Then, link to the tables, but do not save the password in the links, the TableDefs will then have the following Connect property:

Making a connection to SQL Azure for a user

With a few lines of VBA code, you can dynamically make the initial link to SQL Azure, after which, when opening any tables, there will be no prompting for login details. To use this method when the application starts, you can use your own form to capture the user’s login name and password; this way you can also integrate to any existing security and map existing login details to the appropriate SQL Azure login details.

In SQL Azure you can use the SUSER_SNAME() function, which will return the SQL Server Login user name. This can be very useful for identifying which user made changes to data, or in providing tables that only show specific rows of data for a specific user.

To modify the connection string you need to insert;UID=TestUser@b0d;PWD=TestAzure Account444333222; before the ;DATABASE= part of the connection string. The following code shows an example of adding the user’s credentials to the connection string and making the link:

The relinker incorporates a few minor modifications to allow it to relink to a SQL Azure database. The procedure for using the relinker is identical to that described in the earlier sections of this chapter. The changes required to refresh these links involve ensuring that you have logged into SQL Azure, as described in this section. The procedure modLinks_ Tables contains the following additional check (a similar test has been added to modLinks_ ODBC to log in to the new target server when changing the links):


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

MS Access Topics