Thursday, August 25, 2011

Creating SQLAzure Logins and User Accounts with ODBC and Access VBA

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

imageIn 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'

imageThankfully, 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.

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

Create Logins Form

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:

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

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

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


Sunday, August 7, 2011

Visual Studio LightSwitch as a Microsoft Access Upgrade Path

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

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

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

image

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

image

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.

image

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.

image

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.


Friday, August 5, 2011

Office 365 Video: Updating an Access database using SharePoint Online

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

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

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

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

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

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.

Thursday, August 4, 2011

Create Logins in a SQL Azure Database with Access

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

imageIn 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’

imageThankfully, 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.

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

Create Logins Form

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.






Monday, August 1, 2011

A SharePoint 2010 Primer for Access 2010 Developers by Ben Clothier

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

imageHello 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

imageIf 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):

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

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

Is it *gasp* de-normalized?

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

SharePoint Lists operates on ISAM model

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

What SharePoint lists offers that linked tables don’t

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

1) Offline data access

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

2) Built-in replication

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

3) Design changes

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

4) Automatic Auditing & Versioning

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

Server-side filtering

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

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

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

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

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

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

How many columns can I really have?

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

Conclusions

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


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

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



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