New Competition for Access Web Databases: InfoPath 2010 Forms and SharePoint 2010 Online

Si Dunn (@grumblecore) posted a comprehensive review of Using Microsoft InfoPath 2010 with Microsoft SharePoint 2010: Step by Step on 12/14/2011:

Using Microsoft InfoPath 2010 with Microsoft SharePoint 2010: Step by Step
By Darvish Shadravan and Laura Rogers
(Microsoft Press, paperback, list price $34.99; Kindle edition, list price $31.99)

A 21st century Shakespeare might write: “All the world’s a form, and we are just filling it in.”

One of this book’s authors contends (not completely in jest) that “forms run the world. Imagine modern life without forms, both paper and digital–it’s not possible! Everything that is known and recorded about you, from your birth city to your magazine subscriptions, to your preference of aisle or window seats–yes, all of this information was entered in a form at some point in time.”

Microsoft InfoPath 2010 is used to design and build electronic forms, as well as gather data, without writing code. Meanwhile, SharePoint Server 2010 “offers a robust architecture for managing access to data connections and external systems.” SharePoint is Microsoft’s suite of software tools aimed at making it “easier for people work together,” whether in the same office or scattered around the planet.

This well-written and nicely illustrated book shows how to bring the two products together in powerful ways that (1) enable InfoPath forms to be created and formatted and (2) integrate data from SharePoint and other company systems. InfoPath forms also can be hosted on SharePoint.

The book is aimed at “any information worker that needs to build and use electronic forms that will be stored in SharePoint.” Its goal is to “teach you the basics of building and using InfoPath 2010 forms in a SharePoint 2010 environment.”

The writers assume you are at least a “savvy Office and Windows user.” It is helpful, but not mandatory, to also have at least some basic familiarity with SharePoint Server 2010. “However, even if you’re not a SharePoint guru, most topics in this book should be within your grasp,” they point out.

If you do not have a SharePoint environment in your company, “InfoPath 2010 supports the creation of forms in Microsoft Office 365,” the two authors note. Office 365 is Microsoft’s cloud product that provides online access to a variety of programs for communicating and collaborating. [Emphasis added.]

InfoPath has been around for a few years and recently was given a significant update. But many businesses and computer users do not have it.

That’s not show-stopper when InfoPath and SharePoint work together, the authors point out. ”If you create your forms as browser-enabled form templates, users who don’t have InfoPath installed on their computer can still work with the form in a browser. This lets you share business forms with a variety of users, including employees, customers, and vendors.”

The 446-page book has 14 chapters. The first four chapters show how to create and format forms using InfoPath. The remaining chapters focus on using InfoPath with SharePoint.

According to the two authors, “the mission of this book is to help you understand how to create business forms that provide a pleasant, reliable, and intuitive experience for your users and customers,” they write.

The process of creating, formatting and publishing forms is shown and described in clear, succinct how-to steps. Practice files can be downloaded from a Microsoft site, and the exercise topics range from the basics of form design to building an approval process and working with SharePoint views and dashboards, to (1) “control what fields are displayed at any given time” and (2) “generate reports from any information in SharePoint lists and libraries.”

The authors add: “SharePoint libraries, specifically form libraries, are well suited for storing and managing InfoPath forms.”

InfoPath’s native language is XML, “perhaps the single most powerful method of storing and sharing structured data to come along since the advent of digital computing.” Creating electronic forms has long been a code-intensive process.

InfoPath hides most of the XML behind an easy-to-use interface. And XSLT (Extensible Style Sheet Language) style sheets also “‘sit in front of’ the underlying XML and transform it into the rich and easy-to-use forms that InfoPath can create.”

The book’s illustrations, short paragraphs, step-by-step lists and example files can all help readers get up to speed quickly, whether Microsoft InfoPath 2010 is used with Microsoft SharePoint on a company network or via the cloud, by way of Office 365.

– Si Dunn‘s latest book is a novel, Erwin’s Law. His other published works include Jump, a novella, and a book of poetry and several short stories, all available on Kindle. He previously worked in the telecommunications industry as a software and hardware tester and technical writer.

My Introducing Microsoft Office InfoPath 2003 book for Microsoft Press is still generating a few royalty checks.

---

Technorati Tags: Microsoft Access 2010,Access 2010,Access Web Databases,SharePoint 2010,SharePoint 2010 Server,SharePoint Online,Office 365,InfoPath 2010,InfoPath

SQL Server Integration Services 2012 Finally Implements a Pivot UI to Emulate Access Crosstab Queries

Microsoft’s Matt Masson (@mattmasson) described The Pivot Transform – Now with UI! in an 11/17/2011 post:

One of the post-CTP3 changes for SSIS in SQL Server 2012 is the addition of a user interface for the Pivot transform. This post walks through the new UI, which can be found in the new RC0 preview release.

Sample Data

For this example, we’ll be pulling data from the AdventureWorks sample database. We’ll be grabbing the total sales of all products in the Accessories category (ProductCategoryID = 4), grouped by year.

The data will look something like this:

We want the end results to be pivoted to look like this (Total product sales by year):

Setting up the Source

Add an OLE DB Source transform, and add a connection manager for the AdventureWorks sample database. Paste in the query to retrieve the total product sales by year:

Add a Pivot Transform from the SSIS Toolbox (found under the Other Transforms section by default).

Connect the Source to the Pivot transform. Double click the Pivot transform to open its editor.

The Pivot UI shows a sample pivot table at the top of the form, where you configure the Pivot Key (the column to use for values across the “top” of the table), the Set Key (the column to use for values down the “left” of the table), and the Pivot Value (the column to use for the values in the middle). The bottom of the UI is where you configure the pivot key values.

After mapping my columns to the appropriate keys, the UI looks like this:

• Pivot Key –> Year
• Set Key –> Product Name
• Pivot Value –> Total

The Pivot transform requires you to enter all of the possible Pivot Key values (so it can create output columns for each one). You can manually enter each key here (in our case, we’d have a value for each year that appears in our data set – 2002, 2003, and 2004). Alternatively, we can click the “Ignore un-matched Pivot Key values” checkbox and run the package as is. The pivot transform will output a log message containing all of the key values that we can then copy and paste into the UI.

1. Check the “Ignore un-matched Pivot Key values and report them after DataFlow” execution box
2. Click OK to save the changes to the UI
3. Run the package in the designer
4. When the package succeeds, click on the Progress tab
5. Look for an information log message from the Pivot transform which contains the keys
6. Right click the message and select Copy Message Text
7. Click Stop to end the execution
8. Double click the Pivot transform
9. Uncheck the Ignore un-matched Pivot Key values checkbox
10. Paste the Pivot Key values into the bottom text box
11. Trim the text so that it only contains the key values – “[2002],[2003],[2004]”
12. Click the Generate Columns Now button

The Pivot UI should now look like this:

Click OK to save the changes. We’ll add a Row Count transform to the data flow, and connect it to the Pivot’s output. Add a data viewer on the path so we can see the end results.

Run the package, and we can see the pivoted results.

Access’s Crosstab Wizard is still faster and easier to understand.

Technorati Tags: SQL Server Integration Services 2012,SQL Server 2012,Microsoft Access,Microsoft Access 2010,Access Crosstab Wizard,Crosstab Queries,PIVOT Transform

Steven Thomas Reported Full-featured Access Services Solution at http://www.accesshosting.com on 11/14/2011

Our friends at AccessHosting.com are offering free trials of two Access "cloud" solutions. Curious? Here's the deal, from the source.

Offer text provided by AccessHosting.com:

Reporting services for Access Web Databases in SharePoint 2010 now available - Office 365 users looking web based reporting functionality can republish their databases to accesshosting.com and take advantage of a new introductory price for hosted Access 2010 web databases published to SharePoint 2010.

Remote Desktop Services for Access Applications - Looking to move your existing Access 2003, 2007 and 2010 applications into the cloud quickly and easily? Our Remote Desktop solution provides a simple, secure way to connect multiple external and internal users to your Access databases. You can also use this service to deliver legacy Access applications on the iPad and Android powered devices.

Both of these solutions are available as a no obligation 30-day trial for qualified customers. Visit www.accesshosting.com for complete details.

Read Steve’s original post here.

I’ve used AccessHosting’s services for several cloud-based projects. See my following posts:

• Upsizing the Northwind Web Database to an Updated SharePoint 2010 Server Hosted by AccessHosting.com (3/18/2011)
• Kay Eubank Reviews “Access 2010 In Depth” for the I Programmer Web Site (3/15/2011)
• Migrate Access 2000 or Later Databases to Public or Private Rollbase Clouds (7/17/2011)
• Access Web Databases on AccessHosting.com: Adding User Logins and Assigning Permissions (3/14/2011)
• A SharePoint 2010 Primer for Access 2010 Developers by Ben Clothier (8/1/2011)
• Access Web Databases on AccessHosting.com: What is OData and Why Should I Care? (3/16/2011)

Technorati Tags: Microsoft Access 2010,Microsoft Access,Access,Access Hosting,SharePoint 2010,SharePoint,SharePoint Lists,OData,Access Services,Access Web Databases

Creating SQLAzure Logins and User Accounts with ODBC and Access VBA

Patrick Wood (@patrickawood) explained How to Use Microsoft Access to Create Logins in a SQL Azure Database in an 8/2/2011 article for his Gaining Access site (missed when published):

In this article we will demonstrate how you can use a Pass-through query in Access with VBA code to create SQL Azure Logins. Microsoft Access can then use the Login and Password to gain access to SQL Azure Tables, Views, and Stored Procedures. We will create a Login using SQL in Access similar to the following Transact-SQL (T-SQL):

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

Thankfully, a user would never have to memorize that password! Because this Login and password would only be used by my Access application the user never sees it and does not even know it exists.

There are several steps involved in creating a Login and Password for SQL Azure. And although most T-SQL that is used in SQL Azure is exactly the same as that used with SQL Server there are some critical differences which we will address in the following steps.

1) Create a Strong Password that Meets the Requirements of the Password Policy.

It is very important to use Strong Passwords because the extra security is needed since we cannot use Windows Authentication with SQL Azure. Passwords must be at least 8 characters long and contain at least one number or special character such as -/~^&.

2) Use Characters That Do Not Conflict With ODBC Connection Strings.

To avoid errors we should not use these ODBC connection string characters []{}(),;?*!@ in our Login Name and Password.

3) Build a Transact-SQL Statement Which Will Create the Login.

We will use the T-SQL CREATE LOGIN statement in a Pass-through query to create the Login. Since Pass-through queries "pass" the SQL unaltered to SQL Azure most of the time the SQL is just like what we would in SQL Server Management Studio (SSMS) and as seen here:

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

Another requirement of the CREATE LOGIN statement is that it must be the only statement in a SQL batch. So we are only going to create one Login at a time.

4) Ensure the Login and Password Are Created In the master Database.

This is required because "USE master" does not work in SQL Azure as it does with SQL Server because the USE statement is not supported in SQL Azure. But with Access we can create the Login in the master database by specifying the master database in our Connection String: "DATABASE=master;". We use a Function like the one below to get the Connection String with an obfuscated name to keep it more secure.

Public Function obfuscatedFunctionName() As String
obfuscatedFunctionName = "ODBC;" _
& "DRIVER={SQL Server Native Client 10.0};" _
& "SERVER=tcp:MyServerName.database.windows.net,1433;" _
& "UID=MyUserName@MyServerName;" _
& "PWD=MyPassword;" _
& "DATABASE=master;" _
& "Encrypt=Yes"
End Function

See my article Building Safer SQL Azure Cloud Applications with Microsoft Access for more information about securing your Access application.

5) Create a Function to Execute the SQL and Create the Login.

Place the ExecuteMasterDBSQL Function below in a Standard Module. This Function executes our CREATE LOGIN statement. It can be used any time you want to execute a T-SQL statement in the SQL Azure master database that does not return records. The Function returns True if the SQL was executed successfully or False if the SQL fails to be executed.

'This procedure executes Action Query SQL in the SQL Azure master database.
'Example usage: Call ExecuteMasterDBSQL(strSQL) or If ExecuteMasterDBSQL(strSQL) = False Then
'
Function ExecuteMasterDBSQL(strSQL As String) As Boolean
On Error GoTo ErrHandle
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
ExecuteMasterDBSQL = False 'Default Value
Set db = CurrentDb
'Create a temporary unnamed Pass-through QueryDef. This is a
'practice recommended in the Microsoft Developer Reference.
'The order of each line of code must not be changed or the code will fail.
Set qdf = db.CreateQueryDef("")
'Use a function to get the SQL Azure Connection string to the master database
qdf.Connect = obfuscatedFunctionName
'Set the QueryDef's SQL as the strSQL passed in to the procedure
qdf.SQL = strSQL
'ReturnsRecords must be set to False if the SQL does not return records
qdf.ReturnsRecords = False
'Execute the Pass-through query
qdf.Execute dbFailOnError
'If no errors were raised the query was successfully executed
ExecuteMasterDBSQL = True
ExitHere:
'Cleanup for security and to release memory
On Error Resume Next
Set qdf = Nothing
Set db = Nothing
Exit Function
ErrHandle:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description _
& vbCrLf & "In procedure ExecuteMasterDBSQL"
Resume ExitHere
End Function

6) Use a Form to Enter the Login Name and Password

We can make it easy for users to create a Login by using a form. To do this we need to add two text boxes and a command button to the form. Both text boxes need to be unbound. Name the text box for the Login Name txtLoginName. Name the text box for the Password txtPassword. Name the command button cmdCreateLogin. The form should look something like this, but without the extra touches for appearance sake.

Add the code below to the command button's Click event. After the code verifies that a Login Name and Password has been entered, it calls the ExecuteMasterDBSQL Function to create the Login in our SQL Azure master database.

Private Sub cmdCreateLogin_Click()
'Prepare a Variable to hold the SQL statement
Dim strSQL As String
'Build the SQL statement
strSQL = "CREATE LOGIN " & Me.txtLoginName & " WITH password = '" & Me.txtPassword & "'"
'Verify both a Login Name and a Password has been entered.
If Len(Me.txtLoginName & vbNullString) = 0 Then
'A Login Name has not been entered.
MsgBox "Please enter a value in the Login Name text box.", vbCritical
Else
'We have a Login Name, verify a Password has been entered.
If Len(Me.txtPassword & vbNullString) = 0 Then
'A Password has not been entered.
MsgBox "Please enter a value in the Password text box.", vbCritical
Else
'We have a Login Name and a Password.
'Create the Login by calling the ExecuteMasterDBSQL Function.
If ExecuteMasterDBSQL(strSQL) = False Then
MsgBox "The Login failed to be created.", vbCritical
Else
MsgBox "The Login was successfully created.", vbInformation
End If
End If
End If
End Sub

The code in the Form checks the return value of the ExecuteMasterDBSQL Function and informs us whether or not the Login was successfully created. Once we have created a Login we can create a Database User for the Login and grant the User access to the data in the SQL Azure Database. Creating a Database User for the Login appears to be a good subject for another article. [See article below.]

---

Patrick continued with How to Create SQL Azure Database Users With Microsoft Access VBA on 8/23/2011:

Why do we need a SQL Azure Database User Account? An Access Database cannot access SQL Azure Objects such as Tables, Views, or Stored Procedures without one. That is, unless you use your SQL Azure Administrator account which would be living very dangerously if [you] were distributing your database to others. So before your database can do anything with SQL Azure, a Database User Account must be created that it can use. Also permissions must be granted to use the necessary SQL Azure Tables, Views, and Stored Procedures. We are going to show you how you can use a pass-through query in Access to create SQL Azure Database Users using Access VBA.

Database users must be created in the database in which they will exist because the "USE" statement can only work for the current database in SQL Azure. So to create a Database User we must use a query that runs in the Database in which we want to create the User. And since it would be confusing, to me at least, to log in using "JoeDeveloper" and work as Database User "SamCodeSlinger" my normal practice is to create a Database User with the same name as a Login Name.

If we were using SQL Server Management Studio (SSMS) or the Windows Azure Management Portal we could create a Database User as shown with the following Transact-SQL (T-SQL):

CREATE USER MyLoginName FOR LOGIN MyLoginName

Or:

CREATE USER MyLoginName FROM LOGIN MyLoginName

But you can easily create Database Users with Microsoft Access using the following two procedures, passing the Login Name to the CreateSQLAzureDBUser Function:

'Example usage: Call CreateSQLAzureDBUser("MyLoginName")
Public Function CreateSQLAzureDBUser(strLoginName As String) As Boolean
On Error GoTo ErrHandle
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
CreateSQLAzureDBUser = False 'Default Value
strSQL = "CREATE USER " & strLoginName & " FOR LOGIN " & strLoginName
'Create the Database User
Set db = CurrentDb
Set qdf = db.CreateQueryDef("")
'Change obfuscatedFunctionName to the name of a Function
'that Returns your SQL Azure Database Connection String
qdf.Connect = obfuscatedFunctionName
qdf.SQL = strSQL
qdf.ReturnsRecords = False
qdf.Execute dbFailOnError
'If no errors the Database User was Created
CreateSQLAzureDBUser = True
ExitHere:
'Cleanup for security and to release memory
On Error Resume Next
Set qdf = Nothing
Set db = Nothing
Exit Function
ErrHandle:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description _
& vbCrLf & "In procedure CreateSQLAzureDBUser"
Resume ExitHere
End Function

The Database User must be created in the Database in which it is to be used and not in the master Database. You can do this by changing MySQLAzureDatabaseName in the obfuscatedFunctionName Function to the name of the database in which you want to create the users.

'It is best to change the name of this procedure for better security for your use.
'The strIn Argument value, "Wb_gR%/PD\-k&yZq~j>l", is used like a Password to keep
'unauthorized users from getting your Connection String. You should also change it
'to suit you before you use it in a distributed application.
Public Function obfuscatedFunctionName(strIn As String) As String
If strIn = "Wb_gR%/PD\-k&yZq~j>l" Then
obfuscatedFunctionName = "ODBC;" _
& "DRIVER={SQL Server Native Client 10.0};" _
& "SERVER=tcp:MyServerName.database.windows.net,1433;" _
& "UID=MyUserName@MyServerName;" _
& "PWD=MyPassword;" _
& "DATABASE=MySQLAzureDatabaseName;" _
& "Encrypt=Yes"
Else
obfuscatedFunctionName = vbNullString
End If
End Function

For better security you can keep the Login Name, Password, and User Name hidden in the code without exposing it to the Access user.

We now have the ability to create Database Users but we still need to Grant Permissions before our Access Database can use this User Account to access any data in SQL Azure. We plan to show how you can do that in the articles to come.

You can download the code used in this article from our Free Code Samples page.

---

Technorati Tags: Microsoft Access 2010,Microsoft Access,Access VBA,ODBC,SQL Azure,Windows Azure

Visual Studio LightSwitch as a Microsoft Access Upgrade Path

Jeffrey Palermo (@jeffreypalermo) described Visual Studio LightSwitch, an Upgrade Path for Microsoft Access in a 7/30/2011 post to the Headspring blog:

There are lots of business systems written in Microsoft Access. One of the most successful companies I know is Gladstone, Inc, makers of ShoWorks software. This software runs most of the county fairs in the U.S. From entries, to checks, to vendors, this piece of software does it all to help manage and run a fair and keep track of all the data. And it is written in Access.

Started on Access 97, I have watched this software grow through the various Access upgrades, and it tests the limits of the platform. It’s author, Mike Hnatt, is one of the premiere Access gurus, and Microsoft has previous invited him up to the Redmond campus to be a part of internal Software Design Reviews, or SDR’s. Mike knows the limits of access, but even with the vast array of other development options out there, nothing comes close to parity with the capabilities he relies on – until today.

This is my first LightSwitch application. I just installed the software, ran it, defined a table structure and a few screens. It’s really simple, and I see that i runs a desktop version of Silverlight. It feels like Access (I have done some of that programming earlier in my career) because you just define the tables and queries, and then ask for screens that work off the data. You can customize the screens to some degree, and you can write code behind the screens, just like you can write VBA behind Access screens. This is my first time looking at Lightswitch in a serious way since it was just released. I will be looking at it more because it belongs in our toolbelt at Headspring. There are plenty of clients who have Access and FoxPro systems. These systems have tremendously useful built-in functionality that is prohibitively expensive to duplicate in a custom way with raw WPF and C#, but Lightswitch provides a possible upgrade path that won’t break the bank.

In case you are wondering what it looks like to develop this, here it is.

Notice that there is a Solution Explorer, and you are in Visual Studio with a new project type. I was really pleased that I could write code easily.

I tried some ReSharper shortcuts, but they didn’t work. I guess we’ll have to wait for ReSharper to enable this project type. Here is my custom button that shows the message box.

I think LightSwitch as a lot of promise for legacy system rewrites, upgrades, and conversions. Because it’s 100% .Net, you can mix and match with web services, desktop, SQL Server, etc.

Jeffrey is COO of Headspring.

It’s nice to see a well-known and respected .NET developer give credit to Microsoft Access where it’s due.

---

Technorati Tags: Microsoft Access 2010,Access 2010,Visual Studio LightSwitch 2011,Visual Studio LightSwitch,Upsizing Access Databases,Access Web Databases

Office 365 Video: Updating an Access database using SharePoint Online

Chris from the Microsoft SharePoint Content Team posted Office 365 Video: Updating an Access database using SharePoint Online to the SharePoint for End Users blog on 8/2/2011:

Are you a Microsoft Access 2010 user? With Microsoft Office 365, you can use your Windows 7 smartphone to read and update a team database remotely.

Important: The services and features that this video shows depend on several factors, such as the Microsoft Office 365 plan purchase agreement, licenses that might be assigned to each user, and specific user permissions. If your experience is different from the experience shown in the video, see your site administrator.

Prerequisites: This scenario requires Microsoft Office 365 for enterprises with Enterprise Services enabled; Microsoft Access on a single client computer.

To learn more about Access and SharePoint, check out the topics below:

• Build an Access database to share on the Web
• Create a form by using the Form Wizard

As always, let us know what you think!

For my detailed Webcasts about upsizing Access *.accdb databases to Web Databases that run with the version of SharePoint Online that comes with the US$6.00/month version of Microsoft Office 365 online, read Learn How To Create Access Web Databases with Office 365’s SharePoint Online from my Latest Webcast of 5/27/2011.

Technorati Tags: Microsoft Access 2010,Access 2010,Access Web Databases,SharePoint Online,SharePoint 2010

Create Logins in a SQL Azure Database with Access

Patrick Wood described How to Use Microsoft Access to Create Logins in a SQL Azure Database in an 8/1/2011 post:

In this article we will demonstrate how you can use a Pass-through query in Access with VBA code to create SQL Azure Logins. Microsoft Access can then use the Login and Password to gain access to SQL Azure Tables, Views, and Stored Procedures. We will create a Login using SQL in Access similar to the following Transact-SQL (T-SQL):

CREATE LOGIN MyLoginName WITH password = ‘zX/w3-q7jU’

Thankfully, a user would never have to memorize that password! Because this Login and password would only be used by my Access application the user never sees it and does not even know it exists.

There are several steps involved in creating a Login and Password for SQL Azure. And although most T-SQL that is used in SQL Azure is exactly the same as that used with SQL Server there are some critical differences which we will address in the following steps.

1) Create a Strong Password that Meets the Requirements of the Password Policy.

It is very important to use Strong Passwords because the extra security is needed since we cannot use Windows Authentication with SQL Azure. Passwords must be at least 8 characters long and contain at least one number or special character such as -/~^&.

2) Use Characters That Do Not Conflict With ODBC Connection Strings.

To avoid errors we should not use these ODBC connection string characters []{}(),;?*!@ in our Login Name and Password.

3) Build a Transact-SQL Statement Which Will Create the Login.

We will use the T-SQL CREATE LOGIN statement in a Pass-through query to create the Login. Since Pass-through queries “pass” the SQL unaltered to SQL Azure most of the time the SQL is just like what we would in SQL Server Management Studio (SSMS) and as seen here:

CREATE LOGIN MyLoginName WITH password = ‘zX/w3-q7jU’

Another requirement of the CREATE LOGIN statement is that it must be the only statement in a SQL batch. So we are only going to create one Login at a time.

4) Ensure the Login and Password Are Created In the master Database.

This is required because “USE master” does not work in SQL Azure as it does with SQL Server because the USE statement is not supported in SQL Azure. But with Access we can create the Login in the master database by specifying the master database in our Connection String: “DATABASE=master;”. We use a Function like the one below to get the Connection String with an obfuscated name to keep it more secure.

Public Function obfuscatedFunctionName() As String
    obfuscatedFunctionName = "ODBC;" _
	    & "DRIVER={SQL Server Native Client 10.0};" _
	    & "SERVER=tcp:MyServerName.database.windows.net,1433;" _
	    & "UID=MyUserName@MyServerName;" _
	    & "PWD=MyPassword;" _
	    & "DATABASE=master;" _
	    & "Encrypt=Yes"
End Function

See my article Building Safer SQL Azure Cloud Applications with Microsoft Access for more information about securing your Access application.

5) Create a Function to Execute the SQL and Create the Login.

Place the ExecuteMasterDBSQL Function below in a Standard Module. This Function executes our CREATE LOGIN statement. It can be used any time you want to execute a T-SQL statement in the SQL Azure master database that does not return records. The Function returns True if the SQL was executed successfully or False if the SQL fails to be executed.

'This procedure executes Action Query SQL in the SQL Azure master database.
'Example usage: Call ExecuteMasterDBSQL(strSQL) or If ExecuteMasterDBSQL(strSQL) = False Then
'
Function ExecuteMasterDBSQL(strSQL As String) As Boolean
On Error GoTo ErrHandle

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

    ExecuteMasterDBSQL = False 'Default Value

    Set db = CurrentDb

    'Create a temporary unnamed Pass-through QueryDef. This is a
    'practice recommended in the Microsoft Developer Reference.
    'The order of each line of code must not be changed or the code will fail.
    Set qdf = db.CreateQueryDef("")
    'Use a function to get the SQL Azure Connection string to the master database
    qdf.Connect = obfuscatedFunctionName
    'Set the QueryDef's SQL as the strSQL passed in to the procedure
    qdf.SQL = strSQL
    'ReturnsRecords must be set to False if the SQL does not return records
    qdf.ReturnsRecords = False
    'Execute the Pass-through query
    qdf.Execute dbFailOnError

    'If no errors were raised the query was successfully executed
    ExecuteMasterDBSQL = True

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

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

End Function

6) Use a Form to Enter the Login Name and Password

We can make it easy for users to create a Login by using a form. To do this we need to add two text boxes and a command button to the form. Both text boxes need to be unbound. Name the text box for the Login Name txtLoginName. Name the text box for the Password txtPassword. Name the command button cmdCreateLogin. The form should look something like this, but without the extra touches for appearance sake.

Add the code below to the command button’s Click event. After the code verifies that a Login Name and Password has been entered, it calls the ExecuteMasterDBSQL Function to create the Login in our SQL Azure master database.

Private Sub cmdCreateLogin_Click()

    'Prepare a Variable to hold the SQL statement
    Dim strSQL As String

    'Build the SQL statement
    strSQL = "CREATE LOGIN " & Me.txtLoginName & " WITH password = '" & Me.txtPassword & "'"

    'Verify both a Login Name and a Password has been entered.
    If Len(Me.txtLoginName & vbNullString) = 0 Then
        'A Login Name has not been entered.
        MsgBox "Please enter a value in the Login Name text box.", vbCritical
    Else
        'We have a Login Name, verify a Password has been entered.
        If Len(Me.txtPassword & vbNullString) = 0 Then
        	'A Password has not been entered.
        	MsgBox "Please enter a value in the Password text box.", vbCritical
        Else
        	'We have a Login Name and a Password.
        	'Create the Login by calling the ExecuteMasterDBSQL Function.
	        If ExecuteMasterDBSQL(strSQL) = False Then
	    	    MsgBox "The Login failed to be created.", vbCritical
	        Else
	    	    MsgBox "The Login was successfully created.", vbInformation
	        End If
        End If
    End If
End Sub

The code in the Form checks the return value of the ExecuteMasterDBSQL Function and informs us whether or not the Login was successfully created. Once we have created a Login we can create a Database User for the Login and grant the User access to the data in the SQL Azure Database. Creating a Database User for the Login appears to be a good subject for another article.

Get the free Demonstration Application that shows how effectively Microsoft Access can use SQL Azure as a back end.

More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
Free Church Management Software with Contributions management.
Code Samples

Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.

Happy computing,
Patrick (Pat) Wood
Gaining Access
http://gainingaccess.net

It’s unfortunate that Access Web Databases don’t support VBA, which is necessary for Patrick’s approaches outline above.

---

Technorati Tags: Microsoft Access 2010,Access 2010,Access,SQL Azure

A SharePoint 2010 Primer for Access 2010 Developers by Ben Clothier

Ben Clothier answered SharePoint and Access: How do they fit together? in a 6/15/2011 post to the AccessExperts.net blog:

Hello Access with SQL Server blog readers! This is my first post since joining [IT Impact] and I look forward to many more! Both Juan and I will be blogging here on Access, SQL Server and SharePoint and we love reading your comments, so please leave us feedback below.

My specialty is Access and SharePoint, so what better way to get started than with a Series on both?

Access and SharePoint

If you’ve been working with Access for a while, it’s hard to miss the push Microsoft has been putting behind SharePoint integration with Access. The integration started as early as Access 2003, continued through 2007 and has blossomed with 2010. Without question the number one feature in the latest release is publishing your database to the web with SharePoint. In this first post I’m going to discuss what is SharePoint and why was it used to publish Access web databases, replace the Jet Replication and other integration features.

So what is SharePoint?

If you were to go over to Microsoft SharePoint page, you may see a long list of features and success stories described in nebulous terms. Indeed, there is no one simple definition to tack SharePoint to. Instead of discussing its capabilities, I believe we’re better off discussing the intention behind its capabilities, and I’m going to use Access as an analogy.

SharePoint is to Web Apps as Access is to Visual Studio

Why do we use Access instead of Visual Studio? Isn’t Visual Studio more powerful and lets us do all those cool things in code? Or better yet, why not just hire a professional to set up everything for us? The fact is, Access is accessible (excuse the pun) to *non*-programmers; people who need to track their data as part of their job and this is the crucial difference. To use Access, you’re not required to be a professional to get started, and because you can do it yourself. You don’t have to go and talk with your IT department who may be overworked with other, more important projects. Precisely because of this ability to create something on a worker’s desktop, Access is has become the most popular database in the world. Some of you may be thinking, “but there are complex Access applications that requires specialized consultants out there!” – Well, yes, but that usually comes later in the Access databases’ life and not all Access databases out there end the same way and to me that’s the beauty of Access – it enables companies to develop a line-of-business applications at far less risk, whether monetary or time & effort than if we opted to use Visual Studio.

SharePoint solves a different set of problems exactly the same way as Access does: it enables *non*-web-designers to build web pages quickly and without any specialized knowledge because it’s merely a part of their workflow and not their livelihood. Likewise, it enables workers to manage files without having to think about the organization of the files, backing up the files and how they would find the files again. SharePoint helps the company saves money by reducing the overall IT administration in contrast to traditional file sharing/networked hard drives, management of documents and so forth. Some people has suggested that SharePoint be thought of as a platform providing easy-to-use building blocks, and suddenly the analogy from Access is much more accurate; we get to build web applications or develop a certain document management strategy at far less risk and expenses than if we built one from scratch.

In fact, when you examine the difference between Jet Replication and the Offline mode with SharePoint, it’s impressive in how simple it is. All you have to do is just use SharePoint as your data source and everything else about working offline and synchronizing is automatic. No manual configuration. No coding or additional installation. This truly demonstrate the raison d’être behind Access and SharePoint; solving IT problems for common people. And that’s just one of many integration points between Access and SharePoint.

SharePoint is not a relational database

Access and SharePoint are very similar when we look at how they can solve business problems. Given the similarity in the audience both programs addresses, it seems conceivable that they’d be peas in pod, right? Well, here’s the other thing. Many professional Access consultants are actutely aware that SharePoint is anything but a true relational database system and they’re right. Even though SharePoint is powered by SQL Server, it introduces additional abstractions in the form of “lists” which are not similar to a SQL table and has some ramifications on how we can use lists in Access. That’ll be addressed in a future post.

SharePoint 2010 + Access 2010 = Instant Web Database!

The latest version of Access will allow you to publish your work to SharePoint 2010 with just one URL and a click. Gone are the days were you needed to upload files, configure security, configure the web server and countless other details. Granted, not all of the functionality of Access made it into this version of SharePoint’s Access Services, but we expect improved functionality in the years to come.

Sounds great! So why isn’t SharePoint used in small businesses?

Price is another point where SharePoint and Access diverge, SharePoint’s cost are much higher, both in terms of licensing fees and man hours required to support and maintain it. Microsoft has made some headway in this area by offering Office365 which essentially promises to brings SharePoint functionality to small businesses. Of course, there’s also AccessHosting which specializes in hosting Access web databases and we provide consultation for developmental work on web database that get hosted. This inevitable reality of moving services off the desktop and into “cloud” will also be examined in a future post.

My second post [see below] will discuss SharePoint lists in more detail.

For more details about SharePoint and Access integration, see links to my Webcasts in my Three Microsoft Access 2010 Webcasts Scheduled by Que Publishing for March, April and May 2011 post of 5/3/2011.

---

Ben Clothier continued his Access and SharePoint series with a SharePoint Lists and Microsoft Access in depth post of 7/7/2011 (missed when posted):

In a recent post, I discussed about how SharePoint and Access address similar audiences and provide easy solutions to different problems. Now we’ll examine the main object you’ll interact with SharePoint within Access: Lists. If you’re using the new web database with Access 2010, the “web tables” are in fact SharePoint Lists by a different name. Therefore, if you know something about them, you’ll also know something about web tables. There are some key differences between the two we will discuss later in the article.

As linked tables go, SharePoint Lists are definitely a horse of different color. I’ve said it before and I’ll say it again: SharePoint is anything but a true relational database. The lists totally reflect this. Therefore many techniques you may have picked up in optimizing your operations with linked tables may be inapplicable and we need to consider new ones.

Is it *gasp* de-normalized?

In a way, lists are denormalized. If you cared to peek inside the SQL Server database that SharePoint uses to store Lists, you would realize that all list items are stored in one giant sized table with columns names int1, int2, int3 and so forth. There’s another large table that describe the definition of the list and maps its columns to those wildcard columns. In a way, you could say SharePoint Lists are based on the Entity-Attribute-Value data model. After all, there is a reason why they’re called “Lists” and not “Tables”. Historically, they were just that – a simple list of values. In fact, up to SharePoint 2010, referential integrity wasn’t supported, a sore point that could very well have been the #1 blocking reason for adoption of SharePoint as a data source among Access developers. In prior versions you could relate a list to other lists in a loose way. The lists had no formal structure, since on average, they were created by non developers who uses SharePoint as a means to get the job done rather than developing comprehensive and robust solution for others. We’ll examine the implications this has for us when we use SharePoint Lists.

SharePoint Lists operates on ISAM model

Whenever we link to a ODBC data source, Access only needs to submit a SQL statement which is then executed on the server and sent back for Access’ consumption. However, when we query a SharePoint List for the first time, Access has to fetch all records and download them to a local cache in order to process the query which is then executed locally. In 2007, the local cache was stored in a XML format but with 2010, Access will use actual local Access tables (hidden from the UI) to work as a cache.

What SharePoint lists offers that linked tables don’t

This enables at least four things that we do not normally enjoy with ODBC linked tables:

1) Offline data access

A persistent connection to the backend is not required, the tables have more tolerance to disconnections & network interruptions than with a regular ODBC linked table.

2) Built-in replication

Even better, when users have a connection restored, they can sync their changes without any special configuration as was the case with old Jet Replication.

3) Design changes

With most linked tables, you probably had to shuttle between Access and the server’s management software (e.g. SQL Server Management Studio for SQL Server tables/views) if you wanted to make design changes. Even a simple thing such as adding a new field usually meant you had to go over there and issue the ALTER TABLE command, come back to Access and refresh the links to get that new field. With SharePoint Lists, whether as a web table or linked table, you get to make design changes without leaving Access.

4) Automatic Auditing & Versioning

SharePoint is designed from grounds up to provide auditing and data recovery. Whenever you delete a list item from the list, it’s not actually deleted but rather moved into a recycle bin which gives the user an opportunity to undo the accidental deletion. Furthermore, there’s second-tier Recycle Bin accessible to the administrators so data recovery is always possible. SharePoint also supports versioning of data out of the box and has tools to track all changes made to the data saved within the list. All of this great functionality is free; no configuration is required for recycle bin and you simply need to flick the switch for versioning.

Server-side filtering

There are no server-side operations other than fetching all the records and keeping the cache synchronized, which means your queries are always executed locally using Access database engine, unlike ODBC linked tables. If you want to have true server-side filtering consider using SharePoint Views which are analogous but not identical to SQL View. Unfortunately, there is no simple UI process to link to a SharePoint view; it has to be done in code:

DoCmd.TransferSharePointList _
    acLinkSharePointList, _
   "http:\\server\site", _
   "MyList", _
   "{7042e1d4-c929-4a7e-8d34-b5d93b9495fd}", _
   "tblMyList", _
   True

You may be wondering how we came up with GUID “{7042e1d4-c929-4a7e-8d34-b5d93b9495fd}”. SharePoint assigns a GUID to each object, you can’t use the View’s name as you could with a List’s name to select the object. To find out the GUID for your View, you’d have to open your SharePoint site in your web browser, navigate to the List and on the ribbon select Modify View. The GUID will then appear in the URL. Depending on which web browser you are using, it may be encoded so you’ll have to replace “%2D” with “-”, “%7B” with “{” and %7D” with “}”. Two things to note –

1) You can link to the same list multiple times, using different views so you are not necessarily restricted to choosing one view out of all views available for a given list.

2) Views only work as a linked SharePoint list – views can be created with web tables but are ignored in the web database, unless you create a separate linked table object which would not be accessible in the web browser, only in Access (this is what they call “hybrid application”, which we’ll talk about later).

You can use the View to provide additional filtering. This can be helpful particularly in cases where you may have a large amount of items but only need a handful of active, current, pending or open records. However, there’s one major difference between a SharePoint View and a SQL View. As you know, a SQL View has its own set of permissions and you can use Views to manage security. That’s not the case with SharePoint Views. You can prevent people from editing the view’s definition but you can’t prevent people from linking to the underlying SharePoint List directly.

How many columns can I really have?

As those familiar with SQL Server know, we can have a large table with many fields which while quite rare for normalized relational design may be desirable for a OLAP data model. SharePoint also allows a large number of fields. I already mentioned earlier that the List basically has a number of columns of certain data type. How does it handle the scenario when we need more than a given number of a certain data types? The answer is that SharePoint LIst uses Row-Wrapping; a single SharePoint List Item may be actually stored on more than one SQL Server row. By default, SharePoint allows you to row-wrap up to six SQL rows. Let’s take the example of Date and Time data types. The article linked above says we can have 48 Date and Time data type in a single List. If we divide 48 with 6, that actually means we only have 8 Date and Time data types that will fit a single SQL row. So, if we create a SharePoint List that had 20 columns and it happened that there was 9 Date and Time, then we’d have one SQL row populated with data from 19 fields and second SQL row populated with only one excess Date and Time data type. If your list then has 100 list items, we’d need to read 200 SQL rows to gather data from 20 columns including the 9th Date and Time data type. If performance is a important consideration in your design and you expect to store large amount of data, you should be mindful of how many columns and what data types you plan to have so you can minimize row-wrapping.

Conclusions

We’ve only scratched the surface when it comes to SharePoint but I’m confident you’ll find the information much more relevant to Access development and we’ll explore more about web database in my next post.

Ben is a Senior Access Developer at IT Impact, Inc.

For more details about SharePoint and Access integration, see links to my Webcasts in my Three Microsoft Access 2010 Webcasts Scheduled by Que Publishing for March, April and May 2011 post of 5/3/2011.

---

Thanks to Steven Thomas for the heads-up in his Access and SharePoint: a look at integration points from 2003 to 2010 post of 7/31/2011.

---

  

Technorati Tags: Microsoft Access 2010,Access 2010,Microsoft SharePoint 2010,SharePoint 2010,SharePoint Lists,Office 365,Access Services,Access Web Databases,Access Hosting

Migrate Access 2000 or Later Databases to Public or Private Rollbase Clouds

Now that Office 365 has been released to the Web (RTW) for commercial use, there is considerable interest in taking advantage of SharePoint Online’s Access Services to create Web-based data management applications (Access Web Databases) at a monthly cost of US$6 per user. An advantage of this approach is that Access Services supports migration of tables, queries, forms and macros to SharePoint lists, Web pages and workflows. Alternatively, you can move just the Access tables to SharePoint Online and link them to on-premises Access front-ends. Optional local data caching improves data access performance and enables offline data entry. You can learn more about migrating Access 2010 applications to SharePoint Online in my May 2011 Webcast.

Note: The current version of Office 365’s Access Services doesn’t support reports. If you need printed reports, Access Hosting offers hosted SharePoint 2010 for up to 10 users at a flat rate of US$99 per month. Access Hosting offers many advantages over SharePoint online, as described here (scroll down.) My Upsizing the Northwind Web Database to an Updated SharePoint 2010 Server Hosted by AccessHosting.com post contains links to my March 2011 Webcast about the topic.

Rollbase is a cloud application platform, which includes a wizard for importing Access 2000 or later *.mdb and Excel *.xls and *.csv files into Rollbase Objects and Fields to create Rollbase Applications for the Web. Rollbase’s primary claims to fame are its reported capability to import Salesforce and Force.com applications and availability in public and private cloud versions. There’s no indication on the Rollbase site of support for importing Access objects other than tables. Hosted applications are US$15 per month per user.

---

P.S.: Bruce Kyle reported 200 Sessions Announced for SharePoint Conference 2011 in a 7/17/2011 post to MSDN’s US ISV Evangelism blog:

SharePoint Conference 2011 is your only opportunity this year to see over 200 sessions focused on SharePoint 2010 and related technologies both in the cloud and on-premises. All current session content including abstracts and speakers have been posted to www.mssharepointconference.com!

The conference will be held in Anaheim, CA on October 3 – 6.

This year’s conference will have a breadth of both technical and non-technical sessions and will have suitable topics for everyone regardless if you are new to SharePoint 2010 or working on continuing your SharePoint education.

SPC11 will provide you with the training, insight, and networking you need to develop, deploy, govern and get the most from SharePoint. You’ll also hear from Microsoft Engineers, Product Managers, MCMs and MVPs who will discuss topics such as cloud services, best practices and real world project insights.

Don’t miss your chance to attend and learn from these sessions by registering now! Conference registration is only $1,199 and seats are selling fast with only 2.5 months until the event!

Pre & Post Conference Training Opportunities

SharePoint Conference announces five ancillary conference training opportunities with limited space! Act fast before space sells out!

To view more information on each session below click on the training title to learn more about the session including abstracts, agendas, speakers, costs and maximum attendance. Register now to reserve your seat!

Sunday, October 2

• Enable Better Decision Making with Microsoft Business Intelligence
• Learning to develop a high-end search solution with FAST Search for SharePoint 2010
• Project 2010 Technical drill-down for SharePoint Partners

Thursday, October 6 (2pm-6pm)

• SharePoint 2010 and Windows Azure Bootcamp: Developing SharePoint 2010 Applications that Integrate with the Cloud

Friday, October 7

• SharePoint 2010 Administrators Deep Dive

---

Technorati Tags: Microsoft Access 2010,Access 2010,Access,Access Services,Office 365,SharePoint Online,SharePoint 2010,Access Hosting,Rollbase,Salesforce.com,Force.com