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.
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.
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.
When entering the SQL Server Login, use the format shown in the following line of code and in Figure:
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.
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):
MS Access Related Tutorials
|Oracle 8i Tutorial||VBA For Excel Tutorial|
|Management Information systems Tutorial||Microsoft Excel Tutorial|
|Microsoft Word Tutorial||Advanced Excel Charts Tutorial|
|Advanced Excel Functions Tutorial||Excel Dashboards Tutorial|
|Excel Data Analysis Tutorial||Excel pivot tables Tutorial|
|Excel Power View Tutorial|
MS Access Related Interview Questions
|Oracle 8i Interview Questions||VBA For Excel Interview Questions|
|Management Information systems Interview Questions||Microsoft Excel Interview Questions|
|Microsoft Word Interview Questions||Microsoft FrontPage Interview Questions|
|Excel Data Analysis Interview Questions||Excel pivot tables Interview Questions|
|Excel Power View Interview Questions||Access Control List Interview Questions|
Ms Access Tutorial
Using The Vba Editor And Debugging Code
Understanding The Vba Language Structure
Understanding The Vba Language Features
Applying The Access Object Model
Understanding The Data Access Object Model
Using Forms And Events
Using Form Controls And Events
Creating Reports And Events
Adding Functionality With Classes
Using Classes And Events
Using Classes And Forms
Linking Access Tables
Integrating Microsoft Office
Using Sql Server
Upsizing Access To Sql Server
Using Sql Azure
Using Ado And Adox
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.