Roger Jennings' Access Blog

Securing MS Linked Tables Connection Strings During Migration

2012-02-08T11:26:00.001-08:00

Han posted Securing MS Linked Tables Connection Strings During Migration to the SQL Server Migration Assistant Team Blog on 2/8/2012:

Microsoft Access stores all the connection strings for the respective linked tables in a system table called MSysObjects. As seen below, the connection strings contain clear-text used id and password. With the release for SSMA for Access 5.2, when creating link tables during migration, users will now have the option to not store the user id and password for the linked tables.

A new setting for linked tables can be found under the Project Settings menu. By default, the Store user credentials setting is set to false, thus user id and password will not be persisted in the connection string of a linked table. Switching the setting to true would provide the option to store the user id and password in the connection strings during the creation of linked tables.

It is important to note that after securing the connection string, MS Access users will have to enter the required user id and password whenever the linked tables are referenced in the MS Access Database application. Below shows the prompt presented by MS Access.

Technorati Tags: Microsoft Access 2010,Microsof Access,SQL Server Migration Assistant,SQL Server,SQL Azure,SQL Azure Migration,Cloud Computing,Windows Azure

How to save your Access SharePoint Password

2012-01-28T12:47:00.001-08:00

Emily Warn posted How to save your Access SharePoint password to the Access Blog on 1/26/2012:

Recently, someone posted a comment on IT Impact Inc.'s blog asking how to get SharePoint to remember his password when he logged in via Access. He wanted to avoid having to log in every time.
Ben Clothier, a Senior Access Developer at IT Impact, knew the answer. He wrote a detailed blog post (with plenty of screenshots) that we'd like to share with you.
ITImpact has been building custom databases with Microsoft Access since 1994, serving customers around the world. Ben Clothier has been Microsoft Access MVP since 2009 and is also a SharePoint Technology Specialist. He has worked on business applications from survey tracking to world-wide supply control databases.

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

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

2011-12-18T09:16:00.000-08:00

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

2011-11-18T14:38:00.001-08:00

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.

Check the “Ignore un-matched Pivot Key values and report them after DataFlow” execution box
Click OK to save the changes to the UI
Run the package in the designer
When the package succeeds, click on the Progress tab
Look for an information log message from the Pivot transform which contains the keys
Right click the message and select Copy Message Text
Click Stop to end the execution
Double click the Pivot transform
Uncheck the Ignore un-matched Pivot Key values checkbox
Paste the Pivot Key values into the bottom text box
Trim the text so that it only contains the key values – “[2002],[2003],[2004]”
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

2011-11-15T12:34:00.001-08:00

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:

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

2011-08-25T08:57:00.001-07:00

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

2011-08-07T11:34:00.001-07:00

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

2011-08-05T08:29:00.001-07:00

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

2011-08-04T16:29:00.001-07:00

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

2011-08-01T09:10:00.000-07:00

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.

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

2011-07-17T11:38:00.000-07:00

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

Problem Reported with Access Wizards and 64-bit Access 2010 SP1

2011-07-09T07:45:00.001-07:00

Stephen Thomas suggested Using 64-bit Access 2010? You may want to wait on [Installing] SP1 in a 7/8/2010 post to the Access blog:

A customer's post on TechNet brought one of my colleague's attention to an error folks are seeing after applying SP1 to 64-bit Access installations and then trying to use a wizard:
The database cannot be opened because the VBA project contained in it cannot be read. ... To open the database and delete the VBA project without creating a backup copy, click OK.
On clicking OK, Access doesn't open the database, offering this error message:
The code contains a syntax error, or a <DB_NAME> function you need is not available. If the syntax is correct, check the Control Wizards subkey or the Libraries key in the <DB_NAME> section of the windows registry to verify that the entries you need are listed and available.
Apparently, the VBE7.DLL file update included in the service pack prevents the opening of .ACCDE files compiled using RTM 64-bit Access. Because wizards are .ACCDE files, they could trigger the error depending on when they were compiled.
The customer who posted reports that uninstalling the service pack restores the functionality, and advises that people with 64-bit Access wait until a solution is provided before applying SP1. TechNet agrees (the mod marked it as an Answer), and so do I.
Stay tuned for that solution…

Forewarned is forearmed.

Technorati Tags: Microsoft Access 2010,Access 2010 SP1,Access 2010 64-bit,Access Wizards,Access 2010 Problems

New Substitute for the Access Upsizing Wizard Announced

2011-06-30T10:12:00.001-07:00

OneNumbus and Jasco posted Introducing Jasco® 2SQL™ Azure™ about a service for converting Access databases to SQL Azure (and SQL Azure) on 6/30/2011:

Help enable your employees to easily access up-to-date information for more effective decision making.
Jasco 2SQL quickly and securely migrates mission critical data to Microsoft SQL Server or Microsoft Azure, which helps ensure your data can be used more effectively across your organisation and remains accessible to your users.

Sounds to me like a pricey replacement for the Microsoft Access Upsizing Wizard and Access Data Projects (ADPs). It appears that Microsoft has informally deprecated ADPs, so perhaps 2SQLAzure has a chance of success.

For more details about moving Access tables to SQL Server and SQL Azure, see my Bibliography and Links for My “Linking Access tables to on-premise SQL Server 2008 R2 Express or SQL Azure in the cloud” Webcast of 4/26/2011 post of 4/24/2011.

Technorati Tags: Microsoft Access 2010,Microsoft Access,Access Data Projects,Upsizing Wizard,SQL Server,SQL Azure

New Office 365 Developer Training Course Includes Updated Access Services Unit

2011-06-29T13:35:00.001-07:00

Chris Mayo posted Office 365 is Now Available! to his SharePoint Development in the Cloud blog on 6/28/2011:

Office 365 was released to General Availability today with Steve Ballmer hosting the worldwide launch event in New York City. With this release, Office 365 (including SharePoint Online, Exchange Online, Lync Online and Office 2010 Professional Plus) can now be purchased directly from the Office 365 web site.
To coincide with the launch, I’m happy to announce an updated Office 365 Developer Training Course to help you get started building solutions for the cloud with Office 365. The course includes 8 sessions, over 12 hours of video and 19 labs as both an offline training kit as well as an online training course on MSDN.
The Office 365 Developer Training Course includes the following training units:

Developing in the Cloud with Office 365 (Updated)

Office 365 provides a communication and collaboration service in the cloud that you can leverage to build custom solutions for SharePoint Online, Exchange Online and Lync Online. In this session, you’ll learn about this new cloud service and the breadth of solutions that can be developed using the same skills, tools and SDKs you use today when building on-premises solutions.

Developing for SharePoint Online with Sandbox Solutions (Updated)

Sandboxed Solutions are the development paradigm for SharePoint Online. In this session, you’ll learn about sandboxed solutions including how to develop, debug and deploy solutions. You’ll also learn the breadth of solutions that can be developed in the sandbox and strategies for developing common scenarios that are not enabled in the sandbox.

Building Workflow Solutions for SharePoint Online (Updated)

Building Workflow solutions for SharePoint Online allows you to automate
collaboration-centric business processes and surface them to your users via SharePoint Online. In this session, you’ll learn the differences between
declarative and code-based workflows, design a workflow using Visio 2010,
implement that workflow in SharePoint Designer 2010 and customize the workflow using Visual Studio 2010 and custom actions.

Developing SharePoint Online Solutions with the Client Object Model (Updated)

The SharePoint Client Object Model provides libraries for programmatically
accessing SharePoint Online via Silverlight and JavaScript. In this session,
we’ll go deep into the Client Object Model and show you how to develop solutions using both Silverlight and JavaScript.

SharePoint Online Branding (New)

Customizing an intranet site with your company's identity and branding can help create a more effective collaboration experience. In this video, you'll learn how SharePoint Online allows users, designers and developers to customize the look and feel of a site. This can range from simple changes like setting a site logo and Theme to completely changing the user experience with custom styles and Master Pages. In this session, you'll learn how to make these customizations to brand your SharePoint Online site.

Leveraging Excel and Access Services in SharePoint Online (Updated)

Excel and Access Services provide powerful features for building SharePoint Online solutions. In this session, you’ll get an inside look at both Excel and Access services and how each can be accessed programmatically when building SharePoint Online solutions. [Emphasis added.]

Developing Communication Solutions for Lync Online (Updated)

In this session, you learn how to integrate Lync features into your WPF and Silverlight clients much in the same way that Office and SharePoint do, including presence, contact lists and click-to-communicate features. You will also learn how to extend Lync communications to include data and features from your client applications much in the same way that Outlook 2010 does with the "IM" and "Call" features within an email.

Developing Messaging Solutions for Exchange Online (Updated)

In this session, you'll learn how to integrate Exchange Online mailbox data such as mail, calendar and task items as well as Exchange Online services such as the free-busy service into your applications using an easy to discover and easy to use managed API.

To use this training course as self-paced training, you’ll need to do the following:

Set up a local SharePoint development environment (or download and configure the Information Worker VMs).
Download and install the Office 365 Developer Training Kit onto your development machine.
Sign up for an Office 365 trial to gain access to the service.
Visit the Office 365 Training Course on MSDN to watch the videos and get started with the labs.

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

Reading Office 365 Beta’s SharePoint Online Lists with the Open Data Protocol (OData)

2011-06-20T11:13:00.001-07:00

My earlier Access Web Databases on AccessHosting.com: What is OData and Why Should I Care? post (updated 3/16/2011) described working with multi-tenant SharePoint 2010 Server instances provided by AccessHosters.com. Microsoft is about to release Office 365 as a commercial service (presently scheduled for 6/28/2011), so the details for using OData to manipulate SharePoint Online lists have become apropos.

I delivered on 5/23/2011 a Moving Access Tables to SharePoint 2010 or SharePoint Online Lists Webcast for Que Publishing, which provides detailed instructions for creating Web databases by moving conventional Access *.accdb databases to SharePoint Online lists and, optionally, create Web pages that emulate Access forms and reports. Click here to open the Webcast’s slides, which begin by describing how to move Access tables to an onsite SharePoint 2010 server installation and how to overcome problems with relational features that SharePoint lists don’t support. Slides 30 through 34 describe how to move the tables from Northwind.mdb (the classic version) to a ServiceName.sharepoint.com/TeamSite/Nwind subsite, where ServiceName is the name you chose when you signed up for Office 365 (oakleaf for this example):

Opening an OData list of Collections

Log into your site’s default public web site at http://ServiceName.sharepoint.com/, click the Member Login navigation button, type your Microsoft Online username (UserName@ServiceName.onmicrosoft.com) and password if requested, and click OK to enter the default TeamSite in editing mode.

Click the SubsiteName, NWind for this example, and Lists links to display the lists’ names, descriptions, number of items and Last Modified date:

To display in OData a list of the SharePoint lists in a subsite, NWind for this example, type http://ServiceName.sharepoint.com/TeamSite/NWind/_vti_bin/listdata.svc in the address bar of IE9 or later:

Note: vti is an abbreviation for Vermeer Technologies, Inc., the creators of the FrontPage Web authoring application. Microsoft acquired Vermeer in January 1996 and incorporated FrontPage in the Microsoft Office suite from 1997 to 2003.

The EmployeesTitleOfCourtesy item is a lookup list with Dr., Miss, Mr., Mrs. and .Ms choices. The Attachment and two MasterPage… items are default lists found in all sites.

Display Items in a Collection

To display the list items in a collection, add /CollectionName/ (case-sensitive) to the URL for collections. If the resulting page in IE7+ appears similar to the following, you must turn off feed-reading view:

Turn Off Feed-Reading View, if Necessary

To turn off feed-reading view, open the Internet Options dialog, click the Content tab:

Click the Feeds and Web Slices section’s Settings button to open the Feed and Web Slice settings dialog and clear all check boxes:

Click OK three times to return to IE9+. Close and reopen IE to display the items in a formatted OData feed:

Verify Query Throttling

According to the All Site Content page, the OrderDetails table has 2,155 items. To determine if queries are throttled to return a maximum number of items, change the URL’s /CollectionName/ suffix from /EmployeesTitleOfCourtesy/ to /OrderDetails/. Press Ctrl+PgDn to navigate to the last item:

Office 365 beta throttles OData queries by delivering a maximum of 1,000 records per query, as indicated by the last item’s ID of 1000 and the <link rel="next" href="http://oakleaf.sharepoint.com/TeamSite/NWind/_vti_bin/listdata.svc/OrderDetails/?$skiptoken=1000" /> element immediately above the </feed> closing tag. Copy the URL with the skiptoken query option into the address bar to return the next 1,000 Order Details items, starting with ID 1001:

Paging to the end of the document confirms a $skiptoken=2000 value.

To return a single entry, append the Id value enclosed in parenthesis to the list name, as in http://oakleaf.sharepoint.com/TeamSite/NWind/_vti_bin/listdata.svc/OrderDetails(2001).

Note: SharePoint adds an autoincrementing Id primary key value to all lists generated from Access tables and saves the original primary key value in an __OldID column. This column is the source of the problem I reported in my SharePoint 2010 Lists’ OData Content Created by Access Services is Incompatible with ADO.NET Data Services post of 3/22/2011.

Remote Authentication in SharePoint Online Using Claims-Based Authentication

Third-party OData browsers, such as Fabrice Marguerie’s Sesame Data Browser, enable displaying, querying and, in some cases, updating content delivered by most OData providers. Sesame is a Sliverlight application, which runs from the desktop or in a browser. Sesame offers a built-in set of sample data sets and enables a variety of authentication methods, including Windows Azure, SQL Azure, Azure DataMarket and HTTP Basic.

Here’s a screen capture of browser-based Sesame with a connection specified to Fabrice’s Northwind sample database OData source:

Clicking OK displays members of the tables collection in the left-hand frame. Clicking an entry displays up to its first 15 elements. Hovering over a record selection arrow opens a button to display items in a related table by means of a lookup column, such as orders for AROUT in the following example:

Clicking the related table button opens the first 15 or fewer related entries in a linked query window:

The query string is Customers('AROUT')/Orders.

Problems Displaying SharePoint Online Lists in OData Format with the Sesame browser

Attempting to open the OData representation of a SharePoint Online list with Sesame’s Basic authentication (your Office 365 username and password), fails with a .NET Not Found exception. Here’s Fiddler 2.3.4.4 display of the raw HTTP request and response messages involved:

Following is a fiddler capture for a successful IE9 browser request for the OData representation of the SharePoint Online Products list:

Here’s Fiddler’s Headers view of the successful operation:

SharePoint Online doesn’t accept Basic authentication. Instead, it requires a pair of login cookies to enable remote claims-based authentication.

Robert Bogue’s Remote Authentication in SharePoint Online Using Claims-Based Authentication article for the MSDN Library’s “Claims and Security Articles for SharePoint 2011” topic explains how remote claims-based authentication works:

Introduction to Remote Authentication in SharePoint Online Using Claims-Based Authentication

The decision to rely on cloud-based services, such as Microsoft SharePoint Online, is not made lightly and is often hampered by the concern about access to the organization's data for internal needs. In this article, I will address this key concern by providing a framework and sample code for building client applications that can remotely authenticate users against SharePoint Online by using the SharePoint 2010 client-side object model.
Note: Although this article focuses on SharePoint Online, the techniques discussed can be applied to any environment where the remote SharePoint 2010 server uses claims-based authentication.
I will review the SharePoint 2010 authentication methods, provide details for some of the operation of SharePoint 2010 with claims-mode authentication, and describe an approach for developing a set of tools to enable remote authentication to the server for use with the client-side object model.

Brief Overview of SharePoint Authentication

In Office SharePoint Server 2007, there were two authentication types: Windows authentication, which relied upon authentication information being transmitted via HTTP headers, and forms-based authentication. Forms-based authentication used the Microsoft ASP.NET membership and roles engines for managing users and roles (or groups). This was a great improvement in authentication over the 2003 version of the SharePoint technologies, which relied exclusively on Windows authentication. However, it still made it difficult to accomplish many scenarios, such as federated sign-on and single sign-on.
To demonstrate the shortcomings of relying solely on Windows authentication, consider an environment that uses only Windows authentication. In this environment, users whose computers are not joined to the domain, or whose configurations are not set to automatically transmit credentials, are prompted for credentials for each web application they access, and in each program they access it from. So, for example, if there is a SharePoint-based intranet on intranet.contoso.com, and My Sites are located on my.contoso.com, users are prompted twice for credentials. If they open a Microsoft Word document from each site, they are prompted two more times, and two more times for Microsoft Excel. Obviously, this is not the best user experience.
However, if the same network uses forms-based authentication, after users log in to SharePoint, they are not prompted for authentication in other applications such as Word and Excel. But they are prompted for authentication on each of the two web applications.
Federated login systems, such as Windows Live ID, existed, but integrating them into Office SharePoint Server 2007 was difficult. Fundamentally, the forms-based mechanism was designed to authenticate against local users, that is, it was not designed to authenticate identity based on a federated system. SharePoint 2010 addressed this by adding direct support for claims-based authentication. This enables SharePoint 2010 to rely on a third party to authenticate the user, and to provide information about the roles that the user has. …

Robert continues with an “Evolution of Claims-Based Authentication” topic and …

SharePoint Claims Authentication Sequence

Now that you have learned about the advantages of claims-based authentication, we can examine what actually happens when you work with claims-based security in SharePoint. When using classic authentication, you expect that SharePoint will issue an HTTP status code of 401 at the client, indicating the types of HTTP authentication the server supports. However, in claims mode a more complex interaction occurs. The following is a detailed account of the sequence that SharePoint performs when it is configured for both Windows authentication and Windows Live ID through claims.

The user selects a link on the secured site, and the client transmits the request.
The server responds with an HTTP status code of 302, indicating a temporary redirect. The target page is /_layouts/authenticate.aspx, with a query string parameter of Source that contains the server relative source URL that the user initially requested.
The client requests /_layouts/authenticate.aspx.
The server responds with a 302 temporary redirect to /_login/default.aspx with a query string parameter of ReturnUrl that includes the authentication page and its query string.
The client requests the /_login/default.aspx page.
The server responds with a page that prompts the user to select the authentication method. This happens because the server is configured to accept claims from multiple security token services (STSs), including the built-in SharePoint STS and the Windows Live ID STS.
The user selects the appropriate login provider from the drop-down list, and the client posts the response on /_login/default.aspx.
The server responds with a 302 temporary redirect to /_trust/default.aspx with a query string parameter of trust with the trust provider that the user selected, a ReturnUrl parameter that includes the authenticate.aspx page, and an additional query string parameter with the source again. Source is still a part of the ReturnUrl parameter.
The client follows the redirect and gets /_trust/default.aspx.
The server responds with a 302 temporary redirect to the URL of the identity provider. In the case of Windows Live ID, the URL is https://login.live.com/login.srf with a series of parameters that identify the site to Windows Live ID and a wctx parameter that matches the ReturnUrl query string provided previously.
The client and server iterate an exchange of information, based on the operation of Windows Live ID and then the user, eventually ending in a post to /_trust/default.aspx, which was configured in Windows Live ID. This post includes a Security Assertion Markup Language (SAML) token that includes the user's identity and Windows Live ID signature that specifies that the ID is correct.
The server responds with a redirect to /_layouts/authenticate.aspx, as was provided initially as the redirect URL in the ReturnUrl query string parameter. This value comes back from the claims provider as wctx in the form of a form post variable. During the redirect, the /_trust/default.aspx page writes two or more encrypted and encoded authentication cookies that are retransmitted on every request to the website. These cookies consist of one or more FedAuth cookies, and an rtFA cookie. The FedAuth cookies enable federated authorization, and the rtFA cookie enables signing out the user from all SharePoint sites, even if the sign-out process starts from a non-SharePoint site.
The client requests /_layouts/authenticate.aspx with a query string parameter of the source URL.
The server responds with a 302 temporary redirect to the source URL.

Note: If there is only one authentication mechanism for the zone on which the user is accessing the web application, the user is not prompted for which authentication to use (see step 6). Instead, /_login/default.aspx immediately redirects the user to the appropriate authentication provider—in this case, Windows Live ID.
SharePoint Online Authentication Cookies

An important aspect of this process, and the one that makes it difficult but not impossible to use remote authentication for SharePoint Online in client-side applications, is that the FedAuth cookies are written with an HTTPOnly flag. This flag is designed to prevent cross-site scripting (XSS) attacks. In a cross-site scripting attack, a malicious user injects script onto a page that transmits or uses cookies that are available on the current page for some nefarious purpose. The HTTPOnly flag on the cookie prevents Internet Explorer from allowing access to the cookie from client-side script. The Microsoft .NET Framework observes the HTTPOnly flag also, making it impossible to directly retrieve the cookie from the .NET Framework object model.
Note: For SharePoint Online, the FedAuth cookies are written with an HTTPOnly flag. However, for on-premises SharePoint 2010 installations, an administrator could modify the web.config file to render normal cookies without this flag. …

Robert concludes the article with “Using the Client Object Models for Remote Authentication in SharePoint Online” and “Reviewing the SharePoint Online Remote Authentication Sample Code Project.”

Fabrice will need to add a SharePoint authentication option with code similar to that described in the article to enable interacting with OData content from SharePoint Online.

OData References

Following is a table of useful references that provide the details of OData query and data update syntax:

ID	Author	Description	Date
1	Microsoft (MSDN)	[MS-ODATA]: Open Data Protocol (OData) Specification	5/2011
2	Microsoft (MSDN)	Windows Azure Storage Services REST API Reference	2011
3	Robert Bogue for MSDN	Remote Authentication in SharePoint Online Using Claims-Based Authentication	4/2011
4	OData.org	Open Data Protocol Organization Web Site and Blog	6/2011
5	Chris Sells	Open Data Protocol by Example	3/2010
6	Chris Sells	Hello, Data	6/2010
7	SAP	How To... Create Services Using the OData Channel	4/2011
8	Roger Jennings	Access Web Databases on AccessHosting.com: What is OData and Why Should I Care?	3/16/2011
9	Roger Jennings	SharePoint 2010 Lists’ OData Content Created by Access Services is Incompatible with ADO.NET Data Services	3/22/2011
10	Roger Jennings	Reading Office 365 Beta’s SharePoint Online Lists with the Open Data Protocol (OData)	6/20/2011
11	Access Team	Get to Access Services tables with OData	7/20/2010
12	Eric White	Getting Started using the OData REST API to Query a SharePoint List	12/9/2010
13	Eric White	Using the OData Rest API for CRUD Operations on a SharePoint List	12/17/2010
14	Eric White	Consuming External OData Feeds with SharePoint BCS	5/23/2011
15	Jonathan Carter	Open Data for the EnterpriseOpen Data for the Enterprise (TechEd 2010 session)	6/9/2010
16	Alex James	Best Practices: Creating OData Services Using Windows Communication Foundation (WCF) Data Services (TechEd 2010 session)	6/9/2010

Technorati Tags: Open Data Protocol,OData,SharePoint 2010,SharePoint Online,SharePoint,Access Services,Microsoft Access,Access 2010,Access Web Databases,Remote Authentication,Claims-Based Authentication,Federated Authentication,Sesame Data Browser

Reading and Updating Office 365 Beta’s SharePoint Online Lists with OData

2011-06-19T10:20:00.001-07:00

Updated 6/20/2011: My Reading Office 365 Beta’s SharePoint Online Lists with the Open Data Protocol (OData) post of 6/20/2011 replaces this article due to an incompatibility problem with Windows Live Writer 2011.

The new post expands on the use of Fabrice Marguerie’s Sesame Data Browser to read and query OData feeds. Sesame won’t open OData content generated by SharePoint Online due to issues with required claims-based federated authentication. The updated post explains these issues.

A later post will provide details about adding, updating, and deleting OData content.

Technorati Tags: Open Data Protocol,OData,SharePoint 2010,SharePoint Online,SharePoint,Access Services,Microsoft Access,Access 2010,Access Web Databases

Office 365 - Product Insights: Access and Forms Services in SharePoint Online

2011-06-15T12:23:00.001-07:00

Ben Tamblyn (@btamblyn) posted Product Insights: Access and Forms Services in SharePoint Online to the Microsoft Office 365 Blogon June 15, 2010:

Hi, following today’s post we’ll have three more posts the SharePoint Online product insights series. Today we’re going to show you how you can use Access and Forms Services to:

simplify routine business processes and;
capture information and feedback quickly

Next week is the last week – we’ll focus on Search and how SharePoint Online works together with Project Professional 2010.

In the meantime feel free to add a comment at the bottom of this post on other areas you’d like to see shown over the summer.

Note: See my My SharePoint Online (Office 365) Site for links to my Webcasts and more details about Access Web Databases, Office 365, SharePoint Online and Access Services.

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

Tim Anderson on iPad and iPhone with FileMaker Pro and Go

2011-06-13T14:02:00.001-07:00

Tim Anderson (@timanderson) described Easy database apps for iPad and iPhone with FileMaker Pro and Go in a 6/13/2011 post:

FileMaker Pro is a database manager from FileMaker Inc, a wholly owned subsidiary of Apple. It is a capable produce that has been around for over 20 years and is the dominant Mac-based database manager, though there is also a Windows version. FileMaker has evolved relatively slowly, with more focus on usability than on features. In comparison to Microsoft Access, FileMaker wins on usability and scalability, but Access has a more traditional approach based on SQL and programming with Visual Basic for Applications. FileMaker has a drag-and-drop script editor and support for AppleScript on the Mac.
Although the script editor is frustrating for someone used to writing code, it does work. As well as manipulating the data, you can set and retrieve local and global variables, perform loops and display custom dialogs; it is not as limited as it may seem at first.
A FileMaker database can be huge, with 8 terabytes specified as the theoretical limit. External databases are accessible through ODBC on both Windows and Mac.
The number of users supported by FileMaker is limited. The desktop product supports up to 5 concurrent users, and FileMaker Server up to 250 users. FileMaker has its own built-in security system, though FileMaker server can also authenticate against an external directory. Security is fine-grained, and you can even specify permissions for an individual record.
I have not looked at FileMaker for a few years, but renewed my interest when the company came out with FileMaker Go, a runtime client for Apple iOS. Given that FileMaker runs scripts you might have thought this would be restricted, bearing in mind this provision in the App Store guidelines:

2.7 Apps that download code in any way or form will be rejected

This is normally taken to prohibit runtimes like Java or Adobe Flash/AIR. Well, either someone decided that FileMaker scripts are not code; or there are special rules for an Apple subsidiary, which is reasonable enough. Anyway, FileMaker Go is in the App Store and does run scripts.
What this means is that you can create apps in FileMaker Pro and deploy them to iOS without going via the App Store. There are two models. FileMaker Go can open a file hosted by FileMaker desktop or server, in which case it behaves like a Mac or Windows client, or alternatively you can transfer a file to FileMaker Go to run locally. Transferring a file is easy using iOS launch service; essentially, if you can access the file via the internet or an email attachment, you can just tap it on the device and it will open in FileMaker Go. The advantage of running locally is offline use, whereas the advantage of the client-server model is that all users have the most up-to-date version of the data, and the database can be much larger. FileMaker is a real server application; this is not just file sharing. This also means that FileMaker must be running with the database open if you want to to use the client-server approach.
I tried FileMaker Go with a simple example and it works well. In essence it is delightful; you just open your database either locally or over the network, and it works. Here is a sample app on the iPhone 4:

That said, there are things that do not work, spell checking for example. It is also stripped of anything other than client features, so you cannot modify database structure, create new databases, or publish from the device to other clients. You also have to be careful with layout size. Most layouts designed for the desktop will need modification to work well.
There are a couple of issues. One is performance. It is just about bearable, but has that lethargic feel that you get with interpreted code on a relatively slow processor.
Another issue is synchronisation. If you want to work offline, how do you update your main database with any changes? The issue is little different with FileMaker Go than it is with a laptop, and it is discussed here. You have several choices:
1. Don’t synchronize, use client-server.
2. Treat your local database as read-only.
3. Use import and export. Existing records will simply be overwritten by imported ones.
4. Use a third-party tool. However the tool mentioned here, SyncDek, probably does not work with FileMaker Go since it needs to run a Java process on the client.
5. Roll your own. “FileMaker Pro has all the tools needed to create a robust synchronization system” says the guide; but it is non-trivial to implement this.
It is worth mentioning that FileMaker Pro also has an Instant Web Publishing feature that gives another route to mobile access and may perform better. There are pros and cons. The big one is offline, only available with FileMaker Go. Another is scripts. Some scripts work in Instant Web Publishing, but FileMaker Go is more compatible in this area.
I think this is significant for businesses where iOS devices are turning up. Many business apps do resolve down to forms over data, and this is is an easy way to deliver this kind of application to iOS users.
How is FileMaker pro as a programming tool? Just for fun, and because I have done it for other mobile development tools, I built a calculator in FileMaker. I do not recommend FileMaker for general-purpose programming; but it has the essentials, a form designer and scripting. Here is the result on an iPhone 4:

Oddly the biggest struggle I had was finding an easy way to display the input and result. In the end I added a field to the database just for this purpose. If a FileMaker expert could let me know a better way to update a text label on a layout via script, I would be interested to know.
The calculator is slow too, not for the calculation of course, but the operation of the user interface. Still, it does demonstrate that FileMaker Go is indeed able to download code and run it.
Related posts:

Adobe targets Apple iPhone and iPad browsers with tool to convert Flash projects
Trying out MonoTouch – C# for Apple’s iPhone and iPad
Enterprise app development on Apple iPhone and iPad

There are some interesting parallels between FileMaker Go and Azure Web Databases, such as Web Databases support for macro programming only. It will be interesting to see what the next version of SharePoint’s Access Services will bring to Windows Phone 7 clients.

Technorati Tags: Microsoft Access,Microsoft Access 2010,Access 2010,Access Web Databases,SharePoint 2010,SharePoint Online,Access Services,FileMaker Pro,FileMaker Go

Microsoft Office 365 Cloud Suite Launch Date: June 28

2011-06-03T15:18:00.001-07:00

Matthew Weinberger (@MattNLM, pictured below) posted Microsoft Office 365 Cloud Suite Launch Date: June 28 to the TalkinCloud on 6/3/2011:

Microsoft Corporate Vice President of Worldwide Partner Group Jon Roskill (in the form of a Twitter post) says the Office 365 cloud productivity suite will be generally available starting on June 28, 2011.
Here’s the full text of the tweet in question:
June 28th is the date for General Availability of Office 365! > 100,000 real customers on beta…Partners, are you ready???
Roskill [pictured at right] essentially is Microsoft’s Worldwide Channel Chief. As a refresher, Office 365 consists of cloud-hosted versions of Microsoft Office, SharePoint Online, Lync Online and Exchange Online. It’s going to be replacing the Microsoft BPOS suite, though existing Microsoft Online Services customers will have some time prepare to switch from BPOS to 365.
The beta that Roskill is referring to only launched in early April, so if that 100,000 “real customers” number is accurate, it’s a fairly impressive achievement for Microsoft — despite the fact that your humble correspondent was left wanting by a demo.
Microsoft’s been aggressive about promoting Office 365 to channel partners and ISVs. But at the same time, they’ve remained adamant that Microsoft and only Microsoft will continue to own the customer billing relationship, leading to more than a few partners to express reservations.
The timing of the launch is a small wonder — they clearly want to roll out Office 365 in time for the Microsoft Worldwide Partner Conference (July 10-14, Los Angeles), where Roskill and company are going to take the case for the cloud directly to their reseller base.
Keep watching TalkinCloud for updates as we get closer to that June 28th launch.

I hope the release-to-web version fixes the inability of Access Web Databases to print reports from SharePoint Online.

See my Learn How To Create Access Web Databases with Office 365’s SharePoint Online Beta from my Latest Webcast post of 3/27/2011 for more information on Access Web Databases running from Office 365’s Access Services.

Technorati Tags: Office 365,SharePoint Online 2010,Access 2010,Access Web Databases,Web Databases,Access Services

Access to SQL Server Migration: Understanding the Assessment Report and Error List

2011-05-27T16:10:00.001-07:00

Bill Ramos posted Access to SQL Server Migration: Understanding the Assessment Report and Error List on 5/27/2011. This information will be useful to folks who watched my Upsizing Access 2010 Projects to Web Databases with SharePoint 2010 Server Webcast:

The SQL Server Migration Assistant for Access assessment report displays the results from converting a Jet based Microsoft Access solution to use SQL Server. The report displays errors, warnings, and information settings based on either the Default Project Settings or the Project settings you have when set when you run the report. For information regarding the project settings, see “Access to SQL Server Migration: Understanding SSMA Project Settings”. The Error List user interface provides a convenient way to navigate to the specific object that has the error, warning, or informational message. This blog builds on Access to SQL Server Migration: How to Use SSMA using the Access Northwind 2007 template.
Running the Assessment Report

There are two ways of viewing the assessment report in SSMA. The first is way is to access the report after running the Migration Wizard as shown below.

When you run the report via the wizard, you’ve already completed the migration so there is no opportunity to correct any errors before perform the Convert Schema and Migrate Data commands. There is a nice feature within the Migration Wizard dialog that allows you to click on the hyperlink total of messages to display the Error List dialog.
The second way of launching the error report is to not use the wizard and follow these steps:

Create a New Project and complete the dialog.
Issue the Add Databases command and select the database you want to migrate.
Expand the Access-metadata node in the Access Metadata Explorer. Clear the checkboxes except for the database you want to run the report on.
Right-click on the database and select Create Report.

Elements of the Assessment Report

For the Northwind 2007 database, your assessment report will look like this.

The navigation pane allows you to drill down to see which objects had problems reported as an error.
The details pane provides a summary of the errors when you select a folder in the navigation pane or all of the errors, warnings, and info messages when you select the actual object.
The tool bar allows you to filter the lower pane by errors, warnings, or info messages by clicking on the element.
The tool bar displays the estimated manual conversion time required to resolve all of the errors displayed
The display pane allows you to group by specific error, warning, or info messages. It also provides a way to navigate the specific object and all messages associated with the object. The image below shows that happens when you select the Employees table under the “Datatype is not supported” error message.

Considerations for Migrating Access Queries

When you manually run the report by selecting all objects, you may get more errors reported because SSMA reports against potential issues when attempting to migrate Queries into SQL Server View statements. In general, you will want to take a linked table approach when migrating your solution. This approach keeps the Query definition in Access as is and relies on using the linked table references in the query to process the results using the Jet engine.
The recommended approach is to uncheck Queries in the Access Metadata Explorer before running the report. This approach also prevents SSMA for creating Views on SQL Server when you eventually issue the Convert Schema and Synchronize with Database commands.
Using the Error List

Assuming that you converted Northwind 2007 using Migration Wizard with the Linked Tables option, you will see and Error List when you close the Assessment Report and the Migration Wizard as shown below.

By clicking on the 245 Warnings and 233 Information controls highlighted above, you can show just the 6 Errors reported after the conversion.

By double clicking on the error, SSMA takes you to the object associated with the message in both the Access Metadata Explorer and SQL Server Metadata Explorer and displays the definition of the objects as shown below.

You can then see how SSMA mapped the unsupported data type in SQL Server from Access by scrolling through the Access and SQL Server table definitions.
Dealing with Attachment, Hyperlink, and ComplexLong Data Types

It’s been our observation that Access solutions that need migration to SQL Server very seldom use the problem data types used for the Northwind2007 sample database. Here are some things to consider if you do need to migrate these types.
Attachment

The Attachment type stores the file in Access database. In SQL Server, you have several options to consider. You will want to extract the files from the Access database and then consider storing links to the files in your SQL Server database or using the FILESTREAM feature to maintain the attachments. In the upcoming SQL Server code name “Denali” release, you can use the new FileTable feature.
Hyperlink

The Hyperlink data type ends up getting mapped to a nvarchar(max) type by SSMA. You may want to alter the result using the table editing feature in SSMA’s SQL Server Metadata Explorer or using SQL Server Management Studio to use a smaller value. In your Access solution, you can still use the hyperlink behavior in Forms and Reports if you set the Hyperlink property for the control to true.
ComplexLong

The ComplexLong data type is the result of using a lookup query and choosing the multi-selection option for the Long data type. Underneath the covers in Access, there is a junction table that records the results of the multiple selection. In SQL Server, you can emulate the same behavior with a junction table based on the query and then reference the junction table in your Access solution.
Conclusion

As you can see, SSMA provides two mechanisms to help you identify potential issues that you may need to deal with for migrating an Access database to SQL Server. The Assessment Report and Error List user interfaces are also part of SSMA for MySQL, Oracle and Sybase and work in a similar way. The time estimates included in the Assessment Report are just a guideline to help in estimating the scope of the changes needed and can vary greatly in your approach to solving a specific issue.
Additional Resources and References

For specific details on each of the project options, please refer to the Project Settings help topics for SSMA for Access.

Optimizing Microsoft Office Access Applications Linked to SQL Server http://msdn2.microsoft.com/en-us/library/bb188204.aspx describes in detail many of the techniques that were only mentioned briefly in this blog post.

The UtterAccess forum has a discussion group dedicated to SQL Server questions http://www.utteraccess.com/forum/Microsoft-SQL-Server-f32.html. You can post questions and get answers from Access experts who work with SQL Server on a daily basis.

The FMS Upsizing Resource Center contains links to various resources http://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/index.html.

JStreet Technology's developer downloads section http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp has useful tools and presentations to help you get up to speed.

Technorati Tags: Microsoft Access,Access 2010,SQL Server,SQL Server 2008 R2,SQL Server Migration Assistant for Access,SSMA,Webcasts

Learn How To Create Access Web Databases with Office 365’s SharePoint Online Beta from my Latest Webcast

2011-05-27T10:22:00.000-07:00

My Moving Access Tables to SharePoint 2010 or SharePoint Online Lists Webcast (posted 5/24/2011) describes how to move and link Access tables to SharePoint lists. As a bonus, it also includes detailed instructions for and an example of using SharePoint Online‘s Access Services to create a Web Database from a SharePoint (not Access) Online Contacts template.

Click here to watch the Webcast and download the PowerPoint slides.

If you’re familiar with the process of moving Access 2007 or 2010 tables to SharePoint Server 2007 (MOSS), Windows SharePoint Services (WSS) 3.0, or SharePoint 2010 lists, skip directly to 00:22:16 for a detailed demo:

You also can learn how to create and synchronize a SharePoint 2010 or SharePoint Online Workspace to your local computer, starting with this slide at 00:16:00:

Here are live links to the the bibliography for the latest Webcast and to earlier Webcasts:

Read Office 365 vs. Google Apps: Microsoft Comes Out Firing, an 5/20/2011 interview of Microsoft’s Tom Rizzo (pictured below) by Paul Thurrott of Windows IT Pro. A few choice excerpts:

Cue Microsoft Senior Director Tom Rizzo, who I'd categorize as a breath of fresh air at a company I was getting seriously worried about.
"I am not going to let a competitor determine the dialog about my product," Rizzo told me during a phone interview last week. "I'm going to uncover the truth. We were a bit of the silent giant before. Now we're not so silent."
Those are fighting words. And while it's perhaps a bit of a stretch to suggest that I actually leapt out of my seat and punched the air with my fist, it happened in spirit. This is the Microsoft I've been missing for these many years.
So what was Rizzo talking about? We met to discuss the competitive landscape in the online services space, which is to say he wanted to compare how Google's offering—Google Apps—stacked up against Microsoft's forthcoming Office 365. As a matter of full disclosure, I've been using Office 365 for months, and as a long-time Gmail (and Google Calendar) user, I had previously evaluated Google Apps and found it—how you say?—lacking. So I was already on board with this line of thinking, frankly, because in my (hopefully non-partisan) mind, Microsoft clearly has the superior solution of the two.
But let's see what Mr. Rizzo has to say about this. After all, I love the approach.
"Google is making a lot of noise these days," Rizzo noted. "But the interesting thing is that Google's bark is worse than its bite. And of those few customers who dropped Microsoft solutions to 'go Google,' many are now coming right back." …
"Google Apps has made absolutely no dent in the market at all," Rizzo claimed. "They are failing. If I had to give them a grade, it would be an F. They're just throwing darts at the wall."
So while Rizzo makes up, and then some, for almost a decade of quiet acceptance at the software giant, and I try to wipe this crazy grin off my face, let's consider some numbers. And there are a lot of numbers:

30 million. Google claims that it has 30 million users. But it also noted that these users were spread out between 3 million businesses, schools, and governmental agencies. This means that, on average, there are 10 users per deployment. "These guys are just dipping their toes in the Google water," Rizzo said. "And more often than not, they come back to Microsoft because Google doesn't meet their IT requirements, whether it's a small business or an enterprise. And Google promised it would 'kill Office,' but its [online apps] don't have functionality; this isn't coming to fruition."
9 out of 10. Furthermore, Rizzo says that a survey of Google Apps customers revealed that fully 9 out of 10 were also Microsoft Office users. This means that these customers are not abandoning Office as Google had hoped. "These guys aren't replacing Office," Rizzo said. "They're just trialing Google."
<1 percent. According to a late 2010 Gartner survey, less than one percent of enterprise users were using Google Apps. And that's after 4 years in the market. "These customers trust Microsoft," Rizzo noted. "We provide privacy, security, manageability, and a financially-backed SLA. Google is an ad company. They shoehorn consumer stuff into the commercial space and do not understand the needs of commercial users."
10. When Google came to market with Google Apps, it offered both free and paid versions. But the numbers of users it allowed into the free version has dropped precipitously over the years, going from 250 to 100 to 50 and now to just 10, Rizzo said. (Microsoft doesn't offer a free version of Office 365; its small business version supports up to 25 users.) So why the retreat on the free version of Google Apps? "Google is not making money in this space," Rizzo said.
$5 vs. $6. "Google just raised prices [on the paid version of Google Apps] to $5 per user per month," Rizzo said. This compares to $6 per user per month for the small business version of Office 365, or a $1 difference. "So for the price of one cup of coffee at Starbucks, the additional value here is astronomical," Rizzo said. "There are no ads in Office 365, whereas Google makes the customer the product by putting ads in there. We earn your trust, we respect your privacy, and you pay us to run a valued service."
97 percent. Google makes 97 percent of its revenues from web ads, Rizzo said. It's an ad company. And when you look at the remaining 3 percent, which includes Google Apps, it has declined 23 percent year over year. "Google is under a lot of pressure to make it a viable product," Rizzo said. "They are feeling the pressure."

Tom runs the SharePoint Product Management team at Microsoft. I worked with him for several years on many stories for Visual Studio Magazine while he was Director of Product Management in the SQL Server team. Tom was the Technical Editor for my Expert One-on-One Visual Basic 2005 Database Programming book (WROX/Wiley, 2006.)

Technorati Tags: SharePoint 2010,SharePoint Online,Office 365,Access Services,Web Databases,Microsoft Access 2010,Access 2010,SharePoint Workspace,Office 2010,Google Apps,Google Apps for Business

Bibliography for My “Moving Access Tables to SharePoint 2010 or SharePoint Online Lists” Webcast of 5/23/2011

2011-05-22T12:05:00.000-07:00

Downloading NwindMove.zip and GroupPol.zip from Windows Live SkyDrive

NwindMove.zip (1 MB) contains the NwindMove.accdb database and a NwindMove_Backup.accdb file for moving tables from an Access application to SharePoint lists with the Export Tables to SharePoint Wizard. These two files are slightly modified versions of those included in the \Chaptr22 folder of the Microsoft Access 2010 in Depth book’s sample files. Download the file here.

The GroupPol.accdb (11,944 KB) database has contact data for 2,275 fictitious employees and 25,344 students of an equally fictitious OakLeaf University. The file is suitable for adding contacts in bulk to Access databases and Web databases created from the Contacts template. GroupPol.accdb is based on the Oakmont database that’s included in the downloadable sample files for my Microsoft Access 2010 In Depth book. You can download GroupPol.zip, which contains GroupPol.accdb, from my Windows Live SkyDrive account here.

Office 365 SharePoint Online and Administration Forums, Blogs

Office 365 Forums Websites and Collaboration with SharePoint Online:

Office 365 Service Administration:

Office 365 Blogs:

Microsoft Office 365 Blog: Interesting in learning more about Microsoft Office 365? Follow our blog to stay up to date with the latest news and information on taking your business to the cloud!
Office 365 Technical Blog: The Office 365 engineering team blog, supporting our customers whether getting started, performing advanced tasks or troubleshooting common issues. We love to hear from you, so comment and share your thoughts on the latest and greatest cloud service!
The Grid: The Grid is a new community that brings together IT pros and other experts who will share their considerable expertise about technology and Office 365.

SharePoint 2010 TechNet Forums

SharePoint 2010 - General Questions and Answers: General questions, comments and discussions on SharePoint 2010 products and SharePoint services such as Access Services, BCS, Excel Services and Visio Services.
SharePoint 2010 - Setup, Upgrade, Administration and Operation: Discuss setup, upgrade, administration and operation for SharePoint 2010 and SharePoint services such as Access Services, BCS, Excel Services and Visio Services.
SharePoint 2010 - Using SharePoint Designer, Infopath, and other customization: Discuss using SharePoint Designer, SharePoint Gallery Solutions, templates & other customization for SharePoint 2010 and SP services such as Access Services, BCS, Excel Services, & Visio Services.
SharePoint 2010 - Using Visual Studio with SharePoint and other programming: Discuss using Visual Studio and other programming with SharePoint 2010 and SharePoint services such as Access Services, BCS, Excel Services and Visio Services.

Note: SharePoint Online doesn’t support Business Connectivity Servces (BCS)

Downloading and Installing SharePoint Foundation 2011

Microsoft’s Download Center’s Microsoft SharePoint Foundation 2010 page contains the following information:

Overview

SharePoint Foundation 2010 is the new version of Microsoft Windows SharePoint Services. It is the essential solution for organizations that need a secure, manageable, web-based collaboration platform. SharePoint helps teams stay connected and productive by providing easy access to the people, documents, and information that they need to make well-informed decisions and get work done. Use SharePoint Foundation to coordinate schedules, organize documents, and participate in discussions through team workspaces, blogs, wikis, and document libraries on the platform that is the underlying infrastructure for SharePoint Server.
Search Server 2010 Express: If you’re using SharePoint Foundation, you can easily add search capabilities to your collaboration environment across SharePoint sites, file shares, web sites, Exchange Public Folders, and third party repositories using Microsoft Search Server 2010 Express. Click here to download.
System Requirements

Supported Operating Systems:Windows Server 2008 R2;Windows Server 2008 Service Pack 2

Please review the SharePoint Foundation 2010 system requirements before you proceed. Actual requirements and product functionality may vary based on your system configuration.

SharePoint Foundation 2010 can also be installed for development purposes on Windows 7 and Windows Vista Service Pack 2. Installing on these operating systems is not supported for production deployments. For instructions on how to install on these operating systems, refer to Setting Up the Development Environment for SharePoint 2010 on Windows Vista, Windows 7, and Windows Server 2008.

Signing Up for the Office 365 Small Business (P1) Beta Program

Click the Join the Beta button on the Office 365 for Small Business page.

Evaluating database needs across SQL Server, SQL Azure, SharePoint and Access

The Access Team’s Evaluating database needs across SQL Server, SQL Azure, SharePoint and Access post of 1/10/2011 to the Microsoft Access blog explains how to choose between the four primary back ends for Access front ends:

Every now and then we hear from customers who want some cheat sheet or Excel-based "calculator" to help them decide what technology to base their application on: SQL Server, SharePoint, Access, etc. While in some cases (especially in the extremes) the decision is relatively easy to make, it is very common for the decision to be more complex than what can be obtained from such "calculators." There are many issues to consider: from technical requirements of the application in terms of performance, availability, maintenance, backup, scalability, security, etc., to important business requirements in terms of agility, time to value, and the changing business needs of the organization.
We put together a white paper that provides an overview of a few of these products, including:

Access
SharePoint
SQL Server
SQL Azure

The paper also discusses some of the requirement areas to consider as part of the decision-making process.
The most important take-away of this document may be common sense: in many cases, deciding how to best solve data-centric problems requires careful consideration and participation of technical and business experts who are able to articulate multiple angles of the problem (both business and technical) and are able to make an informed decision that addresses the immediate and future needs of the organization. In other words, avoid the temptation to use a cheat sheet! Look around at the people in the room: do you have the right people for the scope of the application you are building? Do you need, and if you do, do you have representation from both IT and the business organization? Do you have the right experts on these technologies helping you make a choice? If you don't, what are the implications if you make the wrong choice? Do you understand how the application and business problem will evolve in the future?
Those are questions that only you can answer and for which a calculator (or a white paper!) is really not the best approach. We hope that this white paper helps you understand the core strengths and capabilities of these technologies and gives you a starter set of issues to consider in discussing your selection. The good thing is that we got you covered: from Access to SharePoint to SQL Server and SQL Azure, your application will likely benefit from one or more of those products.
(Word 2007-2010 .DOCX format)
(Word 97-2003 .DOC format)

SharePoint Lists

Microsoft Access 2010, 2007 and 2003 can link front ends to SharePoint 2010 Foundation, Server 2010 Standard or Enterprise Edition, or SharePoint Online lists. A SQL Server [Express] instance holds the data for SharePoint lists. Following are sub-topics of the Microsoft patterns & practices described in the MSDN Library’s SharePoint Guidance topic:

SharePoint Columns, Lists, and Content Types

Data models in SharePoint 2010 are implemented using columns, lists, and content types. A full understanding of these constructs underpins every effective data model in SharePoint 2010.

List Relationships in SharePoint 2010

SharePoint 2010 allows you to create relationships between lists in the same site collection. List instances are related through lookup columns (as known as lookup fields). The real benefit of this functionality is that SharePoint 2010 allows you to use join statements, in LINQ to SharePoint or in collaborative application markup language (CAML), to query across lists where lookup column relationships are defined. By default, SharePoint permits a maximum of eight joins per query, although administrators can change this limit through the Central Administration Web site or by using PowerShell. However, queries that contain large numbers of join statements are resource-intensive, and exceeding eight joins per query is likely to have a significant detrimental effect on performance.
Although this newly introduced ability to query across lists brings the capabilities of SharePoint data models closer to those of relational databases, SharePoint support join predicates only where a lookup column relationship exists between the lists. In this regard, the join functionality in SharePoint is less powerful than the JOIN predicate in SQL.

Query Throttling and Indexing

A familiar challenge when you work with SharePoint lists is how to address the performance degradation that can occur when your list contains a large number of items. However, SharePoint is capable of managing extremely large lists containing millions of rows. The often-quoted limit of 2,000 items per list, actually refers to the maximum number of items that you should retrieve in a single query or view in order to avoid performance degradation. Effective indexing and query throttling strategies can help you to improve the performance of large lists.
Note: For more information about working with large lists, see List Patterns, "Designing Large Lists and Maximizing Performance" from Performance and capacity test results and recommendations on TechNet and Handling Large Folders and Lists on MSDN.

Data Platform Improvements in SharePoint 2010

The Access Team’s Data Platform Improvements in SharePoint 2010 post of 2/15/2011 describes changes to SharePoint lists that apply to their use as linked Access 2010 back ends:

Database Relationships, Unique Columns and validation rules make it easy to validate your data. This creates much more maintainable applications and helps users to do the right thing the first time.
Some of the feedback we received from blog readers and developers for Access/SharePoint integration was to have better data platform features to ensure the data integrity they expect from a relational database.
In SharePoint 2007 validation such as required fields and min/max values are only enforced through the browser user interface. This means that applications talking to SharePoint via the SOAP Web services or the Object Model will be able to bypass those rules.
The data platform improvements we included in SharePoint 2010 are enforced at the at the data layer to make sure that no matter how an application/user interacts with the backend lists (Browser, Office Client, Web Service, Object Model), the rules will be enforced.
List Relationships

In SharePoint you can create lookups between lists, in 2010 lookups can enforce cascade delete and restrict delete relationships between the two lists.
In Access you can use the lookup wizard to set up a relationship.

You can use this to create simple 1:Many or Many:Many relationships between lists that are enforced at the data layer.
TIP: Use the lookup wizard to create SharePoint supported relationships in Access 2010. If you are working against a published web database—you will need to sync the table after creation before you can create lookups
TIP: Web Databases do not support relationship view. So if you want to see a view of all your relationships use the database documenter or create a dummy query with all the tables.
Unique Column Constraints

You can use unique constraints to make sure that duplicate values are not entered in specific column. Multiple unique constraints can be defined on a table and they allow for NULL values.
You can use these to enforce rules such as making sure an order number is unique across all orders. These constraints are enforced at the data layer.
The property is set through the Table Tools | Fields | Field Validation | Unique check box in the ribbon.

Tip: Unique columns are not supported for attachments, table based lookups (foreign keys), multi-value lookups, memo, hyperlink and Boolean fields.
Data Validation

As in Access, validation can be enforced at the table/list level and at the field/column level. You can supply an expression that must be true whenever you enter or change data. This allows a developer to validate data before it goes into the database. If the condition is not true the developer can provide a customer error message. The properties are set through the Table Tools | Fields | Field Validation | Validation dropdown in the ribbon.

When you are in SharePoint you use Excel expressions to create your rules but in Access you continue to use the familiar Access expressions and we handle the translation back and forth from the server.
In the browser we remote simple expressions to JavaScript to notify the user of any issues before they try to commit the record. This creates a more responsive user experience and reduces calls to the server.
This is our Country field in the browser

If I enter invalid data and tab off the field is automatically highlighted

When I place my focus back into the field the validation message is displayed so that I know how to correct my issue.

More advanced expressions like the sample below get evaluated at save and produce an error prompt.

Tip: You can use NOT, <>, <, >, <=, >=, IN, BETWEEN, LIKE (?, *, # as wildcard characters) and IS NOT NULL as comparisons on your expressions. Table and field validation rules are enforced at the data layer.
One of our testers and author of Access 2007 Inside Out, Jeff Conrad, sent me this great sample;

In the EmailAddress field, we want to be sure the e-mail address provided by the user appears to be a valid e-mail address. We can verify the e-mail address meets most standards of valid syntax by using a combination of the LIKE operator and wildcard characters in a field validation rule. In the blank text box at the top of the Expression Builder dialog box, type Is Null OR ((Like "*?@?*.?*") AND (Not Like "*[ ,;]*")) for the field validation rule.

This field validation rule ensures that every e-mail address provided by the user starts with at least one character followed by the @ symbol, contains at least one more character following the @ symbol, and contains the dot symbol followed by at least one more character after the dot symbol.
Also, this field validation rule does not allow a space, a comma, or a semi-colon anywhere in the email address.”

Enforcing SharePoint validation at the data layer

SharePoint also has a couple more validation options for required fields and min/max values for numbers. For Access Services lists we enforce those at the data layer as well.
Tip: There is a list OM property called SPList.EnforceDataValidation that gets or sets a Boolean value specifying whether required fields and min/max values are enforced at the data layer. You can set this on any SharePoint 2010 list to get this behavior.
More Reading

List Relationships and Unique Columns from the SharePoint perspective(http://sharepoint.microsoft.com/blogs/GetThePoint/Lists/Posts/Post.aspx?ID=316)
More info on data validation in general for Access from 2007 (http://office.microsoft.com/en-us/access/HA100963121033.aspx)

Leveraging Excel and Access Services in SharePoint Online

MSDN’s Leveraging Excel and Access Services in SharePoint Online page provides links to hands-on labs and videos for Access Services and Web Databases:

Excel and Access Services provide powerful features for building SharePoint Online solutions. In this session, you’ll get an inside look at both Excel and Access services and how each can be accessed programmatically when building SharePoint Online solutions.
Hands-On Labs

Excel Services
In this lab you will learn how to add Business Intelligence (BI) capabilities to your SharePoint Online site using Excel Services.

Access Services
In this lab, you will learn how to use SharePoint Access Services to publish an existing Microsoft Access database to SharePoint Online. Doing so will create a new SharePoint web and convert Access tables into SharePoint lists, and Access forms into corresponding HTML and CSS. You will then use this database as a bug tracking database. You will learn how to proactively log exceptions in SharePoint Online.

Videos

Leveraging Excel and Access Services in SharePoint Online
Excel and Access Services provide powerful features for building SharePoint Online solutions. In this session, you'll get an inside look at both Excel and Access services and how each can be accessed programmatically when building SharePoint Online solutions.

Leveraging Access Services in SharePoint Online
In this video, you'll learn how to build Access solutions for the cloud in SharePoint Online.

Leveraging the Excel REST API
In this video, you'll learn how to access Excel data and artifacts such as charts via the Excel REST API when using SharePoint Online.

Note: Office 365 Small Business’ P1 subscription includes Access Online but not Excel Online.

Microsoft SharePoint Online Developer Guide (Beta)

The Microsoft SharePoint Online Developer Guide (Beta) is a 44-page PDF File that the Overview describes as follows:

This guide walks you through some of the rich features that are available to developers and designers in SharePoint Online in Office 365. It provides an overview of the feature set and extensibility points for SharePoint Online, and a discussion of how to create solutions for this new environment. This guide begins by describing the types of solutions you can build, and then addresses the developer tools for SharePoint 2010, the new platform features, and the solution deployment architecture

SharePoint Online’s Enterprise and Storage Features

SharePoint Online’s Small Business (P1) and Information Workers (E3/E4) plans offers most of features of SharePoint Server 2010 Standard Edition plus Access Services, which are required to generate Web Databases from Access databases. The E3/E4 plans include Excel Services, Forms Services and InfoPath Services. Kiosk Workers in plans K1/K2 and Information Workers in plan E1/E2 don’t have Access, Excel, Forms or InfoPath Services. All plans include an initial 50 GB of online storage, plus 500 MB for each additional licensed user.

For more information on what’s included in the five different beta licensing plans, download the “Microsoft Office 365: Microsoft SharePoint Online Beta Service Description” as Microsoft_SharePoint_Online_Standard_Beta_Service_Description_Final.docx from the Office 365 Beta Service Descriptions link. From the Beta Service Descriptions:

Service Availability

Like all Microsoft Online Services offerings, SharePoint Online has 99.9-percent scheduled uptime with financially backed SLAs covering any instance when this service availability standard is not met.

Note

The SLA is not offered during the Office 365 Beta period.

Deleted Item Recovery

SharePoint Online users have a Recycle Bin where deleted content is stored. They can access the Recycle Bin to recover deleted documents and lists if they need to. Items in the Recycle Bin are retained for 30 days. The following data types are captured by the Recycle Bin:

Lists
Libraries
Folders
List items
Documents
Web Part pages

The following data types are not captured by the Recycle Bin:

Deleted Sites
Deleted web pages
Site customizations made through SharePoint Designer 2010

Data Backup

Data protection services are provided to prevent the loss of SharePoint Online data. Backups are performed every 12 hours and retained for 14 days. Note that this describes the data backup services as offered when SharePoint Online is generally available.

Note

The data protection services policy may be different than described during the Beta period

Service Continuity Management

SharePoint Online is hosted in Microsoft-managed, enterprise-level data centers that are designed to operate highly available online services. Because of this, the Microsoft SLA with SharePoint Online subscribers is set at 99.9 percent uptime.
However, service availability can be affected by hardware failures, natural disasters, and human error. To address this, SharePoint Online offers service continuity management, a process for managing risks to ensure that a company’s IT infrastructure is capable of providing continuing services if normal availability solutions fail. Service continuity management for SharePoint Online includes provisions to quickly recover from such unexpected events.
Two metrics commonly used in service continuity management to evaluate disaster recovery solutions are a recovery time objective (RTO), which measures the time between a system disaster and the time when the system is again operational, and a recovery point objective (RPO), which measures the time between the latest backup and the system disaster, representing the nearest historical point in time to which a system can recover. SharePoint Online has set an RPO and RTO in the event of a disaster:

12-hour RPO: Microsoft protects an organization’s SharePoint Online data and has a copy of that data that is equal to or less than 12 hours old.
24-hour RTO: Organizations will be able to resume service within 24 hours after service disruption if a disaster incapacitates the primary data center.

Virus Filtering

Forefront Security for SharePoint is included with SharePoint Online to help protect the SharePoint Online environment from viruses while maintaining uptime and optimizing performance.
Forefront Security for SharePoint provides comprehensive protection for SharePoint document libraries using multiple scan engines and content controls to help eliminate documents that contain malicious code.

Understanding Office 2010’s SharePoint Workspace

TechNet’s SharePoint Workspace 2010 overview topic of 5/12/2010 describes Office 2010’s Sharepoint Workspace feature as follows:

This article describes the key functionality and architecture of Microsoft SharePoint Workspace 2010. SharePoint Workspace 2010 is the new name for and succeeds Microsoft Office Groove 2007. SharePoint Workspace 2010 is a client application that provides fast, any-time interactive access to document libraries and lists on Microsoft SharePoint Server 2010 and Microsoft SharePoint Foundation 2010. SharePoint Workspace 2010 also provides options for creating Groove peer workspaces and Shared Folder workspaces. SharePoint Workspace 2010 is more versatile than Microsoft Office Groove 2007 and can be integrated with Microsoft SharePoint Server 2010 or can run independently.

Microsoft SharePoint Workspace 2010 provides a client for Microsoft SharePoint Server 2010 and Microsoft SharePoint Foundation 2010 that enables real-time synchronization of desktop content with SharePoint documents and lists. SharePoint Workspace 2010 also provides options for creating Groove collaboration workspaces and synchronized shared folders. By using SharePoint Workspace 2010, information workers can easily synchronize online and offline content with a designated SharePoint site or collaborate with external partners and offsite team members through shared workspaces. SharePoint Workspace 2010 is included with Microsoft Office Professional Plus 2010. …

The article continues with “Key features of SharePoint Workspace 2010.”

Creating SharePoint workspaces from Office 365’s SharePoint Online is disabled in Safe Mode.

Technorati Tags: Access 2010,SharePoint 2010,Office 365,SharePoint 2010 Online,Linking SharePoint Lists,Access Services,Access Web Databases

Office 2010 SP1 on track for late June

2011-05-19T09:33:00.001-07:00

Ryan McMinn posted Office 2010 SP1 on track for late June to the Microsoft Access blog on 5/17/2011:

We're on track to deliver Office 2010 SP1 in late June 2011 (here's yesterday's official announcement). We definitely recommend this update for all Office 2010 users. There has been great work put into this service pack that further enhances the performance and security of Access. Here are a couple of highlights:

Fixed issue in export to Excel to make sure it always export the data based on the current view.
Improved the performance of publishing client forms from Access with embedded images.

Additionally, if you're one of the few 64-bit Access users working with compiled Access databases (ACCDE, MDE, and ADE files), be sure to check out this KB article that details an update process you'll need to go through to have your files work properly with SP1.
Let us know what you think!
-- Ryan McMinn, Senior Program Manager Lead

Technorati Tags: Microsoft Office 2010 SP1,Microsoft Acess 2010 SP1,Access 2010 SP1

Access to SQL Server Migration: Understanding SSMA Project Settings

2011-05-19T09:23:00.001-07:00

Bill Ramos published Access to SQL Server Migration: Understanding SSMA Project Settings on 5/17/2011:

In many cases, the Migration Wizard for Access will successfully migrate your Jet based Access solution to SQL Server. You may find a need to change your project options for the desired result. In this blog, I’ll describe how to use the project options available in the SQL Server Migration Assistant (SSMA) for Microsoft Access. This blog builds on Access to SQL Server Migration: How to Use SSMA using the Access Northwind 2007 template. In addition, I’m using SSMA for Access v.50 that is now available for download.
Getting Started with Project Options

For all versions of SSMA, there are two levels of project options: Default Project Settings and Project Settings. Changing the Default Project Settings affects subsequent projects that you create in SSMA. These settings can be overridden using the Project Settings option from the Tools menu and saved with your project. If you are using the Migration Wizard and you want to control the project settings, you should use the Default Project Settings dialog before running the wizard.
New with SSMA for Access v.50, the Default Project Settings has a Migration Target Version that allows you to select SQL Server 2005, SQL Server 2008 (the default), SQL Server “Denali” (the next major release of SQL Server) or SQL Azure. The global options you set will be specific to the target you select when you create a new project.

Project Options for SQL Azure

If you are migrating to SQL Azure, an extra page is available as shown below.

The Heartbeat Interval allows you to adjust the time interval that SSMA uses to keep the project connection alive to the SQL Azure database. The SQL Azure Server Suffix value allows you to adjust the server name suffix for connecting to your SQL Azure server.
Using the Mode Option for Controlling Conversion and Migration Project Settings

Before you go about making changes to your settings, SSMA provides four ways to control the settings used for the Conversion and Migration project settings that you can select using the drop down list control shown below.

The four options are called Default, Full, Optimistic and Custom. These modes have to do with the level of tolerance that SSMA applies to the settings. When you change the value, all of the values are adjusted to one of the three default settings. If you make any changes, from one of these default settings, SSMA changes the mode to Custom. If you want to revert your custom settings back to one of the three default modes, just select the desired node from the drop down.
Impact of Mode Settings for Conversion Options

The following table shows the impact of the conversion options settings based on the mode you select.

With regards to the above options, here are a few points to consider.

Add primary key. This is generally a good option to have set true to make sure that you can uniquely identify records for update and delete actions. SSMA will provide you a notification for tables that it adds a primary key to so that you can double check the result.
Warn when a complex query uses a wildcard. This option is generally good to consider setting as true to help in limiting the data based between SQL Server and Access. You will need to look at the objects like forms and reports that refer to the query to specify the columns really needed for your application.
Add timestamp column. This option helps applications interacting with the table to implement optimistic concurrency. The timestamp data type is marked for deprecation and is replaced by the rowversion data type. SSMA still uses the old timestamp data type. You can still use the timestamp data type for SQL Server code named “Denali” so there is no need to update change to rowversion for now.

Impact of Mode Settings for Migration Options

The following table shows the impact of the data migration settings based on the mode you select.

The mode setting does not impact the options for Replace unsupported dates and Batch size.
With regards to the Keep identity option, I recommend always keeping this set to True to make it easier to validate the result with primary and foreign key values for related tables.
Loading Objects Project Settings

There are five settings as shown below for controlling how SSMA attempts to load objects into SQL Server and for synchronizing your project as shown below.

You can generally stick with the default options for Synchronization for SQL Server items.
The Attempts option may need adjustment if you have complex foreign key relationships. SSMA loads objects into SQL Server typically using multiple passes. Objects that fail to load in the first pass, such as foreign keys, might successfully load in the next pass. You may need to increase this value for your Project Settings if SSMA reports errors while loading objects.
GUI and Type Mapping Options

The GUI tab allows you to configure the number of rows that SSMA displays using the data tab for the source and target tables. In general, there is little need to change these default values. Just be aware of them when you are comparing data using SSMA that you will only see the first 100 rows by default.
For information about the Type Mapping options, please refer to the blog post “Access to SQL Server Migration: Understanding Data Type Conversions”.
Additional Resources and References

For specific details on each of the project options, please refer to the Project Settings help topics for SSMA for Access.
Optimizing Microsoft Office Access Applications Linked to SQL Server http://msdn2.microsoft.com/en-us/library/bb188204.aspx describes in detail many of the techniques that were only mentioned briefly in this blog post.
The UtterAccess forum has a discussion group dedicated to SQL Server questions http://www.utteraccess.com/forum/Microsoft-SQL-Server-f32.html. You can post questions and get answers from Access experts who work with SQL Server on a daily basis.
The FMS Upsizing Resource Center contains links to various resources http://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/index.html.
JStreet Technology's developer downloads section http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp has useful tools and presentations to help you get up to speed.

Technorati Tags: Microsoft Access 2010,Microsoft Access,Access 2010,SQL Server,SQL Server 2008 R2,SQL Server Migration Assistant for Access,SQL Server Migration

Office Productivity Alternatives: Low Adoption Numbers Don’t Tell The Full Story

2011-05-10T11:34:00.001-07:00

One of Microsoft Access’s keys to success has been the inclusion of Access 95 and later in the Microsoft Office Professional suite. Forrester Research’s Matthew Brown (pictured below) posted Office Productivity Alternatives: Low Adoption Numbers Don’t Tell The Full Story on 5/10/2011:

My colleague Phil Karcher completed some nice research into the state of Microsoft Office alternatives recently. I'll share a couple of the highlights here.
Competitors to Microsoft Office receive plenty of attention in the blogosphere these days. Whether it’s Google announcing a new mobile or social feature in Docs; Zoho a new API partner; or the recent buzz around the future of Open Office without Oracle -- it’s natural to wonder how much traction these applications are getting with corporate IT.

Open Office has a global presence, although predominantly in government and education. Google Apps for Business has a growing list of customers, although many are using Gmail, not Docs. Overall, alternatives’ take of the office productivity pie — particularly in large enterprises — is still very small.

Yet, we hear from many organizations considering or piloting them. In fact over a third of respondents to our March survey of IT decision makers with influence over the productivity toolkit claim to be “actively looking at” or “piloting” alternatives. So why does adoption remain so paltry?

Web-based alternatives get the lionshare of interest. Many try, far fewer buy. Despite interest (44% are "somewhat interested" in Web-based tools), a quarter of IT pros tell us they're actively looking or piloting, and a paltry 3% claim they've implemented Web-based alternatives. Those same IT buyers are concerned with numerous obstacles to broader deployment, particularly user acceptance and compatibility with Microsoft Office file formats.
Alternatives are true replacements for only small segments of the workforce. Of the respondents to our survey who have deployed an office productivity alternative at their company, every one also supports some version of Microsoft Office — reinforcing the view that alternatives actually serve as replacements for specific segments of the workforce (e.g., browser-based alternatives for users with basic needs, WordPerfect for legal pros). For other segments, they're complementary tools used to enrich older versions of Microsoft Office with collaboration features. The alternatives vendors themselves acknowledge that they don't seek to fully displace Microsoft on the desktop.
The real story: alternatives represent leverage in MS Office upgrade decisions. So what is the real impact on Microsoft? While their market presence remains low, the alternatives' impact on Microsoft Office upgrade cycles is more significant. With more choice, Forrester finds productivity decision-makers delaying Office upgrades as they evaluate the alternatives as part of the sourcing process. Increasingly, we find that clients are using the very presence of alternatives as a way to gain leverage in Office upgrade negotiations. We expect the role of office alternatives in the market only to grow as vendors make improvements to close the functionality and compatibility gap, and as buyers continue to show interest in cutting costs by provisioning differently to different types of users.

What's your take? Will Office alternatives ever gain a material foothold in the enterprise market?

No Office competitor has a RAD database application that even comes close to Access’s capabilities and ease of use. Of course, an issue is the relatively low percentage of Professional versus Standard editions sold.

Technorati Tags: Microsoft Access 2010,Microsoft Access,Access,Open Office,Google Apps,Zoho

Microsoft Office Access and Cloud Computing

2011-05-10T08:03:00.001-07:00

Srinivasan Sundara Rajan described “Business productivity thick client cloud applications” in a 5/10/2010 Microsoft Office Access and Cloud Computing post to azure.ulitizer.com:

Microsoft Office Access is one of the most popular thick client / client server application development tool that is prevalent in the enterprises today. Its simplicity to use, fitment for quick departmental applications and ability to be home grown by the business users without dependency on IT and still a poor man's adhoc reporting tool, all makes MS Access a permanent place in most enterprises.
The latest incarnation of this application development and reporting tool has the following benefits:

Build a relational database for small applications much faster using wizards
User interface forms and reports using a common look and feel of Microsoft Office
Automation features to avoid manual coding
Ability to act as Information Integration Engine which can connect disparate data sources

Mostly Microsoft Access is used as a desktop thick client application and with increased usage of the Cloud Platform it would be helpful to analyze the options for Microsoft Office in Cloud.
Microsoft Access In Office 365
From older version of Microsoft Access we can move the data to Microsoft Access 2010, and then publishing the database to Access Services on Office 365, you can make your data accessible to all of your colleagues wherever they are. Perhaps more importantly, your database will be better able to grow as your needs change.
We can use Access 2010 and Access Services, a new component of SharePoint, to build web database applications. Access 2010 and Access Services provide a platform for you to create databases that you can use on the Web. You design and publish a web database by using Access 2010 and SharePoint, and people who have SharePoint accounts use the web database in a web browser.
When you publish a web database, Access Services creates a SharePoint site that contains the database. All of the database objects and data move to SharePoint lists in that site.
SSMA to SQL Azure
Microsoft SQL Server Migration Assistant (SSMA) is a toolkit that dramatically cuts the effort, cost, and risk of migrating from Access to SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Azure. SSMA 2008 for Access also supports migrating to SQL Azure.
SSMA provides a Wizard-like interface that allows us to:

Create a Migration Project in SSMA with SQL Azure as the destination
Allows us to choose the target schema for migration
Migrate the tables and data with the convert , Load and Migrate command
Provides ability to verify the migration

This tool provides an out-of-the-box solution for migration of Microsoft Access databases to Cloud.
Microsoft Access As Thick Client Cloud Consumer
If the data resides in SQL Azure, then Microsoft Access can continue to be a thick client and connect to a Cloud database using the linked tables option.
With Microsoft Access 2010, You can create an external data connection to a Web Service. For example, you can create a Web Service data connection to your enterprise Business Data Catalog and get business data. After you create a Web Service data connection, you can link to it as you would any other external data source. If the Web Service accepts parameters, you can change the parameters when you create a linked table by using the Web Service data connection.
The following are the general steps in making Microsoft Access consume Cloud Web Services as a thick client.

Obtain a Web Service data connection file
Install the web service data connection by using the connection file
Create a linked table by using the installed web service data connection

Summary
Microsoft Office Access continues to be most productive end user reporting tool and in the past decade numerous enterprise class applications have been written using that tool.
We cannot discount the immense value Microsoft Access applications brought to end user analytics, ad hoc reporting and complex client side calculations. The biggest draw back for the Microsoft Access applications have been hitherto its storage whereby the Jet engines which is the underlying database have historically known for space limitation.[*]
While over the period the limitations have been reduced to a great extent, the above mentioned Cloud Migration options makes Microsoft Access continuously play a role in the enterprise end user productivity applications.
With the support for Microsoft Office mobile applications, having Microsoft Access as a thick client which can consume cloud data may be a worth while option for future productivity applications.

Srinivasan Sundara Rajan works at Hewlett Packard as a Solution Architect.

* Historically, problems have been encountered with database corruption with more than about 20 users updating records simultaneously, not “space limitations.”

For more details about Access 2010 Web databases and as a RAD front-end for Microsoft’s SQL Azure cloud database, see my Three Microsoft Access 2010 Webcasts Scheduled by Que Publishing for March, April and May 2011 post updated 5/3/2011.

Three Microsoft Access 2010 Webcasts Scheduled by Que Publishing for March, April and May 2011

2011-05-03T07:28:00.000-07:00

Updated 5/3/2011: Added links to a downloadable Northwind.mdb file for upsizing to SQL Server or SQL Azure with the SQL Server Migration Assistant (SSMA) for Access and a NwindAzure.mdb front-end for a publically accessible SQL Azure cloud database. Changed date and title of the 5/23/2011 Webcast to Moving Access Tables to SharePoint 2010 or SharePoint Online Lists and added a “Web Databases with SharePoint Online” topic.

Updated 3/30/2011: Changed from WebEx to pre-recorded Webcast format. The first Webcast is available on-demand by clicking the headline below and completing the registration form.

Over this and the next two months, I’ll present the following free 45-minute to one-hour Webcasts about advanced Microsoft Access topics for Que Publishing:

Upsizing Access 2010 Projects to Web Databases with SharePoint 2010 Server

Presented by Roger Jennings
Tuesday – March 29, 2011 ~~– 12:00 pm ET~~ Prerecorded – Available on Demand

Microsoft Access 2000, 2002 and 2003 offered intranet connectivity by means of Access Data Pages (DAP), which Access 2007 and 2010 no longer support. Access 2010 introduces the capability to easily publish Access *.accdb applications to Web Databases that run on SharePoint Server 2010’s Access Services.

If your organization doesn’t have a SharePoint Server 2010 license, you can subscribe to low cost, on-demand instances provided by Office 365 or independent Microsoft partners, such as AccessHosting.com. Use SharePoint security to establish read and read/write access to your on-premises or hosted project. In this Webinar, you’ll learn how to:

Generate a Web Database-compatible application from an Access template
Modify an existing Access app to comply with Web Database requirements
Subscribe to trial Office 365, AccessHosting.com, or both hosting services
Publish your application to on-premises or hosted services with a wizard
Establish role-based authentication and authorization security for intranet, Internet, or both types of users

Register here to watch the 45-minute Webcast.

Update 3/30/2011: Problems with image size/quality and the missing player slider have been fixed.

Linking Access tables to on-premise SQL Server 2008 R2 Express or SQL Azure in the cloud

Presented by Roger Jennings
Tuesday – April 26, 2011 – ~~12:00 pm ET~~ Prerecorded – Available on Demand

Microsoft SQL Server has been the preferred back-end database for heavy-duty, multiuser Access applications since Access 2000 introduced the capability to link to the Microsoft Data Engine (MSDE). Microsoft Access 2007 and 2010 don’t support user- and group-level security for new multi-user database projects, so it’s more important than ever to use the SQL Server Migration Assistant (SSMA) for Access to move to SQL Server databases, which maximize data accessibility, reliability and security.

If you don’t want the burden of maintaining and backing up SQL Server databases, the SSMA lets you migrate directly to remote SQL Azure instances running in Microsoft data centers.

In this Webcast, you’ll learn how to:

Download and install no-charge SQL Server 2008 R2 Express and SQL Server 2008 R2 Management Studio (SSMS) Express to manage on-premises database back ends
Download and install the SQL Server team’s free SSMA, which substitutes for Access’s Upsizing Wizard in linking scenarios
Use the SSMA to link existing Access queries, forms, and reports to a SQL Server 2008 R2 Express database.
Establish security with user logins and database roles you create in SSMS.
Obtain a 30-day free trial subscription to a 1-GB SQL Azure Web database that you can convert to a $9.99 per month paid subscription
Use SSMA to create a SQL Azure server with a trial subscription.
Use the SSMA to link existing Access queries, forms, and reports to the trial SQL Azure database running in a Microsoft data center.
Use SSMS to add security with SQL Azure user logins and database roles.

You can download Northwind.mdb for upsizing to SQL Server and SQL Azure from my Windows Live Skydrive instance here.

You also can download NwindAzure.mdb, which is a Access front-end for tables linked by a publically accessible Northwind SQL Azure database, here. Running NwindAzure.mdb will open a connection to the Northwind database running in Microsoft’s South Centeral US (San Antonio, TX) data center.

Register here.

Moving Access Tables to SharePoint 2010 or SharePoint Online Lists

Presented by Roger Jennings
Monday – May 23, 2011 – Prerecorded – Available 12:00 pm ET

The Microsoft Access Team recommends using SharePoint lists as the shared data source for secure multi-user queries, forms and reports. If you don’t have access SharePoint Services v3 for Windows Server 2003 [R2] or SharePoint Foundation 2010 for Windows Server 2008 [R2], your network administrator can install one of these no-charge add-ons in a few minutes. In this case, your IT department is responsible for backing up and managing your data.

Alternatively, you can subscribe to Microsoft’s low-cost Office365 (formerly Business Productivity Online Suite, BPOS), which now includes a hosted SharePoint instance, or to an instance provided by a Microsoft partner. In this case, the provider assures your data is backed up and always available. The SharePoint administrator manages read and update permissions for data. In this webcast, you’ll learn to:

Create a SharePoint-compatible application from an Access template
Modify an existing Access app to replace relationships with SharePoint lookup tables
Subscribe to trial Office 365 SharePoint hosting services
Move your application’s tables to hosted SharePoint services with a wizard
Synchronize locally-stored (cached) copies with the master SharePoint list data
Establish role-based authentication and authorization security for intranet users
Upsize Access applications to Web Databases with SharePoint Online.

Register here.

About the Author

Roger Jennings is an author and consultant specializing in Microsoft .NET, SQL Server, and Access database applications, as well as Windows Azure and SQL Azure cloud computing projects. He was a technical beta tester for all 10 editions of Microsoft Access; SQL Server 6.5, 7.0, 2000, 2005, 2008, and 2008 R2; every release of Visual Basic since version 2.0 and Windows 3.1; and all subsequent Microsoft Windows operating systems. He also was one of the founding members of Microsoft’s former Access Insiders group. Roger is a principal of OakLeaf Systems, a Northern California software consulting firm. He’s the author of many Que titles such as Microsoft Access 2010 In Depth and the curator of Roger Jennings’ Access Blog and the OakLeaf Systems blog.

Chris Mayo’s Recent SharePoint Online Beta Posts

2011-04-24T11:23:00.001-07:00

Now that Office 365, which includes Access Services, is available in a public beta, I’ll be including articles on SharePoint Online topics in this blog.

Note: I’ll cover linking Access tables to SharePoint Online lists and upsizing to Access Web Databases using SharePoint Online in my Moving Access Tables to SharePoint 2010 and SharePoint Online Lists Webcast of 5/10/2011. See my Three Microsoft Access 2010 Webcasts Scheduled by Que Publishing for March, April and May 2011 post for more details.

SharePoint Online for Office 365: Developer Guide (4/21/2011)

The SharePoint Online team has released the SharePoint Online for Office 365: Developer Guide to provide a comprehensive overview of the SPO platform and how developing for SPO differs from SP2010. Give it a read, it’s a great reference.

Chris is a Microsoft technology specialist focusing on Office 365 and SharePoint Online.

Visual Studio 2010 SharePoint Power Tools for SharePoint Online (4/21/2011)

Developing solutions for SharePoint Online requires they be built using Sandboxed Solutions . Sandboxed solutions are developed against a scoped version of the Microsoft.SharePoint API specific to the capabilities of the sandbox.

While Visual Studio 2010 provides IntelliSense support for the sandbox, compilation support would be even better (so you can catch issues with code copy/pasted from other projects or online samples, etc.).

Enter the Visual Studio 2010 SharePoint Power Tools. These tools provide sandboxed solution compilation support and a Visual Web Part (Sandboxed) project item template so you can create Visual Web Parts that will run in the Sandbox.

Download and install the tools today. They are a huge time saver for SharePoint Online and sandboxed solutions development.

Building a SharePoint Development Machine Using the Easy Setup Script on SharePoint Sideshow (4/19/2011)

Now that the Office 365 Training Course is available for the Office 365 beta, I’m putting together a blog post on setting of a development environment for Office365. A good place to start is either setting up a SharePoint VM , using the 2010 Information Worker VMs ( native boot is a great way to go for VMs), or using the new SharePoint 2010 Easy Setup Script . The Easy Setup Script installs all the SharePoint 2010 development components either locally of onto a native boot VHD without a lot of manual labor. [Click for] details on the SharePoint Sideshow episode and video!

Couple of interesting Office 365 articles (4/29/2011)

Interesting article on the economics of Office 365 on PC World and another on the marketplace in the LA Times.

New Office 365 Developer Training Course on MSDN (4/28/2011)

Since we announced Office 365 around the same time as PDC last year, I’ve been talking to a lot of other developers about the platform opportunities for building solutions for Office 365. In those discussions, the most common question I get is “how is the SharePoint Online/Exchange Online/Lync Online platform different from the SharePoint 2010/Exchange 2010/Lync Server 2010 platform?”. In order to answer those questions and help you build solutions for the cloud with Office 365, I’m really happy to announce the release of the Office 365 Developer Training Course on MSDN. This training course includes 7 modules with over 10 hours of video and 17 hands-on-labs to help you get started building cloud based communication and collaboration solutions for Office 365, including:

Developing in the Cloud with Office 365

Office 365 provides a communication and collaboration service in the cloud that you can leverage to build custom solutions for SharePoint Online, Exchange Online and Lync Online. In this session, you’ll learn about this new cloud service and the breadth of solutions that can be developed using the same skills, tools and SDKs you use today when building on-premises solutions.

Developing for SharePoint Online with Sandbox Solutions

Sandboxed Solutions are the development paradigm for SharePoint Online. In this session, you’ll learn about sandboxed solutions including how to develop, debug and deploy solutions. You’ll also learn the breadth of solutions that can be developed in the sandbox and strategies for developing common scenarios that are not enabled in the sandbox.

Building Workflow Solutions for SharePoint Online

Building Workflow solutions for SharePoint Online allows you to automate collaboration-centric business processes and surface them to your users via SharePoint Online. In this session, you’ll learn the differences between declarative and code-based workflows, design workflow using Visio 2010, implement that workflow in SharePoint Designer 2010 and customize the workflow using Visual Studio 2010 and custom actions.

Developing SharePoint Online Solutions with the Client Object Model

The SharePoint Client Object Model provides libraries for programmatically accessing SharePoint Online via Silverlight and JavaScript. In this session, we’ll go deep into the Client Object Model and show you how to develop solutions using both Silverlight and JavaScript.

Leveraging Excel and Access Services in SharePoint Online

Excel and Access Services provide powerful features for building SharePoint Online solutions. In this session, you’ll get an inside look at both Excel and Access services and how each can be accessed programmatically when building SharePoint Online solutions. [Emphasis added.]

Developing Communication Solutions for Lync Online

In this session, you learn how to integrate Lync features into your WPF and Silverlight clients much in the same way that Office and SharePoint do, including presence, contact lists and click-to-communicate features. You will also learn how to extend Lync communications to include data and features from your client applications much in the same way that Outlook 2010 does with the
"IM" and "Call" features within an email.

Developing Messaging Solutions for Exchange Online

In this session, you'll learn how to integrate Exchange Online mailbox data such as mail, calendar and task items as well as Exchange Online services such as the free-busy service into your applications using an easy to discover and easy to use managed API.

To use this training course as self-paced training, you’ll need to do the following:

Download and configure the 2010 Information Worker Demonstration and Evaluation Virtual Machine (RTM) to provide you a local development environment for SharePoint if you don’t already have one configured.
Download and install the Office 365 Developer Training Kit onto the 2010a VM.
Sign up for the Office 365 Beta to receive access to the service.
Visit the Office 365 Training Course on MSDN to watch the videos and get started with the labs and demos.

As always, I really appreciate your questions/feedback. Please ping me via the Email Blog Author link!

More Beta details on Microsoft Office 365 Blog (4/18/2011)

More details on the beta can be found on the Microsoft Office 365 Blog. This is a great blog to mark if you’re working with Office 365.

Office 365 Beta on PC World and PC Magazine (4/18/2011)

Nice write ups on both PC World and PC Magazine.

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

Bibliography and Links for My “Linking Access tables to on-premise SQL Server 2008 R2 Express or SQL Azure in the cloud” Webcast of 4/26/2011

2011-04-24T07:52:00.000-07:00

Updated 4/24/2011 with SQL Server Migration Assistant (SSMA) for Access v4.2 migration log posts of 4/20/2011 and the following:

Download NwindAzure.mdb from my Windows Live Skydrive site to connect to a publicly accessible SQL Azure Northwind database running in Microsoft’s San Antonio, Texas data center from here.

Download Northwind.mdb for upsizing to SQL Server and SQL Azure from my Windows Live Skydrive instance from here.

The following Web-accessible articles address issues with and the history of topics covered in my Linking Access tables to on-premise SQL Server 2008 R2 Express or SQL Azure in the cloud Webcast for Que Publishing on 4/26/2011. The articles are listed in reverse chronological order and include an excerpt from their introduction or summary.

See Three Microsoft Access 2010 Webcasts Scheduled by Que Publishing for March, April and May 2011 of 3/30/2011 for more information on my two upcoming Microsoft Access 2010 Webcasts.

Output Logs for Successful Migration of Northwind.mdb to a SQL Server Northwind Database with SSMA for Access (to SQL Server or SQL Azure) blog post by Roger Jennings (4/20/1011):

Successfully migrating the tables of an Access 2003 Northwind.mdb sample database with the SQL Server Migration Assistant (SSMA) for Access v4.2 generates 325 log entries for 9 tables. You can download Northwind.mdb from my Windows Live Skydrive instance here.

A complete copy of the log file follows.

Output Logs for Canceled Migration of Northwind.accdb to a SQL Server Northwind Database with SSMA for Access blog post by Roger Jennings (4/20/2011):

Attempting to migrate the tables of an Access 2010 Northwind.accdb database created from the built-in Northwind template with the SQL Server Migration Assistant for Access v4.2 generates 670 log entries for 20 tables. The entries consist of 6 error, 245 warning, 233 information and 116 other messages.

Migration failed because of five instances of Access tables with unsupported Attachment field data types and one instance of an unsupported Lookup field data type. Many warnings are the result of the Access team’s inexplicable inclusion of spaces in field names.

A complete copy of the log file follows.

Access to SQL Server Migration: Access Solutions using SQL Server, Part 1 blog post by Mary Chipman (3/19/2011):

In Part 1 and Part 2 I’ll discuss your options for continuing to use Access as a front-end client once you have migrated the data to SQL Server. Some people come to SQL Server migration as Access developers who are new to SQL Server, whereas others may be IT professionals who know little about Access, so I will attempt to bridge any gaps with useful information for everyone. The SSMA Migration Assistant for Access (SSMA) makes it easy to link converted SQL Server tables and queries to the Access database you started from. For applications of any complexity, migrating the data is by far the easiest step. If your existing Access application suffered from intractable problems when the tables were in native Jet/ACE format, and you have done nothing to fix or at least understand those problems, then you are most likely going to have even bigger problems with SQL Server.
The Access client application has often been dismissed as a "toy" because of its ease of use, but when understood and used appropriately with SQL Server on the back end, those very features can help you quickly deliver cost-effective results with a low TCO. Access is not a suitable client for Web or n-tier applications that require a separate data access layer, although it can be used in conjunction with other clients, such as ASP.NET. There are always tradeoffs involved between ease of development, efficiency and security when using Access in a 2-tier, client-server scenario. You will need to determine which of the following techniques are best suited to your needs

Access to SQL Server Migration: Access Solutions using SQL Server, Part 2 blog post by Mary Chipman (3/19/2011):

In Part 2 I'll continue the discussion about how you can "tread lightly" on your SQL Server by making your Access front end an efficient and economical data consumer. I'll focus on techniques you can use to keep your application running efficiently, freeing up network and server resources in the process.

Upsize Microsoft Access Databases to SQL Azure with SSMA 4.2 blog post by Bill Ramos reposted to this blog (4/14/2011):

Bill Ramos explained Migrating Access Jet Databases to SQL Azure in a 4/14/2011 post:

In this blog, I’ll describe how to use SSMA for Access to convert your Jet database for your Microsoft Access solution to SQL Azure. This blog builds on Access to SQL Server Migration: How to Use SSMA using the Access Northwind 2007 template. The blog also assumes that you have a SQL Azure account setup and that you have configured firewall access for your system as described in the blog post Migrating from MySQL to SQL Azure Using SSMA.

Access to SQL Server Migration: Understanding Data Type Conversions blog post by Mary Chipman (3/6/2011):

In this blog post I’ll discuss how you can use the SSMA Migration Assistant for Access (SSMA) to refine data type mappings between Access and SQL Server, but first a little history lesson. You can think of Access as two separate products rolled into one: the relational database engine, Jet, consisting of tables and queries, and Access application objects, consisting of forms, reports, macros and modules. Access 2007 introduced the Access Connectivity Engine (ACE), replacing Jet. It is fully backwards compatible with earlier versions of Jet, but incorporates new data types and functionality for integrating with SharePoint. Access 2010 completes the SharePoint "vision", making Access a first-class client application for SharePoint. What this means is that little work was done on Access-SQL Server integration, so much of the information available online for data type mappings between Access 2007 and SQL Server 2005 is still valid. Many of the new types introduced in SQL Server 2005 and 2008 are not supported in any version of Access, and the new SharePoint-compatible types introduced in Access 2007 and 2010 are not supported in SQL Server. Hopefully by the end of this article you’ll be able to find the information you need to manage data type conversions between your Access/Jet/ACE application and SQL Server.

Access to SQL Server Migration: Handling Schema Migration Issues blog post by Mary Chipman (3/6/2011):

In this blog post I’ll discuss some of the data migration issues you may encounter when migrating your Access schema to SQL Server. These consist of issues that you should address prior to migrating your data, and others are behavioral differences that you should be aware of. In my experience, it’s better to take a proactive approach and solve any known issues sooner rather than later. Fixes and workarounds after the fact are always more expensive and time-consuming than doing things the right way to begin with. The simple fact is, Access shields you from much of the complexity involved creating in a database application, and once you leave its protective umbrella for SQL Server, you face an infinitely more challenging database engine with a much higher learning curve. You can use the assessment reports in the SSMA Migration Assistant (SSMA) to identify potential issues before you migrate your data, which will not only smooth data migration, but also simplify application development later on.

Access to SQL Server Migration: How to Use SSMA blog post by Mary Chipman (1/28/2011):

In this blog post I’ll walk you through the process of migrating the Access 2007 Northwind database to SQL Server using the SQL Server Migration Assistant for Access (SSMA). You can use SSMA to do all of the heavy lifting, migrating your tables and queries to SQL Server 2008 or SQL Server Azure. SSMA can also link your Access solution to the new SQL Server tables after you have migrated your data.

Migration for Microsoft Access from the Microsoft SQL Server 2005 Web site (current):

Microsoft Access is a database intended for small projects with a few users. When a database grows large, and more users need to work with it, you are ready to move up to the more robust and secure database solution provided by Microsoft SQL Server. This page provides the tools and resources to help you simplify the process of migrating Access to SQL Server. For more information, read Microsoft Access or SQL Server 2005: What's Right in Your Organization? [Emphasis added.]

Remember that it’s the SQL Server team talking when you read the first sentence above.

Optimizing Microsoft Office Access Applications Linked to SQL Server by Andy Baron (11/2006):

Microsoft Office Access supports three primary options for connecting to data stored in Microsoft SQL Server databases:

Use the Office Access database engine—originally called the Jet database engine—to communicate with SQL Server over ODBC connections.
Create Office Access Project applications that use an OLE DB connection to communicate with SQL Server.
Write Microsoft Visual Basic for Applications (VBA) code that uses DAO, ActiveX Data Objects (ADO), or middle-tier objects to connect and manipulate SQL Server data.

This paper focuses on the challenges encountered by Office Access developers who rely on the Office Access (Jet) database engine to connect to SQL Server over ODBC. The most common way this is done is by creating linked tables in Office Access that use the SQL Server ODBC driver to connect to tables in SQL Server databases.
The SQL Server Migration Assistant (SSMA) for Office Access enables you to convert an Office Access database to this type of application by moving your Office Access data to new SQL Server tables and linking to these tables. Any forms, reports, queries, or code that previously worked with the original Office Access tables are automatically connected to the new SQL Server tables.
In an application that uses linked SQL Server tables, two different database engines are at work: the Office Access/Jet database engine that runs on the Office Access client and the SQL Server database engine. The interaction of these two engines can sometimes yield results that are inferior to those obtained by using only the Jet database engine with native Office Access tables. This white paper discusses several of these issues and presents strategies for resolving them. Most of these issues relate to performance or updatability.

Microsoft Access or SQL Server 2005: What's Right in Your Organization? by Luke Chung, FMS, Inc. for Microsoft Corp. (12/2004 revised 7/2006):

Organizations have a wide range of database needs and resources. From simple, short-term needs to long-term mission critical requirements, organizations create and support a variety of databases. Choosing the right technology is an ongoing challenge. Many organizations struggle with deciding whether or when they should be using Microsoft® Access and Microsoft SQL Server™. Both are powerful and established database alternatives with their unique strengths and weaknesses.
Microsoft Access is composed of two parts: the application layer and the data layer (Jet database engine). SQL Server only provides the data layer. The Access application layer can interact with SQL Server, either through linked tables in an .mdb database or directly through an Access Database Project (ADP). Other products like Visual Studio® .NET, Visual Basic®, and C++ can also interact with SQL Server.
Both databases have an important role in most organizations, because each is optimized for different segments of database solutions. The strength of Access is its ease of use, rapid application development environment, and simplistic distribution (assuming the recipient also has the correct version of Access installed). It can even support multiple user shared deployments. The strength of SQL Server is its more robust data integrity, scalability, security, and manageability.
Depending on the situation, the strength of Access may outweigh its deficiencies. Overall, if the database problem is targeted for Microsoft Windows®-based operating systems only and can be solved with Access, the need for SQL Server is minimized. This is particularly true if information workers who are not programmers can use Access to solve their own problems. Not only are immediate costs lower, Access users may create a solution that would be difficult for someone else to understand and create for them as quickly.
For more challenging situations, an information technology (IT) professional creating a SQL Server solution is more appropriate. Whether the front end is in Access or not, a SQL Server database offers many features that a file server Access database cannot. Whether you need triggers, stored procedures, transaction logs, Web application support, or security, SQL Server is a solid choice for critical needs that Access (using the Jet database engine) cannot match.
In most cases, it's easy to determine whether a new application should be using Access (with the Jet database engine) or SQL Server. The challenge for most organizations is how to anticipate and manage the small fraction of Access applications that need to migrate to SQL Server each year.
Few (we estimate less than 2 percent) Access databases need to migrate every year, yet some organizations want to ban Access completely. This is often because IT professionals are included when an Access application is breaking down. They fail to recognize the ability of Access to solve 98 percent of database situations that never require their involvement. When an Access application is created initially, the features needed in the future cannot be anticipated, nor can the budget be justified. It's a case of database evolution, and now it's time to evolve from Access and the Jet database engine. You have many ways to migrate an Access application without losing the existing investment, and an organization that manages this well attains a significant competitive advantage.

Client-Server Application Development Using Microsoft Access 2.0 blog post by Roger Jennings (4/25/2006) describes what I believe was the first Access application linked to a mainframe database (IBM DB2):

The oldest article that I've been able to find that's related to my early Access client/server development projects is the "Client-Server Application Development Using Microsoft Access 2.0" presentation from Tech•Ed 95 by Microsoft's Christopher Payne. (Chris Payne left Microsoft in March 2007.)
The article—last modified on February 26, 1995—had been available as a Web page from http://www.microsoft.com/Accessdev/ARTICLES/AC302.HTM. Details are preserved here because the Web version is no longer available from the Microsoft site, and it appears to have disappeared from the Google cache. Figures were missing from the cached source copy. Microsoft Knowledge Base article Q129613 has a link to a Te9514.exe self-extracting ZIP file that contains a downloadable Microsoft Word version of the article.
My Access 2.0 applications that inspired this article were NSC_ASK.mdb and MSA_OLTP.mdb. NSC_ASK.mdb was a client/server decision-support application for National Semiconductor Corp., which is described in my "An (Almost) Lifetime of BASIC" archive post. NSC_ASK.mdb began as an Access 1.1 front-end for a Hitachi mainframe running an IBM DB2 database. Microsoft didn’t offer an Open Database Connectivity (ODBC) data source for DB2 when I wrote NSC_ASK, so I used an early beta version of Information Builders’ EDA ODBC middleware to link Access front ends on salespersons’ laptops to the data source back end via TCP/IP and a phone modem. I wrote MSA_OLTP.mdb as a contract project for Greg Nelson, an early Access marketer.
National Semiconductor's Margaret Lam and Greg Nelson demonstrated the NSC_ASK and MSA_OLTP applications at Tech•Ed 1995's AC302 session—about a year and a half after Microsoft released Access 1.0 in November 1993. Special Edition Using Access 95 and subsequent editions have used MSA_OLTP.mdb to test backward compatibility and compare client/server data retrieval and update performance with unbound controls.
Remarkably, much of the content of this article still applies to client/server projects with current Access versions. MSA_OLTP.mdb was designed for heads-down telephone order entry by customer service representatives. All order lookup, new order entry, and new customer addition operations use keyboard shortcuts; a mouse isn't required.

My blog post continues with screen shots of the MSA_OLTP.mdb project and an excerpt from Chris Payne’s white paper.

Note that Visual Basic will celebrate it’s 25th birthday on May 20, 2011.

Evolving Microsoft Access Applications to SQL Server by Dan Haught, FMS, Inc. (undated, about 2000):

Learn When and How to Upsize to SQL Server

This whitepaper explores the issues related to upsizing Microsoft Access
applications to take advantage of the performance, security, and reliability of Microsoft SQL Server. Topics discussed include:

The Value of Access in Your Organization – a brief discussion of how
Access provides power and agility to an organization’s users
Making the Decision: When to Upsize – an evaluation of the criteria to
decide if an application has outgrown the capabilities of access
Microsoft Access Data Architectures – a discussion of the type of data
architectures that Access supports
Types of Upsizing Projects – There are several approaches to upsizing.
This section shows how to determine which one is best for you.
Planning an Upsizing Project – careful planning results in a successful
project. This section outlines what to plan for.

FMS Expertise – FMS is a world-leader in both Access and SQL Server.
We can deliver your upsizing project on time and on budget.

Technorati Tags: Microsoft Access 2010,Access 2010,Microsoft SQL Server,SQL Server 2008 R2,SQL Server,SQL Azure,Acces Upsizing Wizard,SQL Server Migration Assistant for Access,SSMA for Access

Output Logs for Successful Migration of Northwind.mdb to a SQL Server Northwind Database with SSMA for Access

2011-04-20T16:21:00.001-07:00

Successfully migrating the tables of an Access 2003 Northwind.mdb sample database with the SQL Server Migration Assistant for Access v4.2 generates 325 log entries for 9 tables. You can download Northwind.mdb from my Windows Live Skydrive instance here.

Following is a complete copy of these log entries:

Starting conversion...
Analyzing metadata...
Converting table Northwind.[Categories] ...
Information: A2SS0029: Primary key name 'PrimaryKey' was changed during conversion.
Information: A2SS0029: Index name 'CategoryName' was changed during conversion.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'CategoryName' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Description' was converted to a check constraint.
Information: A2SS0020: New timestamp column 'SSMA_TimeStamp' created.
Converting table Northwind.[Customers] ...
Information: A2SS0029: Primary key name 'PrimaryKey' was changed during conversion.
Information: A2SS0029: Index name 'City' was changed during conversion.
Information: A2SS0029: Index name 'CompanyName' was changed during conversion.
Information: A2SS0029: Index name 'PostalCode' was changed during conversion.
Information: A2SS0029: Index name 'Region' was changed during conversion.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'CustomerID' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'CompanyName' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'ContactName' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'ContactTitle' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Address' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'City' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Region' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'PostalCode' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Country' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Phone' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Fax' was converted to a check constraint.
 Warnings: A2SS0016: Column 'C:\USERS\ADMINISTRATOR\DOCUMENTS\NORTHWIND.MDB.CUSTOMERS.CUSTOMERID' of the primary key is nullable, which SQL Server does not allow. The nullable flag was removed from the target column to avoid problems during loading database objects into SQL Server. If there are rows that contain NULL values, you might encounter problems during data migration.
Converting table Northwind.[Employees] ...
Information: A2SS0029: Primary key name 'PrimaryKey' was changed during conversion.
Information: A2SS0029: Index name 'LastName' was changed during conversion.
Information: A2SS0029: Index name 'PostalCode' was changed during conversion.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'LastName' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'FirstName' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Title' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'TitleOfCourtesy' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Address' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'City' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Region' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'PostalCode' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Country' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'HomePhone' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Extension' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Notes' was converted to a check constraint.
Information: A2SS0020: New timestamp column 'SSMA_TimeStamp' created.
Converting table Northwind.[HRActions] ...
Information: A2SS0029: Primary key name 'PrimaryKey' was changed during conversion.
Information: A2SS0029: Index name 'ActionTypeEffDate' was changed during conversion.
Information: A2SS0029: Index name 'EffectiveDate' was changed during conversion.
Information: A2SS0029: Index name 'HRActionsEmployeeID' was changed during conversion.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'ActionType' was converted to a check constraint.
Information: A2SS0020: New timestamp column 'SSMA_TimeStamp' created.
Converting table Northwind.[Order Details] ...
Information: A2SS0029: Primary key name 'PrimaryKey' was changed during conversion.
 Warnings: A2SS0030: Primary key 'PrimaryKey' has a name that might cause problems for the Access application to function correctly against SQL Server.
Information: A2SS0029: Foreign key name 'Order DetailsOrders' was changed during conversion.
 Warnings: A2SS0030: Foreign key 'Order DetailsOrders' has a name that might cause problems for the Access application to function correctly against SQL Server.
Information: A2SS0029: Foreign key name 'ProductsOrder Details' was changed during conversion.
 Warnings: A2SS0030: Foreign key 'ProductsOrder Details' has a name that might cause problems for the Access application to function correctly against SQL Server.
Information: A2SS0029: Index name 'Order DetailsOrders' was changed during conversion.
 Warnings: A2SS0030: Index 'Order DetailsOrders' has a name that might cause problems for the Access application to function correctly against SQL Server.
Information: A2SS0029: Index name 'OrderID' was changed during conversion.
 Warnings: A2SS0030: Index 'OrderID' has a name that might cause problems for the Access application to function correctly against SQL Server.
Information: A2SS0029: Index name 'ProductID' was changed during conversion.
 Warnings: A2SS0030: Index 'ProductID' has a name that might cause problems for the Access application to function correctly against SQL Server.
Information: A2SS0029: Index name 'ProductsOrder Details' was changed during conversion.
 Warnings: A2SS0030: Index 'ProductsOrder Details' has a name that might cause problems for the Access application to function correctly against SQL Server.
 Warnings: A2SS0030: Table 'Order Details' has a name that might cause problems for the Access application to function correctly against SQL Server.
 Warnings: A2SS0016: Column 'C:\USERS\ADMINISTRATOR\DOCUMENTS\NORTHWIND.MDB.ORDER DETAILS.ORDERID' of the primary key is nullable, which SQL Server does not allow. The nullable flag was removed from the target column to avoid problems during loading database objects into SQL Server. If there are rows that contain NULL values, you might encounter problems during data migration.
Information: A2SS0020: New timestamp column 'SSMA_TimeStamp' created.
Converting table Northwind.[Orders] ...
Information: A2SS0029: Primary key name 'PrimaryKey' was changed during conversion.
Information: A2SS0029: Foreign key name 'CustomersOrders' was changed during conversion.
Information: A2SS0029: Foreign key name 'EmployeesOrders' was changed during conversion.
Information: A2SS0029: Foreign key name 'OrdersShippers' was changed during conversion.
Information: A2SS0029: Foreign key name 'ShippersOrders' was changed during conversion.
Information: A2SS0029: Index name 'CustomerID' was changed during conversion.
Information: A2SS0029: Index name 'CustomersOrders' was changed during conversion.
Information: A2SS0029: Index name 'EmployeeID' was changed during conversion.
Information: A2SS0029: Index name 'EmployeesOrders' was changed during conversion.
Information: A2SS0029: Index name 'OrderDate' was changed during conversion.
Information: A2SS0029: Index name 'OrdersShippers' was changed during conversion.
Information: A2SS0029: Index name 'ShippedDate' was changed during conversion.
Information: A2SS0029: Index name 'ShippersOrders' was changed during conversion.
Information: A2SS0029: Index name 'ShipPostalCode' was changed during conversion.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'CustomerID' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'ShipName' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'ShipAddress' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'ShipCity' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'ShipRegion' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'ShipPostalCode' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'ShipCountry' was converted to a check constraint.
Converting table Northwind.[Products] ...
Information: A2SS0029: Primary key name 'PrimaryKey' was changed during conversion.
Information: A2SS0029: Foreign key name 'CategoriesProducts' was changed during conversion.
Information: A2SS0029: Foreign key name 'ProductsCategories' was changed during conversion.
Information: A2SS0029: Foreign key name 'SuppliersProducts' was changed during conversion.
Information: A2SS0029: Index name 'CategoriesProducts' was changed during conversion.
Information: A2SS0029: Index name 'CategoryID' was changed during conversion.
Information: A2SS0029: Index name 'ProductName' was changed during conversion.
Information: A2SS0029: Index name 'ProductsCategories' was changed during conversion.
Information: A2SS0029: Index name 'SupplierID' was changed during conversion.
Information: A2SS0029: Index name 'SuppliersProducts' was changed during conversion.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'ProductName' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'QuantityPerUnit' was converted to a check constraint.
Information: A2SS0020: New timestamp column 'SSMA_TimeStamp' created.
Converting table Northwind.[Shippers] ...
Information: A2SS0029: Primary key name 'PrimaryKey' was changed during conversion.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'CompanyName' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Phone' was converted to a check constraint.
Converting table Northwind.[Suppliers] ...
 Warnings: A2SS0021: The hyperlink column 'HomePage' was converted to a text column and, as a result, is no longer a link.
Information: A2SS0029: Primary key name 'PrimaryKey' was changed during conversion.
Information: A2SS0029: Index name 'CompanyName' was changed during conversion.
Information: A2SS0029: Index name 'PostalCode' was changed during conversion.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'CompanyName' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'ContactName' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'ContactTitle' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Address' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'City' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Region' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'PostalCode' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Country' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Phone' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'Fax' was converted to a check constraint.
Information: A2SS0005: The Zero-length-not-allowed flag on the column 'HomePage' was converted to a check constraint.
Information: A2SS0020: New timestamp column 'SSMA_TimeStamp' created.
Conversion finished with 0 errors, 11 warnings, and 103 informational messages.
Loading objects into database...
Analyzing metadata...
Preparing category Tables of schema Northwind.dbo...
Preparing table Northwind.dbo.[Categories]...
Preparing category Triggers of table Northwind.dbo.[Categories]...
Preparing category Indexes of table Northwind.dbo.[Categories]...
Preparing index Northwind.dbo.[Categories].[Categories$CategoryName]...
Preparing table Northwind.dbo.[Customers]...
Preparing category Triggers of table Northwind.dbo.[Customers]...
Preparing category Indexes of table Northwind.dbo.[Customers]...
Preparing index Northwind.dbo.[Customers].[Customers$City]...
Preparing index Northwind.dbo.[Customers].[Customers$CompanyName]...
Preparing index Northwind.dbo.[Customers].[Customers$PostalCode]...
Preparing index Northwind.dbo.[Customers].[Customers$Region]...
Preparing table Northwind.dbo.[Employees]...
Preparing category Triggers of table Northwind.dbo.[Employees]...
Preparing category Indexes of table Northwind.dbo.[Employees]...
Preparing index Northwind.dbo.[Employees].[Employees$LastName]...
Preparing index Northwind.dbo.[Employees].[Employees$PostalCode]...
Preparing table Northwind.dbo.[HRActions]...
Preparing category Triggers of table Northwind.dbo.[HRActions]...
Preparing category Indexes of table Northwind.dbo.[HRActions]...
Preparing index Northwind.dbo.[HRActions].[HRActions$ActionTypeEffDate]...
Preparing index Northwind.dbo.[HRActions].[HRActions$EffectiveDate]...
Preparing index Northwind.dbo.[HRActions].[HRActions$HRActionsEmployeeID]...
Preparing table Northwind.dbo.[Order Details]...
Preparing category Triggers of table Northwind.dbo.[Order Details]...
Preparing category Indexes of table Northwind.dbo.[Order Details]...
Preparing index Northwind.dbo.[Order Details].[Order Details$Order DetailsOrders]...
Preparing index Northwind.dbo.[Order Details].[Order Details$OrderID]...
Preparing index Northwind.dbo.[Order Details].[Order Details$ProductID]...
Preparing index Northwind.dbo.[Order Details].[Order Details$ProductsOrder Details]...
Preparing table Northwind.dbo.[Orders]...
Preparing category Triggers of table Northwind.dbo.[Orders]...
Preparing category Indexes of table Northwind.dbo.[Orders]...
Preparing index Northwind.dbo.[Orders].[Orders$CustomerID]...
Preparing index Northwind.dbo.[Orders].[Orders$CustomersOrders]...
Preparing index Northwind.dbo.[Orders].[Orders$EmployeeID]...
Preparing index Northwind.dbo.[Orders].[Orders$EmployeesOrders]...
Preparing index Northwind.dbo.[Orders].[Orders$OrderDate]...
Preparing index Northwind.dbo.[Orders].[Orders$OrdersShippers]...
Preparing index Northwind.dbo.[Orders].[Orders$ShippedDate]...
Preparing index Northwind.dbo.[Orders].[Orders$ShippersOrders]...
Preparing index Northwind.dbo.[Orders].[Orders$ShipPostalCode]...
Preparing table Northwind.dbo.[Products]...
Preparing category Triggers of table Northwind.dbo.[Products]...
Preparing category Indexes of table Northwind.dbo.[Products]...
Preparing index Northwind.dbo.[Products].[Products$CategoriesProducts]...
Preparing index Northwind.dbo.[Products].[Products$CategoryID]...
Preparing index Northwind.dbo.[Products].[Products$ProductName]...
Preparing index Northwind.dbo.[Products].[Products$ProductsCategories]...
Preparing index Northwind.dbo.[Products].[Products$SupplierID]...
Preparing index Northwind.dbo.[Products].[Products$SuppliersProducts]...
Preparing table Northwind.dbo.[Shippers]...
Preparing category Triggers of table Northwind.dbo.[Shippers]...
Preparing category Indexes of table Northwind.dbo.[Shippers]...
Preparing table Northwind.dbo.[Suppliers]...
Preparing category Triggers of table Northwind.dbo.[Suppliers]...
Preparing category Indexes of table Northwind.dbo.[Suppliers]...
Preparing index Northwind.dbo.[Suppliers].[Suppliers$CompanyName]...
Preparing index Northwind.dbo.[Suppliers].[Suppliers$PostalCode]...
Starting Phase #0
Synchronizing Northwind.dbo ...
Loading to database new table Northwind.dbo.[Categories] ...
Loading to database new table Northwind.dbo.[Customers] ...
Loading to database new table Northwind.dbo.[Employees] ...
Loading to database new table Northwind.dbo.[HRActions] ...
Loading to database new table Northwind.dbo.[Order Details] ...
Loading to database new table Northwind.dbo.[Orders] ...
Loading to database new table Northwind.dbo.[Products] ...
Loading to database new table Northwind.dbo.[Shippers] ...
Loading to database new table Northwind.dbo.[Suppliers] ...
Loading to database new table Northwind.dbo.[Categories] primary keys ...
Loading to database new table Northwind.dbo.[Customers] primary keys ...
Loading to database new table Northwind.dbo.[Employees] primary keys ...
Loading to database new table Northwind.dbo.[HRActions] primary keys ...
Loading to database new table Northwind.dbo.[Order Details] primary keys ...
Loading to database new table Northwind.dbo.[Orders] primary keys ...
Loading to database new table Northwind.dbo.[Products] primary keys ...
Loading to database new table Northwind.dbo.[Shippers] primary keys ...
Loading to database new table Northwind.dbo.[Suppliers] primary keys ...
Loading to database new index Northwind.dbo.[Categories].[Categories$CategoryName] ...
Loading to database new index Northwind.dbo.[Customers].[Customers$City] ...
Loading to database new index Northwind.dbo.[Customers].[Customers$CompanyName] ...
Loading to database new index Northwind.dbo.[Customers].[Customers$PostalCode] ...
Loading to database new index Northwind.dbo.[Customers].[Customers$Region] ...
Loading to database new index Northwind.dbo.[Employees].[Employees$LastName] ...
Loading to database new index Northwind.dbo.[Employees].[Employees$PostalCode] ...
Loading to database new index Northwind.dbo.[HRActions].[HRActions$ActionTypeEffDate] ...
Loading to database new index Northwind.dbo.[HRActions].[HRActions$EffectiveDate] ...
Loading to database new index Northwind.dbo.[HRActions].[HRActions$HRActionsEmployeeID] ...
Loading to database new index Northwind.dbo.[Order Details].[Order Details$Order DetailsOrders] ...
Loading to database new index Northwind.dbo.[Order Details].[Order Details$OrderID] ...
Loading to database new index Northwind.dbo.[Order Details].[Order Details$ProductID] ...
Loading to database new index Northwind.dbo.[Order Details].[Order Details$ProductsOrder Details] ...
Loading to database new index Northwind.dbo.[Orders].[Orders$CustomerID] ...
Loading to database new index Northwind.dbo.[Orders].[Orders$CustomersOrders] ...
Loading to database new index Northwind.dbo.[Orders].[Orders$EmployeeID] ...
Loading to database new index Northwind.dbo.[Orders].[Orders$EmployeesOrders] ...
Loading to database new index Northwind.dbo.[Orders].[Orders$OrderDate] ...
Loading to database new index Northwind.dbo.[Orders].[Orders$OrdersShippers] ...
Loading to database new index Northwind.dbo.[Orders].[Orders$ShippedDate] ...
Loading to database new index Northwind.dbo.[Orders].[Orders$ShippersOrders] ...
Loading to database new index Northwind.dbo.[Orders].[Orders$ShipPostalCode] ...
Loading to database new index Northwind.dbo.[Products].[Products$CategoriesProducts] ...
Loading to database new index Northwind.dbo.[Products].[Products$CategoryID] ...
Loading to database new index Northwind.dbo.[Products].[Products$ProductName] ...
Loading to database new index Northwind.dbo.[Products].[Products$ProductsCategories] ...
Loading to database new index Northwind.dbo.[Products].[Products$SupplierID] ...
Loading to database new index Northwind.dbo.[Products].[Products$SuppliersProducts] ...
Loading to database new index Northwind.dbo.[Suppliers].[Suppliers$CompanyName] ...
Loading to database new index Northwind.dbo.[Suppliers].[Suppliers$PostalCode] ...
Loading to database new table Northwind.dbo.[Order Details] foreign keys ...
Loading to database new table Northwind.dbo.[Orders] foreign keys ...
Loading to database new table Northwind.dbo.[Products] foreign keys ...
Loading to database new table Northwind.dbo.[Categories] check constraints ...
Loading to database new table Northwind.dbo.[Customers] check constraints ...
Loading to database new table Northwind.dbo.[Employees] check constraints ...
Loading to database new table Northwind.dbo.[HRActions] check constraints ...
Loading to database new table Northwind.dbo.[Order Details] check constraints ...
Loading to database new table Northwind.dbo.[Orders] check constraints ...
Loading to database new table Northwind.dbo.[Products] check constraints ...
Loading to database new table Northwind.dbo.[Shippers] check constraints ...
Loading to database new table Northwind.dbo.[Suppliers] check constraints ...
Load operation is complete.
Migrating data...
Analyzing metadata...
Preparing table Northwind.[Categories]...
Migrating data for the table Categories from the database Northwind...
Data migration complete: 8 rows of 8 migrated.

Preparing table Northwind.[Customers]...
Migrating data for the table Customers from the database Northwind...
Data migration complete: 91 rows of 91 migrated.

Preparing table Northwind.[Employees]...
Migrating data for the table Employees from the database Northwind...
Data migration complete: 9 rows of 9 migrated.

Preparing table Northwind.[HRActions]...
Migrating data for the table HRActions from the database Northwind...
Data migration complete: 0 rows of 0 migrated.

Preparing table Northwind.[Order Details]...
Migrating data for the table Order Details from the database Northwind...
Data migration complete: 2155 rows of 2155 migrated.

Preparing table Northwind.[Orders]...
Migrating data for the table Orders from the database Northwind...
Data migration complete: 830 rows of 830 migrated.

Preparing table Northwind.[Products]...
Migrating data for the table Products from the database Northwind...
Data migration complete: 77 rows of 77 migrated.

Preparing table Northwind.[Shippers]...
Migrating data for the table Shippers from the database Northwind...
Data migration complete: 3 rows of 3 migrated.

Preparing table Northwind.[Suppliers]...
Migrating data for the table Suppliers from the database Northwind...
Data migration complete: 29 rows of 29 migrated.

Data migration operation has finished.
	9 table(s) successfully migrated. 
	0 table(s) partially migrated. 
	0 table(s) failed to migrate.
Linking tables...
Analyzing metadata...
Preparing mdb-file Northwind...
Preparing table Northwind.[Categories]...
Preparing table Northwind.[Customers]...
Preparing table Northwind.[Employees]...
Preparing table Northwind.[HRActions]...
Preparing table Northwind.[Order Details]...
Preparing table Northwind.[Orders]...
Preparing table Northwind.[Products]...
Preparing table Northwind.[Shippers]...
Preparing table Northwind.[Suppliers]...
Starting Phase #0
Processing table '[Categories]' in file 'Northwind'... ...
Linking & backing up table 'Categories' from database 'Northwind'...
Processing table '[Customers]' in file 'Northwind'... ...
Linking & backing up table 'Customers' from database 'Northwind'...
Processing table '[Employees]' in file 'Northwind'... ...
Linking & backing up table 'Employees' from database 'Northwind'...
Processing table '[HRActions]' in file 'Northwind'... ...
Linking & backing up table 'HRActions' from database 'Northwind'...
Processing table '[Order Details]' in file 'Northwind'... ...
Linking & backing up table 'Order Details' from database 'Northwind'...
Processing table '[Orders]' in file 'Northwind'... ...
Linking & backing up table 'Orders' from database 'Northwind'...
Processing table '[Products]' in file 'Northwind'... ...
Linking & backing up table 'Products' from database 'Northwind'...
Processing table '[Shippers]' in file 'Northwind'... ...
Linking & backing up table 'Shippers' from database 'Northwind'...
Processing table '[Suppliers]' in file 'Northwind'... ...
Linking & backing up table 'Suppliers' from database 'Northwind'...
Link & back up complete

Technorati Tags: Microsoft Access 2010,Microsoft Access,Access 2010,SQL Server,SQL Server 2008 R2,SQL Server Migration Assistant for Access,SQL Server Migration Assistant,SSMA for Access,SSMA,SQL Azure