Skip Navigation LinksHome Page > SQL Azure Information Center > SQL Azure How To's

How To's for Access, SQL Azure, and SQL Server



How to Create Logins in SQL Azure

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:

CREATE LOGIN MyLoginName WITH password = 'zX/w3-q7jU'
GO

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:

'Example usage: Call CreateLogin("MyLoginName", "zX/w3-q7jU")

Function CreateLogin(strLoginName As String, strPW As StringAs Boolean
On Error GoTo ErrHandle

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String

    strSQL = "CREATE LOGIN " & strLoginName & " WITH password = '" & strPW & "'"

    Set db = CurrentDb

    'Create the Logins
    Set qdf = db.CreateQueryDef("")
    qdf.Connect = GetConnMaster 'Function to get Connection string to master database in SQL Azure
    qdf.SQL = strSQL
    qdf.ReturnsRecords = False
    qdf.Execute dbFailOnError

    'If no errors the Login was Created
    CreateLogin = True

ExitHere:
    'Cleanup for security and to release memory
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Exit Function

ErrHandle:
    CreateLogin = False
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description _
    & vbCrLf & "In procedure CreateLogin"
    Resume ExitHere

End Function



How to Create Database Users in SQL Azure

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.

T-SQL:

CREATE USER MyLoginName FOR LOGIN MyLoginName

Or:

CREATE USER MyLoginName FROM LOGIN MyLoginName

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:

'Example usage: Call CreateDBUser("MyLoginName")

Function CreateDBUser(strLoginName As StringAs Boolean
On Error GoTo ErrHandle

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String

    strSQL = "CREATE USER " & strLoginName & " FOR LOGIN " & strLoginName

    'Create the Database User
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("")
    qdf.Connect = GetConnDB 'Function GetConnDB gets Connection string to the database
    qdf.SQL = strSQL
    qdf.ReturnsRecords = False
    qdf.Execute dbFailOnError

    'If no errors the Database User was Created
    CreateDBUser = True

ExitHere:
    'Cleanup for security and to release memory
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Exit Function

ErrHandle:
    CreateDBUser = False
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description _
    & vbCrLf & "In procedure CreateDBUser"
    Resume ExitHere

End Function



How to Build an ODBC Connection String in Access

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:



Public Function obfuscatedFunctionName() As String
    obfuscatedFunctionName = "ODBC;" _
        & "Driver={SQL Server Native Client 10.0};" _
        & "Server=tcp:MyServerName.database.windows.net,1433;" _
        & "Database=MySQLAzureDatabaseName;" _
        & "Uid=MyUserName@MyServerName;" _
        & "Pwd=MyPassword;" _
        & "Encrypt=Yes"
End Function


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).




How to Make Linked Tables DSN-Less

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.



Function SetDSNLessTablesNViews() As Boolean

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strConnection As String

    SetDSNLessTablesNViews = False 'Default Value

    Set db = CurrentDb

    'I use a Function to get the Connection string
    'Note: In actual use I never use "Connection" in my Variables or Procedure names.
    'I disguise them to make it hard for a hacker to use code to get my Connection string
    strConnection = GetCnnString()

    'Loop through the TableDefs Collection
    For Each tdf In db.TableDefs
        'Verify the table is an ODBC linked table
        If Left$(tdf.Connect, 5) = "ODBC;" Then
            'Skip System tables
            If Left$(tdf.Name, 1) <> "~" Then
                Set tdf = db.TableDefs(tdf.Name)
                tdf.Connect = strConnection
                tdf.Attributes = dbAttachSavePWD 'dbAttachSavePWD = 131072
                tdf.RefreshLink
            End If
        End If
    Next tdf

    SetDSNLessTablesNViews = True

    Set tdf = Nothing
    Set db = Nothing

End Function

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.




How to Make Your Pass-through Queries DSN-Less

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.



Function ODBCQueriesMakePassThru() As Boolean

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strQueryName As String
    Dim strSQL As String
    Dim strMsg As String
    Dim blnSelectQuery As Boolean

    ODBCQueriesMakePassThru = False 'Default Value

    Set db = CurrentDb

    'Loop through the QueryDefs Collection
    For Each qdf In db.QueryDefs

        'Verify the query is an ODBC Pass-through query
        If Left$(qdf.Connect, 5) = "ODBC;" Then
            strQueryName = qdf.Name

            'Skip System queries
            If Left$(strQueryName, 1) <> "~" Then
                Debug.Print strQueryName
                Set qdf = db.QueryDefs(strQueryName)

               'Get the Query's SQL
                strSQL = qdf.SQL

                'Set the Connection String using a Function
                'that returns the DSN-Less Connection String
                qdf.Connect = GetCnnString()

                'Set whether Returns Records is True or False by
                'checking the Querydef SQL for Action Query words:
                'INSERT, UPDATE, and DELETE.
                'This must be set correctly or the query will fail

                'If one of the Action Query words start the SQL it is an Action Query
                If Left$(strSQL, 7) = "INSERT " Then
                    blnSelectQuery = False
                ElseIf Left$(strSQL, 7) = "UPDATE " Then
                    blnSelectQuery = False
                ElseIf Left$(strSQL, 7) = "DELETE " Then
                    blnSelectQuery = False

                'If one of the words is separate from other
                'words in the SQL it is almost surely an Action Query
                ElseIf InStr(1, strSQL, " INSERT ") > 0 Then
                    blnSelectQuery = False
                ElseIf InStr(1, strSQL, " UPDATE ") > 0 Then
                    blnSelectQuery = False
                ElseIf InStr(1, strSQL, " DELETE ") > 0 Then
                    blnSelectQuery = False

                'Not absolutely sure if the next few ElseIfs are
                'Action queries. A Pass-through query could be written
                'like a Stored Procedure or a column name could
                'contain one of the Action Query words within it.

                'Leave the ReturnsRecords setting alone but advise the user to check.
                strMsg = "Check Query " & strQueryName & " and set the query's" _
                    & vbCrLf & Space(12) & "ReturnsRecords Property to True" _
                    & vbCrLf & Space(17) & "if it is not an Action Query."

                ElseIf InStr(1, strSQL, "INSERT ") > 0 Then
                    'Unknown - keep the existing setting
                    blnSelectQuery = qdf.ReturnsRecords
                    MsgBox strMsg, vbExclamation
                    Debug.Print strMsg
                ElseIf InStr(1, strSQL, "UPDATE ") > 0 Then
                    'Unknown - keep the existing setting
                    blnSelectQuery = qdf.ReturnsRecords
                    MsgBox strMsg, vbExclamation
                    Debug.Print strMsg
                ElseIf InStr(1, strSQL, "DELETE ") > 0 Then
                    'Unknown - keep the existing setting
                    blnSelectQuery = qdf.ReturnsRecords
                    MsgBox strMsg, vbExclamation
                    Debug.Print strMsg
                Else
                    'Not an Action Query
                    blnSelectQuery = True
                    Debug.Print vbTab & "Not Action Query"
                End If

                'Set the Query's ReturnRecords Property
                qdf.ReturnsRecords = blnSelectQuery

                If blnSelectQuery = False Then
                    Debug.Print vbTab & "Action Query"
                End If
                'Done. No need to append or save the Query
            End If
        End If
    Next qdf

    ODBCQueriesMakePassThru = True 'All queries were sucessfully set

    Set qdf = Nothing
    Set db = Nothing

End Function





How to Migrate a SQL Server Database to SQL Azure by Generating Scripts

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.



Login Status: Not logged in.
Login      Free Registration

  Call Us for a Free Consultation

Phone:

Email:

Share

Site Navigation

Skip Navigation Links.