Create Logins Using SQL Server Management Studio (SSMS): Logins must be created in the master database. To create a Login in SSMS I normally right click on the server and select "New Query" which opens a blank query in the master database. It must be run from the master database because you cannot use "USE master" with SQL Azure.
T-SQL:
To be secure Passwords must follow the required Password Policy. It is best to use Strong Passwords. They must be at least 8 characters long and contain at least one number or special character such as -/~^&. Since ODBC connection strings utilize the characters []{}(),;?*!@ they should not be used in Passwords.
Create Logins Using Microsoft Access: You can create Logins with Microsoft Access with VBA using an unnamed temporary Pass-through QueryDef, which is a technique recommended in the Microsoft Access Developer References. You can use the following sample code, passing the Login Name and the Password to the Function:
Create Database Users With SQL Server Management Studio (SSMS): Database users must be created in the database in which they will exist and usually the Login Name is used as the Database User Name.
Or:
Create Database Users With Microsoft Access: You can create Database Users with Microsoft Access using the following sample code, passing the Login Name to the Function:
I use an obfuscated Function Name to return the connection string because the names of Constants and Variables in accde and mde databases can be read using some types of software. To connect to SQL Azure the Function needs to return a connection string like this:
You can get your connection string from the SQL Azure Portal and use it to replace the My... words in the code. You may need two similar Functions. One for the master database and one for your other database(s).
When using applications where you feel it is safe to use SQL Azure or SQL Server linked tables you can make your linked tables and views DSN-Less by using the TableDef RefreshLink Method shown below. You can run this code when your Access database first opens to keep the tables and views connected.
I decided to use the TableDef RefreshLink Method after I read that you could delete your TableDefs and not be able to append a new one if there is an error in the Connection string at this page on Doug Steele's website at the bottom of the page.
You can read about the TableDef RefreshLink Method in the Access 2007/2010 Developer's Reference and the Microsoft Access 2003 Language Reference help (In the VBE select RefreshLink and press F1). While I could find no documentation for setting the TableDef Attributes Property using the RefreshLink Method but there is documentation for setting the TableDef Attributes using the Database CreateQueryDef Method.
To learn how to create SQL Azure linked tables and views using Microsoft Access see the walkthrough, Connecting Microsoft Access to SQL Azure, by MVP Dhananjay Kumar or see Microsoft Access and Cloud Computing with SQL Azure Databases (Linking to SQL Server Tables in the Cloud) by Luke Chung.
After creating Pass-through queries using a DSN you can make then all DSN-Less by setting the QueryDef Properties using the code below. The code automatically sets the ReturnsRecords Property based on the SQL in your queries unless a query does not begin with Insert, Update, or Delete and it uses one of the words as part of the end of field or column name for example: LastInsert, RecordsToUpdate, or ItemsToDelete etc., in which case it alerts you to verify the existing ReturnsRecords Property is correct.
Generate a Database Script for SQL Azure from the Journey to SQL Authority with Pinal Dave blog. A very helpful article that shows how to build a SQL Azure database by generating scripts from an existing SQL Server database.
In smaller databases it may be practical to also Migrate the Data by Generating Scripts using SSMS.
First, migrate the schema. I recommend that you first follow the example in the article and migrate the schema first. That will give you the opportunity to make any changes that might be needed. As shown in the article you click on the "Advanced" button, followed by clicking on "Script for the database engine type", and then selecting "SQL Azure Database". Then click on "Types of data to script" and select "Schema only".
Then, migrate the data. As before you click on the "Advanced" button and "Script for the database engine type" and then select "SQL Azure Database". But this time select "Data only" for the "Types of data to script". This may produce a large script depending upon the amount of data stored in the database.
Email:
Phone:
Tweet